Excel отрезать последние символы

Удалить пробелы в ячейках Excel – Офис Ассист

Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel, далек от совершенства. Если он был введен другими пользователями или выгружен из какой-нибудь корпоративной БД или ERP-системы не совсем корректно, легко может содержать следующее.

• лишние пробелы перед, после или между словами (для красоты!);
• ненужные символы («г.» перед названием города);
• невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или «кривой» выгрузки из 1С, переносы строк, табуляция);
• апострофы (текстовый префикс — спецсимвол, задающий текстовый формат у ячейки).

Давайте рассмотрим способы избавления от такого «мусора».

Замена символов

Старый, но не устаревший трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки → Найти и выделить (Home → Find & Select → Replace) или жмем сочетание клавиш Ctrl+H.

Изначально это окно было задумано для оптовой замены одного текста на другой по принципу «найди Маша — замени на Петя», но мы в данном случае можем использовать его и для удаления лишнего текста. Например, в первую строку вводим «г.» (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы «г.» перед названиями городов.

Удаление пробелов

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

Однако часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние — иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого — СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно.

Удаление непечатаемых символов

В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел, — на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их «зачистить».

Вариантов решения два:

• Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
• Использовать функцию ПЕЧСИМВ (CLEAN). Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справиться не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE). У нее три обязательных аргумента:

• Текст, в котором производим замену.
• Старый текст — тот, который заменяем.
• Новый текст — тот, на который заменяем.

С ее помощью можно легко избавиться от ошибок (замена «а» на «о»), лишних пробелов (замена их на пустую строку «»), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом).

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

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

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

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

Sub Apostrophe_Remove() For Each cell In Selection If Not cell.HasFormula Then v = cell.Value cell.Clear cell.Formula = v End If Next End Sub

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

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

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

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

5. Дальше следует нажать сочетание клавиш Shift+F10 и V.

И таким образом произойдет удаление ненужных пробелов между числами или буквами. Второй столбик (или строчку) можно удалить.

Как удалить пробелы в Excel

В этом уроке я покажу вам два способа удаления пробелов в Excel.

• Использование функции TRIM.
• Использование поиска и замены.

Использование функции TRIM

Функция Excel TRIM удаляет начальные и конечные пробелы, а также двойные пробелы между текстовыми строками.

Например, в приведенном выше примере, чтобы удалить пробелы из всего списка для имен (в A2: A7), используйте следующую формулу в ячейке C1 и перетащите ее вниз для всех имен:

= ОБРЕЗАТЬ (A2)

Функция Excel TRIM мгновенно удалит все начальные и конечные пробелы в ячейке.

Когда у вас есть очищенные данные, скопируйте их и вставьте как значения вместо исходных данных.

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

Функция Excel TRIM хорошо справляется с удалением пробелов в Excel, однако она не работает, если в вашем наборе данных есть непечатаемые символы (например, разрывы строк). Чтобы удалить непечатаемые символы, вы можете использовать комбинацию функций ОБРЕЗАТЬ и ОЧИСТИТЬ.

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

= ОБРЕЗАТЬ (ЧИСТЫЙ (A1))

Непечатаемые символы также могут быть результатом = CHAR (160), который не может быть удален с помощью формулы CLEAN. Итак, если вы хотите быть абсолютно уверены, что у вас есть все лишние пробелы и непечатаемые символы, используйте следующую формулу:

= ОБРЕЗАТЬ (ОЧИСТИТЬ (ПОДСТАВИТЬ (A1; СИМВОЛ (160); "")))

Удалите лишние пробелы в Excel с помощью НАЙТИ и ЗАМЕНИТЬ

Вы можете удалить пробелы в Excel с помощью функции «Найти и заменить».

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

• Когда вы хотите удалить двойные пробелы.
• Если вы хотите удалить все символы пробела.

Удаление двойных пробелов

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

Вот как это сделать:

• Выделите ячейки, из которых вы хотите удалить двойные пробелы.
• Перейдите на главную -> Найти и выбрать -> Заменить. (Также можно использовать сочетание клавиш — Control + H).
• В диалоговом окне «Найти и заменить» введите:
  • Найдите что: Двойное пространство.
  • Заменить на: Single Space.
• Нажмите «Заменить все».

Это заменит все двойные пробелы одним пробелом.

Обратите внимание, что это удалит только двойные пробелы. Если у вас есть три пробела между двумя словами, это приведет к двум пробелам (один будет удален)

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

Удаление одиночных пространств

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

• Выделите ячейки, из которых вы хотите удалить пробел.
• Перейдите на главную -> Найти и выбрать -> Заменить. (Также можно использовать сочетание клавиш — Control + H).
• В диалоговом окне «Найти и заменить» введите:
  • Что: единое пространство.
  • Заменить на: Оставьте это поле пустым.
• Нажмите «Заменить все».

Это удалит все пробелы в выбранном наборе данных.

Обратите внимание, что в этом случае, даже если между двумя текстовыми строками или числами имеется более одного символа пробела, все они будут удалены

Удалить разрывы строк

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

Вот как это сделать:

• Выберите данные.
• Перейдите на главную -> Найти и выбрать -> Заменить (сочетание клавиш — Ctrl + H).
• В диалоговом окне «Найти и заменить»:
  • Найти что: нажмите Control + J (вы можете не видеть ничего, кроме мигающей точки).
  • Заменить на: оставьте поле пустым.
• Заменить все.

