Как правильно удалить из текста таблицы Excel лишние символы и пробелы

Пробелы в числах

Разрывы в цифрах могут появиться в любой момент, и если вы работаете с энтузиазмом, вы можете даже не заметить, как они выглядят. Среди основных причин стоит отметить следующие:

• в то время как информация из внешних источников вводится в ячейку, могут также вводиться фрагменты текста из того же источника;
• числовая ячейка уже имеет форматирование, предусматривающее визуальное разделение на цифры, это предусмотрено для удобного чтения больших чисел;
• появление неразрушимых щелей.

Обычный «Пробел» можно просто убрать, но перечисленные выше причины просто не устранить. Для них нужно использовать специальные формулы, скрипты и использовать инструменты форматирования.

Способ 2: удаление пробелов между разрядами

Мы только что выяснили, как удалить пробелы в ячейках Excel с помощью текстовых выражений, но что, если вам нужно удалить лишние одиночные пробелы в числовых выражениях, которые часто устанавливаются автоматически? В этом случае также поможет инструмент «Заменить».

1. В таблице выделите область, содержащую ненужные пробелы.
2. На главной странице панели инструментов нажмите кнопку «Найти и заменить» и выберите инструмент «Заменить».
3. В появившемся окне на этот раз нужно ввести несколько другие значения.
4. В поле «Найти» введите пробел.
5. Оставьте поле «Заменить на» без изменений.
6. Нажмите кнопку «Заменить все».
7. Подтвердите свои действия, прочтите отчет и примите его.

После этого вы обнаружите, что пробелы между числами исчезли, соответственно задача выполнена.

Способ 3: удаление путем форматирования текста

использование приведенного выше оператора для удаления пробелов в Excel могло не дать никаких результатов. При этом программа вам сказала, что пробелов просто нет. Это происходит только в одном случае, если при форматировании были предусмотрены пробелы в числах. На самом деле это никак не повлияет на то, как формулы и все остальное работают, но для некоторых пользователей очень важно, чтобы все числа были записаны в одну строку без пробелов. Если вы один из них, следуйте инструкциям ниже:

1. Выделите область таблицы в ячейках, в которых вы хотите исключить пробелы между числовыми выражениями.
2. Щелкните выделение правой кнопкой мыши.
3. Выберите «Форматировать ячейки».
4. Появится соответствующее окно, в котором можно перейти в раздел «Числовой», расположенный на вкладке «Число».
5. В правой части окна снимите флажок «Разделитель групп грома».
6. Нажмите ОК».

После этого окно закроется, и пробелы между числовыми значениями исчезнут. Так что, если все дело было в форматировании, этот метод вам точно поможет.

Способ 2: удаление пробелов между разрядами

Мы только что выяснили, как удалить пробелы в ячейках Excel с помощью текстовых выражений, но что, если вам нужно удалить лишние одиночные пробелы в числовых выражениях, которые часто устанавливаются автоматически? В этом случае также поможет инструмент «Заменить».

1. В таблице выделите область, содержащую ненужные пробелы.
2. На главной странице панели инструментов нажмите кнопку «Найти и заменить» и выберите инструмент «Заменить».
3. В появившемся окне на этот раз нужно ввести несколько другие значения.
4. В поле «Найти» введите пробел.
5. Оставьте поле «Заменить на» без изменений.
6. Нажмите кнопку «Заменить все».
7. Подтвердите свои действия, прочтите отчет и примите его.

После этого вы обнаружите, что пробелы между числами исчезли, соответственно задача выполнена.

О вреде лишних пробелов

Наличие дополнительных пространств может помешать работе в следующих случаях:

• вам нужно сравнить ячейки между собой. Он может искать дубликаты в пределах диапазона или сравнивать два списка и искать повторяющиеся значения между ними
• При создании сводных таблиц строки «сворачиваются», если они полностью идентичны, и разница в одно пространство не позволяет им «сворачиваться». Алгоритм построения сводной таблицы также сравнивает ячейки друг с другом.
• Используя «ВПР» или комбинацию ИНДЕКС / ПОИСК, вы можете «тянуть» данные из одной таблицы в другую. Эти функции также сравнивают ячейки во время поиска и учитывают разницу в форме пространств.

Обычно лишние пробелы в тексте неуместны, и обычная задача — удалить их из текста. Но удалите ненужные, чтобы предложения не складывались в одно слово.

Способ 4: Изменение формата ячейки

Этот метод применяется только к числам, так как он позволяет вам изменить их формат, отключив автоматическое добавление пробелов в качестве разделителя групп цифр, которое иногда выбирается пользователем автоматически или вручную. Для этого необязательно обрабатывать каждый блок — можно выделить сразу все.