Это мгновенно удалит все разрывы строк из выбранного вами набора данных.

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

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

Если взаимодействие трех функций, описанных в приведенном выше примере, не смогло устранить весь мусор из текста, то это означает, что оставшиеся знаки имеют значения ASCII, отличные от 0 до 32 (непечатаемые символы) или 160 (неразрывный пробел).

В этом случае используйте функцию КОДСИМВ, чтобы сначала идентифицировать код мешающего вам знака, а затем используйте ПОДСТАВИТЬ, чтобы заменить его обычным пробелом. А затем при помощи СЖПРОБЕЛЫ удалите его.

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

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

В этом примере у нас есть неизвестный знак в середине текста, в 11-й позиции, и мы определим его код:

=КОДСИМВ(ПСТР(A2;11;1))

Получаем значение 127 (см. скриншот ниже).

1. В ячейке C3 вы заменяете СИМВОЛ(127) обычным пробелом (» «), а затем просто удаляете его:

Если ваши данные содержат несколько разных непечатаемых символов, а также неразрывные пробелы, вы можете вложить две или более функции ПОДСТАВИТЬ друг в друга для одновременного удаления всех нежелательных знаков:

Результат должен выглядеть примерно так:

Эту универсальную формулу мы и использовали. Как видите, успешно.

Убираем в MS EXCEL из текстовой строки числа слева

​javvva​ знаков, и выдавать​​ так – надо​​: Добрый день. Подскажите​ до значения​ (ТЕКСТ (A1;n0);7;1)+1)&ИНДЕКС (n1x;ПСТР​ (ТЕКСТ (A1;n0);4;3);”миллион”&ВПР (ПСТР​

​ (n_4;ПСТР (ТЕКСТ (A1;n0);4;1)+1)&ИНДЕКС​​Freedoms​

​Пусть текстовая строка содержит​

• ​Пусть текстовая строка содержит​ справа до знака​ диапазона используемых ячеек​
• ​ диапазона используемых ячеек​ примере “, ,”​
• ​ можно формулу массива​ Есть хлс документ​
• ​: протянуть формулу по​ все символы, если​ проверить, а не​ пожалуйста по такому​
• ​vikttur​ (ТЕКСТ (A1;n0);8;1)+1;ПСТР (ТЕКСТ​ (ТЕКСТ (A1;n0);6;1)*И (ПСТР​ (n0x;ПСТР (ТЕКСТ (A1;n0);5;1)+1;ПСТР​

​: =ЛЕВСИМВ (A2; ДЛСТР​ буквы и текст.​ буквы и текст.​ “/”​

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

Получить слово до первого пробела достаточно просто:
=ПСТР(A1;1;НАЙТИ(» «;A1)-1)

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

=ПСТР(A1;НАЙТИ(» «;A1)+1;НАЙТИ(» «;A1;НАЙТИ(» «;A1)+1)-НАЙТИ(» «;A1)-1)

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

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

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

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

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

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

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

=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;999));1;999*1);999);» «;»»)
Чтобы получить 5-е — на 5:

=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;999));1;999*5);999);» «;»»)

И ТО, К ЧЕМУ ШЛИ — СЛОВО ПОСЛЕ ПОСЛЕДНЕГО ПРОБЕЛА

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

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

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

=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(» «&ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;999));999*1);1;999);» «;»»)

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

Если необходимо выдергивать слова по пробелам, то лучше дополнить еще одной функцией — СЖПРОБЕЛЫ:

=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);» «;ПОВТОР(» «;999));1;999*1);999);» «;»»)

=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(» «&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);» «;ПОВТОР(» «;999));999*1);1;999);» «;»»)

Так же можно «вытянуть» определенное количество слов:
=СЖПРОБЕЛЫ(ПСТР(ПРАВСИМВ(» «&ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;999));999*3);1;999*2))

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

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

я для получения месяцев(Август 2015 г. и Сентябрь 2015 г.) — такую:

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

Есть достаточно большая таблица в MS Excel. Как автоматически удалить лишние данные из столбца?

​ и звездочка)​​ скрыть: меню -​ таблица на пустом​ есть ключевой столбец!​(Roger) по-прежнему на​ списка (на практике​ 2 быстрых и​ если строка с​ совет, якобы позволяющий​ знаете, что пустые​ строки.​ разглагольствуя, перейдем непосредственно​ пункт «Удалить строки​ от друга.​ Выше описан яркий​

​(Выделить группу ячеек).​​Go To Special​​Заменить все.​​ формат — строка/столбец​ сером фоне за​​ Примените фильтр к​

​ месте (сравните с​

• Как в excel продлить формулу на весь столбец
• Как в таблице excel посчитать сумму столбца автоматически
• Как в excel убрать пустые столбцы
• Excel удалить повторяющиеся значения
• Удалить гиперссылки в excel
• Excel количество уникальных значений в столбце
• Формула умножения в excel для всего столбца
• Поиск в столбце в excel
• Удалить дубли в excel
• Как в excel поменять строки и столбцы местами
• Как в excel закрепить и строку и столбец одновременно
• Excel подсчет уникальных значений в столбце

Удаление таблицы

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

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

​Выделите ячейки таблицы, для​​ стиля по умолчанию​).​ преобразуется в таблицу.​

​Если в книге есть​ таблицы. Если вам​ таблицы.​ ячейки с условным​ сложные аналитические вычисления,​ недостаток данного способа​ и форматирования.​ помогла ли она​ таблицы, а вам​

support.office.com>