1. Выделите нужные ячейки в таблице, удерживая нажатой левую кнопку мыши.



2. Щелкните их правой кнопкой мыши и в появившемся контекстном меню найдите пункт «Формат ячеек».



3. Используя левую панель, переключитесь на «Числовой» формат, который изначально установлен для выбранных ячеек.



4. Снимите флажок «Разделитель групп грома» и сохраните изменения.

Вернитесь к таблице и убедитесь, что формат ячейки изменился. При необходимости вы всегда можете снова открыть то же меню и настроить другой тип просмотра.

Способ 2: Функция «Найти и заменить»

Вы можете автоматизировать удаление лишних пробелов из огромного количества значений таблицы с помощью стандартной функции поиска и замены. Это позволит вам убрать все двойные или тройные пробелы в автоматическом режиме в кратчайшие сроки.

1. На вкладке «Главная» найдите параметр «Найти и выбрать», разверните его раскрывающееся меню и выберите «Заменить».
2. Откроется окно, отвечающее за настройку быстрой замены шрифтов. В поле «Найти» дважды нажмите пробел, чтобы указать данные, которые вы ищете.
   

   В качестве альтернативы, если ввод пробела с клавиатуры не работает, вы можете попробовать вставить скопированное пространство из самой таблицы в поле «Найти». Этот вариант многим помогает.

3. Нет необходимости нажимать на поле «Заменить на», поскольку нет символов, которыми можно было бы заменить пробелы.
4. Если есть только несколько значений, используйте кнопку «Заменить», в противном случае нажмите «Заменить все».
5. Пройдет несколько секунд, после чего появится уведомление об успешном завершении данной операции. Вернитесь к таблице и проверьте изменения.

Найти и заменить

Найти и заменить — это самый простой способ удалить пробелы в ячейке Excel. Для того, чтобы все заработало, необходимо выполнить следующий порядок действий:

1. Выберите ячейку, столбец, строку или некоторые из них (где вы должны удалить «пробелы»).
2. Затем вы должны удерживать последовательность клавиш на клавиатуре Ctrl + H. Это сочетание клавиш вызовет специальное окно инструментов под названием «Найти и заменить» или «Найти и заменить» (в английской версии).
3. В столбце «Найти» следует ввести двойной пробел, а внизу «Заменить на» только один. Вы также можете установить пробел в строке «Найти» и оставить поле «Заменить на» пустым, если вы хотите полностью удалить пробелы.
4. После этого остается нажать «Заменить все».

   

На экране появится новое окно, информирующее вас о выполненных заменах и их количестве. На всякий случай можно повторить последний шаг, чтобы убедиться, что все пробелы в файле исправлены.

Способ 1: с помощью инструмента «Заменить»

Если при работе в программе вы случайно поставили два пробела между словами вместо одного, вы можете легко удалить их в Excel с помощью инструмента «Заменить». Использовать довольно просто:

1. Перейдите на вкладку «Главная».
2. На панели инструментов справа нажмите кнопку «Найти и выбрать». Нажмите здесь.
3. В появившемся раскрывающемся списке выберите «Заменить».
4. Появится окно, в котором нужно вставить двойной пробел в поле «Найти». Для этого просто дважды щелкните соответствующую кнопку.
5. В поле «Заменить на» введите пробел.
6. В окне отчета нажмите «ОК».

После этого все двойные пробелы в Excel будут удалены. Как видите, этот метод довольно прост в использовании. Все последующие способы не представляют особой сложности и пользователь легко поймет, как убрать лишние пробелы в Excel.

Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы

Предположим, у нас есть таблица с двумя столбцами. Столбец Имя в первой ячейке содержит правильное имя Джона Доу, то есть без лишних пробелов. Все остальные ячейки содержат вариант записи с дополнительными пробелами между именем и фамилией, а также в начале и в конце (ведущие и конечные пробелы). Второй столбец, называемый «Длина», показывает количество символов в каждом имени.

Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов

В Excel есть функция ОБРЕЗАТЬ, которая используется для удаления лишних пробелов из текста. Ниже вы найдете подробные инструкции по работе с этим инструментом:

1. Добавьте столбец поддержки рядом с вашими данными. Вы можете назвать это Trim.
2. В первой ячейке вспомогательного столбца (C2) введите формулу для удаления лишних пробелов:

• Скопируйте эту формулу в остальные ячейки столбца. Вы можете воспользоваться советами из статьи Как ввести одну и ту же формулу сразу во все выделенные ячейки .
• Заменить исходный столбец полученными данными. Для этого выделите все ячейки вспомогательного столбца и нажмите Ctrl + C, чтобы скопировать данные в буфер обмена, затем выберите первую ячейку исходного столбца (в нашем случае A2), нажмите Shift + F10 или клавишу контекстного меню, а затем клавишу V (З).

  

• Удалите вспомогательную колонку.

Готовый! Мы удалили все лишние пробелы с помощью функции TRIM. К сожалению, этот метод занимает много времени, особенно когда стол достаточно большой.

Примечание. Если после применения формулы вы все еще видите лишние пробелы, скорее всего, в тексте есть неразрывные пробелы. Вы можете узнать, как их удалить из этого примера.

Как удалить разрывы строк и непечатаемые символы

При импорте данных из внешних источников отображаются не только лишние пробелы, но и различные непечатаемые символы, такие как возврат каретки, перевод строки, вертикальные или горизонтальные табуляции и т.д.

TRIM может удалять пробелы, но не может удалять непечатаемые символы. Технически он предназначен для удаления только 7-битного значения 32 ASCII, что в точности соответствует пробелу.

Чтобы удалить непечатаемые символы в строке, используйте ее вместе с функцией CLEAN. Как следует из названия, PECHSIMV предназначен для очистки ненужных «мусорных» данных и может удалить любой из первых 32 непечатаемых символов в 7-битном наборе ASCII (значения от 0 до 31), включая разрыв строки (значение 10).

Предполагая, что данные, подлежащие очистке, находятся в ячейке A2, формула будет иметь следующий вид:

= ВЫРЕЗАТЬ (НАЖАТЬ (A2))

Почти всегда, когда вы удаляете разрывы строк с помощью приведенного выше выражения, отдельные слова «склеиваются» вместе. Вы можете решить эту проблему одним из следующих способов:

• Используйте инструмент Excel «Заменить все»: в поле «Найти» введите возврат каретки, нажав Ctrl + J. В поле «Заменить» введите пробел. При нажатии кнопки «Заменить все» все разрывы строк в выбранном диапазоне заменяются пробелами.
• Используйте следующую формулу для замены возврата каретки (код 13) и перевода строки (код 10) на пробелы:

= ОБРЕЗАТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (A2; СИМВОЛ (13); «»); СИМВОЛ (10); » «))

Как видите, почтовый адрес в столбце C выглядит вполне разборчивым.

Пример использования функции СЖПРОБЕЛЫ

Давайте рассмотрим быстрый пример, чтобы показать вам, как использовать функцию TRIM. У нас есть таблица, где указаны названия детских игрушек и их количество. Не очень грамотный оператор вел примитивную запись: когда появлялись дополнительные игрушечные блоки, он просто вводил новые локации, несмотря на то, что похожие названия уже существуют. Наша задача: посчитать общую сумму каждого вида.

Невооруженным глазом видно, что некоторые позиции написаны с заметными лишними пробелами. Подумайте, к чему это может привести.

Мы рассчитаем общее количество позиций для каждого типа игрушки с помощью функции СУММЕСЛИ. Вставляем, перетаскиваем на остальные ячейки и смотрим результат. Программа дала ответ за чучела зайцев: 3. Хотя видим, что должно быть 3 + 2 = 5. В чем проблема? Дополнительные места.

Записываем функцию в ячейку D3. В качестве аргумента введите ячейку B3, содержащую название игрушки.

Теперь давайте растянем функцию до строки 14 и увидим, что тексты действительно выровнены, потому что пробелы были удалены. Давайте проверим это наверняка, изменив диапазон в команде СУММЕСЛИ. Вместо B3: B14 напишите D3: D14 и посмотрите на результат. Сейчас реально 5 чучел зайцев, 6 медведей и т.д. Оказывается, доски играют важную роль и нуждаются в чистке.

Удалить переносы строк внутри ячеек («энтеры»)

Помимо обычных пространств, есть и другие ограничители космического характера. К ним относятся разрывы строк. Если вы просто удалите их, строковые значения могут слипнуться, поэтому лучше заранее заменить их собственным символом-разделителем.

SEMTools также имеет эту функцию! Поскольку надстройка добавляет в Excel регулярные выражения. В синтаксисе регулярных выражений s (пробел) обозначает любой тип пробелов, включая разрывы строк. Заменив их, вы можете преобразовать их в обычные пробелы, а если есть лишние, повторите предыдущий шаг.

Удаление разрывов строк (разрывов строк) в ячейках столбцов в Excel

Функция СЖПРОБЕЛЫ

Для удаления пробелов в ячейках Excel часто использует специальную функцию под названием TRIM или TRIM (в английской версии). Его немного сложнее выполнить, чем предыдущий метод, но он намного мощнее и может обрабатывать более сложный тип одностороннего отступа. Чтобы все заработало, нужно следовать инструкции:

1. Вы должны выбрать желаемый столбец или строку, которыми вы хотите управлять, чтобы удалить ненужные пробелы, а затем (наоборот, если столбец находится ниже, если строка) создать другой столбец, строку.
2. Теперь во вновь созданной ячейке строки или столбца вы должны ввести функцию, подобную этой: = ОБРЕЗАТЬ (номер соседней ячейки, в которой есть лишние пробелы). В английской версии TRIM вставляется вместо TRIM.
3. Затем вам нужно продублировать эту функцию, просто распространив ее на все остальные ячейки.
4. Следующим шагом является замена первого столбца (или строки) на тот, в котором вводятся формулы. Для этого нужно выделить все ячейки с формулами, скопировать их (Ctrl + C), затем щелкнуть левой кнопкой мыши по первой ячейке первого столбца (или строки).
5. Затем нажмите комбинацию клавиш Shift + F10 и V.

   

Таким образом, ненужные пробелы между цифрами или буквами будут удалены. Второй столбец (или строку) можно удалить.

Символ перед каждым словом в ячейке — Excel формула

Один символ до или после каждого слова в ячейке
Для начала мы определяем концепции и рассматриваем любой набор символов в ячейке как слово, граница которого является началом строки, пробелом или концом строки. Те, если перед буквами уже есть символы, нашей задачей будет не вставлять символы после них, а перед буквами, а перед. В такой конфигурации проблему решить довольно просто. Придется учесть 4 пункта:

1. Если в ячейке нет слов, в ячейку ничего нельзя добавить
2. Если в ячейке есть слово, вам просто нужно поставить перед ним символ, называя его ячейкой
3. Если есть 2 или более слов, пробел между ними можно заменить пробелом и требуемым символом, это формально и добавит символ перед каждым словом, кроме первого
4. На всякий случай лучше убрать лишние пробелы между словами, иначе предыдущий шаг создаст лишние символы в ячейке.

Теперь, когда все нюансы понятны, давайте взглянем на соответствующие необходимые функции:

1. IF поможет нам создать тестовое условие и даст Excel дальнейшие инструкции на основе результата.
2. СЦЕПИТЬ (или просто «&») добавит символ перед значением ячейки
3. REPLACE поможет вам заменить пробелы пробелом и символом, необходимым для вставки перед словами
4. TRIMS поможет вам избавиться от лишних пробелов перед этим

Посмотрите пример того, как создать формулу, состоящую из этих функций:

Мы разрабатываем формулу для вставки символа перед каждым словом. Мы решаем случаи, когда ячейка пуста и необходимо убрать лишние пробелы.

Отсюда окончательная универсальная формула:

= ЕСЛИ (A1 = «»; «»; D1 & ПОДСТАВИТЬ (ОБРЕЗАТЬ (A1); «»; «» & D1))

Здесь D1 — это ячейка, содержащая запрошенный символ. Вы не можете ссылаться на ячейку, но вручную «закодируете» ее в формулу. Например, вставка восклицательного знака перед каждым словом будет выглядеть так:

= ЕСЛИ (LA1 = «»; «»; «!» & ПОДСТАВИТЬ (ОБРЕЗАТЬ (A1);» «;» !»))

Удаляем все пробелы между числами

Предположим, у нас есть таблица с числами, в которой группы цифр (тысячи, миллионы, миллиарды) разделены пробелами. В этом случае Excel обрабатывает числа как текст, и математические операции выполнять нельзя.

Самый простой способ избавиться от лишних пробелов — использовать стандартный инструмент Excel «Найти и заменить).

• Нажмите Ctrl + Пробел, чтобы выделить все ячейки в столбце.
• Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить).
• В поле «Найти» введите пробел. Убедитесь, что поле «Заменить на» пусто.
• Нажмите кнопку «Заменить все», затем нажмите «ОК». Там! Все пробелы удалены.

Как убрать неразрывные пробелы в Excel?

Если после использования формулы TRIM и PECHSIMV все еще остаются какие-то упрямые символы, вы, скорее всего, скопировали / вставили данные из Интернета или другой программы, и несколько неразрывных пробелов все еще закрались в вашу таблицу.

Чтобы исключить неразрывные пробелы (html-код), замените их обычными, а затем попросите функцию TRIM удалить их:

= ОБРЕЗАТЬ ((ПОДСТАВИТЬ (A2; СИМВОЛ (160);» «)))

Чтобы лучше понять логику, давайте разберем формулу:

• Неразрывное пространство имеет 7-битный код ASCII 160, поэтому вы можете определить его с помощью SYMBOL (160).
• Функция REPLACE используется для преобразования неразрывных пробелов в обычные пробелы.
• И, наконец, введите ЗАМЕНА в ОБРЕЗКЕ, чтобы окончательно удалить все ненужные элементы.

Если ваш рабочий лист также содержит непечатаемые символы, помимо указанных выше, используйте функцию SECRET, чтобы удалить пробелы и другие ненужные символы за один раз:

= ОБРЕЗАТЬ (ПЕЧАТЬ ((ЗАМЕНИТЬ (A2; CH (160);» «))))

На следующем снимке экрана показана разница в результатах:

Способ 2: Функция «Найти и заменить»

Вы можете автоматизировать удаление лишних пробелов из огромного количества значений таблицы с помощью стандартной функции поиска и замены. Это позволит вам убрать все двойные или тройные пробелы в автоматическом режиме в кратчайшие сроки.

1. На вкладке «Главная» найдите параметр «Найти и выбрать», разверните его раскрывающееся меню и выберите «Заменить».
2. Откроется окно, отвечающее за настройку быстрой замены шрифтов. В поле «Найти» дважды нажмите пробел, чтобы указать данные, которые вы ищете.
   

   В качестве альтернативы, если ввод пробела с клавиатуры не работает, вы можете попробовать вставить скопированное пространство из самой таблицы в поле «Найти». Этот вариант многим помогает.

3. Нет необходимости нажимать на поле «Заменить на», поскольку нет символов, которыми можно было бы заменить пробелы.
4. Если есть только несколько значений, используйте кнопку «Заменить», в противном случае нажмите «Заменить все».
5. Пройдет несколько секунд, после чего появится уведомление об успешном завершении данной операции. Вернитесь к таблице и проверьте изменения.

Удаление апострофов в начале ячеек

Апостроф (‘) в начале ячейки на листе Microsoft Excel — это специальный символ, официально называемый текстовым префиксом. Необходимо дать понять Excel, что все последующее содержимое ячейки должно восприниматься как текст, а не как число. Апостроф виден только в строке формул, его не видно на листе.

Фактически, это удобная альтернатива заданию текстового формата для ячейки (Main → Number → Text) и для ввода длинных последовательностей чисел (номера банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим.

Но иногда апостроф попадает в ячейки против нашей воли (например, после загрузки из корпоративных баз данных) и начинает мешать вычислениям. Чтобы удалить его, вам понадобится небольшой макрос. Откройте редактор Visual Basic, нажав Alt + F11, вставьте новый модуль (меню «Вставка» → «Модуль») и введите его текст.

Теперь, если вы выберете диапазон на листе и запустите наш макрос (Alt + F8 или вкладка «Разработчик» → кнопка «Макрос»), апострофы перед содержимым выбранных ячеек исчезнут.

Формула ПОДСТАВИТЬ

Помимо первых двух методов, существует еще один метод, использующий специальную формулу «SUBSTITUTE», если используется английская версия, то «SUBSTITUTE».

Чтобы формула работала, вам нужно ввести следующую формулу в следующий столбец или строку, откуда взяты числа с пробелами: = ПОДСТАВИТЬ (A1; «»; «»). Запрошенный адрес необходимо ввести вместо A1. После этого при необходимости формула копируется во все остальные ячейки и заменяется так же, как в предыдущем способе. Таким образом, вы можете удалить пробелы во всем столбце.

Как получить слово после последнего пробела

довольно легко получить слово до первого пробела: = MID (A1; 1; FIND («»; A1) -1)

Но гораздо чаще возникают трудности с получением слова (символа), расположенного в определенной позиции между пробелами. В качестве примера я беру пространство, но на самом деле это может быть абсолютно любой персонаж. Например, чтобы получить второе слово (то есть слово между первым пробелом и третьим), вы можете написать следующую формулу:

= MID (A1; НАЙТИ («»; A1) +1, НАЙТИ («», A1, НАЙТИ («», A1) +1) -НАЙТИ («», A1) -1)

На мой взгляд, это выглядит немного запутанно, хотя все предельно просто:

• НАЙТИ («»; A1) +1 — поиск позиции первого пробела;
• НАЙТИ («»; A1; НАЙТИ («»; A1) +1) — ищет позицию второго пробела, а затем вычитает позицию первого пробела из этой позиции (-НАЙТИ («»; A1)).

Но возникает проблема: если второго пробела нет, формула выдаст ошибку # ЗНАЧ !. Таким образом, вам также необходимо проверить наличие ошибки, что, очевидно, не добавляет элегантности формуле.

Поэтому я предпочитаю использовать эту формулу: = ПОДСТАВИТЬ (ПРАВО (MID («» & ПОДСТАВИТЬ (A1; «»; ПОВТОР («»; 999)); 1; 999 * 2); 999);» «;»»)

На первый взгляд, это намного больше кошмар, чем первый.

Но у него есть ряд преимуществ: его не нужно проверять на пробелы; поменяв номер, можно получить не второе, а 3-е, 4-е и так далее.

Разберем самое главное: чтобы получить первое слово из начала строки, нужно в блоке 999 * 2 заменить 2 на 1:

= REPLACE (RIGHT (MIDDLE («» & REPLACE (A1; «»; REPEAT («»; 999))); 1; 999 * 1); 999); «»; «») Чтобы получить пятый — умножить на 5:

= REPLACE (RIGHT (MIDDLE («» & REPLACE (A1; «»; REPEAT («»; 999))); 1; 999 * 5); 999);» «;»»)

И ЧТО БЫЛО СДЕЛАТЬ — СЛОВО ПОСЛЕ ПОСЛЕДНЕГО ПРОСТРАНСТВА

Если вдруг окажется меньше пробелов, чем указанное число, мы получим слово после последнего пробела (т.е первое слово с конца строки). Это означает, что если вы укажете * 999, в большинстве случаев мы получим только последнее слово.

Как это работает: с помощью функции REPLACE мы заменяем все пробелы в тексте 999 пробелами (число может быть меньше 999, но не должно быть меньше длины исходной строки). Затем с помощью функции PSTR извлекаем первые 999 символов, умноженные на число, обозначающее нужное нам слово (999 * 1 — первое). Фактически только указанное количество слов (в данном случае одно — 999 * 1). Таким образом, функция RIGHT возвращает нам только последнее слово, то есть то, что нам нужно. А затем сама ЗАМЕНА теперь удаляет лишние пробелы, заменяя их все пустой строкой — «».

Кажется, мы получили то, что нам нужно. Но что, если вам нужно получить второе слово с конца строки? Как быть тогда? Считаете пробелы? Что, если у нас будет хотя бы 50? Вы можете использовать некоторую модификацию приведенной выше формулы, но она возвращает только слово из конца строки:

= REPLACE (MIDDLE (RIGHT («» & REPLACE (A1; «»; REPEAT («»; 999))); 999 * 1); 1; 999);» «;»»)

Принцип тот же: если в блоке 999 * 1 заменить 1 на 5, мы получим пятое слово с конца строки.

Если вам нужно извлекать слова из пробелов, то лучше добавить еще одну функцию — TRIMS:

= REPLACE (RIGHT (MIDDLE («» & REPLACE (TRIM (A1); «»; REPEAT («»; 999))); 1; 999 * 1); 999);» «;»»)

= REPLACE (MIDDLE (RIGHT («» & REPLACE (TRIM (A1); «»; REPEAT («»; 999))); 999 * 1); 1; 999);» «;»»)

Вы также можете «извлечь» несколько слов: = TRIMS (MID (RIGHT («» & SUBSTITUTE (A1; «»; REPEAT («»; 999)); 999 * 3); 1; 999 * 2))

3 — третье слово с конца строки. 2 — количество слов.

Осталось добавить, что вместо пробелов могут быть другие символы. Например, очень распространенная ситуация возникает, когда вам нужно получить из текста не слово в определенной позиции, а конкретную строку текста, разнесенную по строкам в ячейке:

Затем, чтобы получить вторую строку (ТК Таганка и ТК Опус), можно применить следующую формулу: = ПОДСТАВИТЬ (ПРАВО (PSTR (СИМВОЛ (10) & ПОДСТАВИТЬ (C2; СИМВОЛ (10); ПОВТОР (СИМВОЛ (10)))); 999)); 1; 999 * 2); 999); СИМВОЛ (10); «») CHAR (10) здесь означает разрыв строки. Обычно эти переводы выполняются с клавиатуры. Входим в режим редактирования ячейки, ставим курсор в нужную точку в строке и нажимаем Alt + Enter.

у меня есть месяцы (август 2020 г и сентябрь 2020 г.) — вот так:

= REPLACE (MIDDLE (RIGHT («/» & REPLACE (C2; «/»; REPEAT («/»; 999))); 999 * 1); 1; 999); «/»; «»)) Если вы также вам лень писать один символ в формуле несколько раз, вы можете записать его в ячейку (скажем, G1) и указать ссылку на эту ячейку в формуле: = REPLACE (RIGHT (G1 & REPLACE (C2; G1; ПОВТОР (G1; 999)); 999 * 1); 1; 999); G1; «») Теперь, чтобы изменить символ, вам нужно будет изменить его один раз в ячейке, и формула «вытащит» нужное слово / строку на основе этого конкретного символа.

Форматирование ячеек

Иногда причиной ненужных пробелов является форматирование. Возможно, он уже был повторно введен или перемещен с данными из внешнего источника. Чтобы все исправить, нужно щелкнуть правой кнопкой мыши вверху столбца и выбрать там вкладку «Формат ячеек». Появится список настроек, нужно выбрать вкладку «Число», где уже и настроить наличие битового разрыва.

Если вам нужно убрать разделительные пробелы в числах, перейдите в настройки формата ячеек и выберите «Числовой». В этом случае снимите флажок «Разделитель групп цифр».

Способ 3: удаление путем форматирования текста

использование приведенного выше оператора для удаления пробелов в Excel могло не дать никаких результатов. При этом программа вам сказала, что пробелов просто нет. Это происходит только в одном случае, если при форматировании были предусмотрены пробелы в числах. На самом деле это никак не повлияет на то, как формулы и все остальное работают, но для некоторых пользователей очень важно, чтобы все числа были записаны в одну строку без пробелов. Если вы один из них, следуйте инструкциям ниже:

1. Выделите область таблицы в ячейках, в которых вы хотите исключить пробелы между числовыми выражениями.
2. Щелкните выделение правой кнопкой мыши.
3. Выберите «Форматировать ячейки».
4. Появится соответствующее окно, в котором можно перейти в раздел «Числовой», расположенный на вкладке «Число».
5. С правой стороны окна удалите прибл.
6. Нажмите ОК».

После этого окно закроется, и пробелы между числовыми значениями исчезнут. Так что, если все дело было в форматировании, этот метод вам точно поможет.

Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами

Очень часто в нашем распоряжении есть данные, которые, хотя и содержат полезную информацию, не поддаются немедленному анализу. Одна из самых распространенных проблем — наличие лишних пробелов — в начале, в конце или внутри между словами.

С этой проблемой очень легко справиться с помощью специальной функции TRIM. Удаляет все лишние пробелы, но оставляет пробел между словами:

Просто и красиво. Но есть пара нюансов.

Неразрывные пробелы не удаляются

Чайная ложка мази тут как раз кажется невозможностью удалить таким способом сплошные пробелы. Это особый вариант пробела (внешне неотличимый от обычного), в котором отсутствует перенос строки. Обычно используется в таких случаях, как «g. Москва »или« Иванов II », чтобы эти предложения не разрывались между строками. В Microsoft Word такой специальный символ вводится комбинацией клавиш Ctrl + Shift + Пробел и отображается кружком, а не точкой:

К сожалению, функция TRIM удаляет только нормальные пробелы. Чтобы убрать унификацию, нужно использовать другие функции или макросы.

Удаление пробелов только слева или справа

Если вам нужно удалить пробелы только слева или справа (иначе они называются ведущими и замыкающими), вам необходимо сделать следующее:

1. Уберите пробел слева в начале строки. Чтобы все работало, нужно в соседнюю ячейку ввести следующую формулу: = MID (A1; FIND (LEFT (TRIM (A1))). Если английская версия, то: = MID (A1; FIND (LEFT (TRIM (A1))); A1); LEN (A1)) Где A1 — целевое значение для обработки.

   

2. Уберите пробел справа в конце строки. В соседней ячейке введите следующую формулу: = LEFT (F1; MAX ((MID (A1 & REPEAT (««; 99); LINE (A1: A99); 1); »«) * LINE (A1: A99))). В английской версии: = LEFT (A1; MAX ((MID (A1 & REPT («»; 99); ROW (A1: A99), 1); «») * ROW (A1: A99))). Где A1 — целевое значение для обработки.

   

СЖПРОБЕЛЫ с другими функциями

Использование функции TRIM в сочетании с другими функциями улучшает взаимодействие с пользователем. Имеет смысл использовать его вместе с теми функциями, которые проверяют массивы и возвращают данные. В частности, это функции НАЙТИ, ВЛЕВО, ВПРАВО и т.д. На практике оказывается, что TRIM чаще всего используется вместе с ВПР.

Давайте посмотрим на пример той же таблицы. Задача: поставить цену перед каждым предметом. Справа есть вспомогательная таблица, в которой указана цена каждой игрушки. Теоретически мы можем вручную повторно ввести нужные числа, но, поскольку мы находимся в Excel, мы будем использовать ВПР.

Мы особо выделили те позиции, которые написаны розовым цветом с лишними пробелами. Поэтому команда VLOOKUP не дала им результата. Чтобы исправить это, добавьте TRIM в VLOOKUP и посмотрите, что произойдет.

Поскольку нам нужно удалить пробелы в желаемом значении, сначала поместите команду TRIM в синтаксисе VLOOKUP. Формула немного изменилась, и если мы теперь расширим ее до нижней части таблицы, функция заполнит все значения за нас. Теперь все правильно.

Удалить пробелы в конце строки

Довольно гипотетическая ситуация, но все же возможная: когда вам нужно удалить только пробелы в конце строки, а если в строке есть два или более пробелов в середине строки, не удаляйте их. И здесь на помощь приходят регулярные выражения:

= REGEXREPLACE (A1; » s+$»;»»)

Как видите, регулярное выражение заменяет один или несколько пробелов пробелами, если они находятся в конце строки.

Способ 4: с помощью специальной функции

Все вышеперечисленные методы неэффективны, если вам нужно удалить пробелы в начале или конце значения в Excel. Однако функция TRIM идеально подходит для этого.

1. Поместите курсор в ячейку параллельно столбцу или строке, пробелам, из которых вы хотите удалить.
2. Откройте окно мастера, нажав соответствующую кнопку рядом с панелью функций.
3. В списке «Категория» выберите «Текст», а в списке функций выберите «ОБРЕЗАТЬ» и нажмите «ОК».
4. Появится окно ввода функции. В нем нужно указать ячейку, в которой нужно убрать пробелы. Для этого достаточно щелкнуть по нему левой кнопкой мыши.
5. Нажмите ОК».

Лишние пробелы будут удалены сразу после этого. Однако правильная версия будет в ячейке, в которую была записана формула. Хорошо, просто скопируйте ячейку и вставьте ее в нужную область.

Способ 2: удаление пробелов между разрядами

Мы только что выяснили, как удалить пробелы в ячейках Excel с помощью текстовых выражений, но что, если вам нужно удалить лишние одиночные пробелы в числовых выражениях, которые часто устанавливаются автоматически? В этом случае также поможет инструмент «Заменить».

1. В таблице выделите область, содержащую ненужные пробелы.
2. На главной странице панели инструментов нажмите кнопку «Найти и заменить» и выберите инструмент «Заменить».
3. В появившемся окне на этот раз нужно ввести несколько другие значения.
4. В поле «Найти» введите пробел.
5. Оставьте поле «Заменить на» без изменений.
6. Нажмите кнопку «Заменить все».
7. Подтвердите свои действия, прочтите отчет и примите его.

После этого вы обнаружите, что пробелы между числами исчезли, соответственно задача выполнена.

Способ 3: Функция ПОДСТАВИТЬ

По аналогии с предыдущим методом работает функция REPLACE, для которой задается сама ячейка, желаемое значение и его замена. Вам нужно активировать пустую ячейку, в которой будет заменено уже правильное значение, затем введите следующее: ПОДСТАВИТЬ (A1; «»; «»), где A1 — ячейка, необходимая для обработки. Восстановите или скопируйте эту формулу, заменив другие ячейки, если вам нужно обработать несколько значений одновременно.

Синтаксис

ОБРЕЗАТЬ (текст)

Аргументы функции TRIM описаны ниже.

• Требуется текст. Текст, из которого удалены пробелы.

Способ 4: с помощью специальной функции

Все вышеперечисленные методы неэффективны, если вам нужно удалить пробелы в начале или конце значения в Excel. Однако функция TRIM идеально подходит для этого.

1. Поместите курсор в ячейку параллельно столбцу или строке, пробелам, из которых вы хотите удалить.
2. Откройте окно мастера, нажав соответствующую кнопку рядом с панелью функций.
3. В списке «Категория» выберите «Текст», а в списке функций выберите «ОБРЕЗАТЬ» и нажмите «ОК».
4. Появится окно ввода функции. В нем нужно указать ячейку, в которой нужно убрать пробелы. Для этого достаточно щелкнуть по нему левой кнопкой мыши.
5. Нажмите ОК».

Лишние пробелы будут удалены сразу после этого. Однако правильная версия будет в ячейке, в которую была записана формула. Хорошо, просто скопируйте ячейку и вставьте ее в нужную область.

Как посчитать пробелы в Excel

Чтобы узнать общее количество пробелов в ячейке, выполните следующие действия:

• Вычислить всю длину строки с помощью функции DLSTR: DLSTR (A2)
• Замените все пробелы ничем: SUBSTITUTE (A2; » «; «»)
• Вычислите длину строки без пробелов: DLSTR (SUBSTITUTE (A2; » «; «»))
• Вычтите этот результат из исходной длины.

Предполагая, что исходная текстовая строка находится в ячейке A3, полная формула выглядит следующим образом:

= DLSTR (A3) — DLSTR (ПОДСТАВИТЬ (A3;» «;»»))

Чтобы узнать, сколько лишних пробелов в ячейке, получите длину текста без них, а затем вычтите ее из исходной длины:

= DLSTR (A3) -LSTR (TRIM (A3))

На рисунке показаны обе формулы в действии:

Теперь, когда вы знаете, сколько пробелов содержит каждая ячейка, вы можете безопасно удалить лишние пробелы с помощью функции TRIM.