Как убрать текст после пробела excel - Word и Excel - помощь в работе с программами

Содержание

Как удалить все до или после определенного символа в Excel
С помощью функции «Найти и заменить»
С помощью формул
С помощью функции «Заполнить»
С помощью Visual Basic
Excel убрать все после пробела
Простое разделение ячеек на несколько ячеек / строк (список столбцов) в Excel
Как удалить символы После знака в Excel
Видеоинструкция
Как удалить знаки после символа в Excel
Удаление знака после запятой в Эксель
После пробела в Excel
Вам также будет интересно
Комментарии 2

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

Люди, которые только начинают работать в Excel часто встречаются с таким вопросом.

Допустим, у нас есть такая табличка:

Примерно так выглядит удаление всех символов после «,».

Это можно сделать разными способами. Мы рассмотрим несколько.

С помощью функции «Найти и заменить»

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

Допустим, у нас та же табличка и задача:

Копируем и вставляем столбик А в В;

Выделите столбик и щелкните «Главная»;

Далее — «Найти и выделить» -> «Заменить…»;

В первом параметре укажите «,*»;

Второй параметр не меняйте;

Щелкните «Заменить все».

Готово! Вот результат:

Как это работает?

Символ * означает неопределенное количество символов.

Так как мы используем «,*», то это значит, что программе нужно заменить запятую и все символы после неё на пустое место.

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

С помощью формул

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

Допустим, у нас есть такая табличка:

Формула принимает такой вид:

Функция НАЙТИ возвращает порядковый номер запятой.

Это простой пример, давайте рассмотрим кое-что посложнее.

Теперь у нас такая табличка:

Формула, для этого примера, принимает такой вид:

Итак, также как в прошлый раз — не получится. Так как НАЙТИ будет возвращать порядковый номер первой запятой, а нам надо найти его для второй.

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

И все бы хорошо, только в этом примере в каждой строке у нас ровно 2 запятые. А что делать если их неопределенное количество? Ведь в больших данных вы не будете выверять сколько запятой в каждой строке.

Итак, нам нужно найти порядковый номер последней запятой, а после уже проводить с ней операции.

Для этого примера, формула принимает такой вид:

Итак, функция ДЛСТР сначала находит количество символов в строчке с запятыми, а потом без них.

А после вычитает из первого — второе. Таким образом мы получаем количество запятых в строчке.

А затем мы заменяем последнюю на восклицательный знак.

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

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

С помощью функции «Заполнить»

Функция «Заполнить», это довольно давний инструмент. Он может помочь нам и в этом случае.

Как он работает?

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

Давайте рассмотрим пример.

Допустим, у нас есть та же табличка:

В первую ячейку столбика В введите то, что должно получиться после обработки;

В следующую ячейку, то же самое;

А теперь выделите столбик;

И щелкните на «Главная» -> «Заполнить» -> «Мгновенное заполнение»;

Готово! Вот результат:

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

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

С помощью Visual Basic

И, как обычно, разберем вариант с помощью Visual Basic.

Мы создадим свою собственную функцию и будем использовать её для обработки данных.

Это крайне удобно, если вы делаете что-либо очень часто. Например, как в нашем случае, удаляете данные после символа.

Код Visual Basic:

Код, чтобы он работал, нужно вставить в Visual Basic -> «Insert» -> «Module».

Давайте рассмотрим пример её использования.

Допустим, у нас есть такая табличка. Формула принимает такой вид:

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

С помощью Visual Basic все проще.

Вот и все! Если вам нужно сделать что-то подобное 1-2 раза, то лучше всего использовать функцию «Найти и заменить…», а если вы делаете это постоянно, то используйте Visual Basic.

Надеюсь, эта статья оказалась полезна для вас!

Источник

Excel убрать все после пробела

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

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

Удалите все символы после последнего пробела

Выберите пустую ячейку, введите формулу =LEFT(A2,FIND(«^^»,SUBSTITUTE(A2,» «,»^^»,LEN(A2)-LEN(SUBSTITUTE(A2,» «,»»))))-1) (A2 — это ячейка, в которой вы удалите все символы после последнего пробела) и перетащите маркер заполнения в нужный диапазон.

Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем!
Подробнее . Бесплатная пробная версия

И тогда вы увидите все символы после удаления последнего пробела в каждой ячейке. Смотрите скриншот:

Удалите все символы после первого пробела

Выберите пустую ячейку, введите формулу = ЛЕВЫЙ (A2; НАЙТИ («»; A2) -1) (A2 — это ячейка, в которой вы удалите все символы после первого пробела), и перетащите маркер заполнения вниз до нужного диапазона.

Простое разделение ячеек на несколько ячеек / строк (список столбцов) в Excel

Обычно пользователи Excel могут применять Текст в столбцы функция разбить одну ячейку на несколько столбцов, но нет прямого метода преобразования одной ячейки в несколько строк. Однако Kutools для Excel Разделить клетки Утилита может помочь вам легко сделать это, как показано на скриншоте ниже.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Get It Now

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

1. Нажмите другой + F11 одновременно клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модули, а затем скопируйте и вставьте следующий код в новое окно модуля.

VBA: удалить все символы после последнего пробела в Excel

3. Сохраните функцию, определяемую пользователем, и закройте окно Microsoft Visual Basic для приложений.

4. Выберите пустую ячейку и введите формулу. = RemoveAfterLastSpace (A2) (A2 — это ячейка, в которой вы удалите все символы после последнего пробела), и перетащите маркер заполнения в нужный диапазон.

И тогда вы увидите все символы после удаления последнего пробела из каждой ячейки. Смотрите скриншот:

Этот метод представит Kutools for Excel’s Разделить имена утилита для быстрого удаления всех символов после первого пробела из ячеек в Excel.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Выберите диапазон, в котором вы удалите все символы после первого пробела из каждой ячейки, и щелкните Кутулс > Текст > Разделить имена. Смотрите скриншот:

2. В открывшемся диалоговом окне Split Names отметьте только Имя и нажмите Ok кнопку.

3. Затем укажите первую ячейку целевого диапазона во втором диалоговом окне «Разделить имена» и нажмите кнопку OK кнопку.

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

Источник

Как удалить символы После знака в Excel

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

Видеоинструкция

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

Для этого потребуется использовать формулу:

А1 — ячейка, которую мы будет проверять;
Символ — символ или текст, после которого будет обрезаться ячейка.

Также можно комбинировать этот способ с очисткой данных «До».

Удаление знака после запятой в Эксель

Выполнить эту задачу можно при помощи конструкции:

После пробела в Excel

Сделать это можно через формулу:

Подписывайтесь на наш канал в Яндекс.Дзен

Подписывайтесь на наш Telegram-канал

Подписывайтесь на наш Youtube-канал

Подписывайтесь на нашу группу ВКонтакте

Вам также будет интересно

Комментарии 2

А если после запятой надо оставить только (например) три знака в числовом формате, то надо так: =ПСТР(А3;1;ПОИСК(«,»;А3)+3)
При этом, (опять же например)
исходное значение: 23,558668; готовый результат: 23,558

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

Источник

Иногда вам может потребоваться удалить все тексты после или до второго или n-го конкретного разделителя (например, пробела, запятой и т. Д.), Как показано на следующем снимке экрана, в этой статье я расскажу о некоторых простых формулах для решения этой задачи в Excel .

Удалить текст после второго или n-го определенного разделителя (пробела, запятой и т. Д.) Из текстовых строк
Удалить текст перед вторым или n-м разделителем (пробел, запятая и т. Д.) Из текстовых строк

Удалить текст после второго или n-го определенного разделителя (пробела, запятой и т. Д.) Из текстовых строк

В Excel для удаления текста после второго или n-го пробела или других разделителей функции LEFT, SUBSTITUTE и FIND могут оказать вам услугу. Общий синтаксис:

=LEFT(SUBSTITUTE(cell,» «,»#»,N+1),FIND(«#»,SUBSTITUTE(cell,» «,»#»,N),1)-1)

cell: Ссылка на ячейку или текстовую строку, из которой вы хотите удалить текст.
N: Представляет собой n-й разделитель, на основе которого вы хотите удалить текст.

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

=LEFT(SUBSTITUTE(A2,» «,»#»,3),FIND(«#»,SUBSTITUTE(A2,» «,»#»,2),1)-1)

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

Пояснение к формуле:

1. ЗАМЕНА (A2, «», «#», 3): Эта функция ЗАМЕНА используется для замены третьего пробела конкретным символом # в ячейке A2. Он получит следующее: «Том Хилл, Хьюстон, штат Техас, США». Эта часть формулы распознается как текстовый аргумент функции LEFT.

2. FIND(«#»,SUBSTITUTE(A2,» «,»#»,2),1)-1: Эта часть формулы преобразована в аргумент num_chars функции LEFT.

ПОДСТАВИТЬ (A2; «», «#»; 2): Функция ЗАМЕНА заменит второй пробел на определенный символ # в ячейке A2. И вы получите следующий результат: «Tom Hill # Houston Texas US».
НАЙТИ («#», ПОДСТАВИТЬ (A2, «», «#», 2), 1) -1 = НАЙТИ («#», «Том Хилл # Хьюстон, штат Техас, США», 1) -1: Эта функция НАЙТИ используется для нахождения позиции символа # в текстовой строке, возвращаемой функцией ЗАМЕНА, начиная с первого символа. Вычитание 1 означает исключение пробела. И это будет цифра 8.

3. LEFT (SUBSTITUTE (A2; «», «#», 3), FIND («#», SUBSTITUTE (A2, «», «#», 2), 1) -1) = LEFT («Tom Hill Houston # Техас США «, 8): Наконец, эта функция LEFT извлечет 8 символов из левой части текстовой строки в ячейке A2.

Ноты:

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

2. Чтобы удалить текст после n-го конкретного разделителя, измените n-е число по своему усмотрению, например, чтобы удалить текст после третьего пробела, примените следующую формулу:

=LEFT(SUBSTITUTE(A2,» «,»#»,4),FIND(«#»,SUBSTITUTE(A2,» «,»#»,3),1)-1)

Удалить текст перед вторым или n-м разделителем (пробел, запятая и т. Д.) Из текстовых строк

Чтобы удалить текст перед вторым или n-м определенным разделителем, вам могут помочь функции ВПРАВО, ДЛИН, НАЙТИ и ЗАМЕНА. Общий синтаксис:

=RIGHT(cell,LEN(cell)-FIND(«#»,SUBSTITUTE(cell,» «,»#»,N)))

cell: Ссылка на ячейку или текстовую строку, из которой вы хотите удалить текст.
N: Представляет собой n-й разделитель, на основе которого вы хотите удалить текст.

Скопируйте или введите приведенную ниже формулу в пустую ячейку:

=RIGHT(A2,LEN(A2)-FIND(«#»,SUBSTITUTE(A2,» «,»#»,2)))

Затем перетащите маркер заполнения вниз к ячейкам, чтобы применить эту формулу, и вы получите нужный результат:

Пояснение к формуле:

1. LEN (A2) -FIND («#», ПОДСТАВИТЬ (A2, «», «#», 2)) ：Эта часть формулы распознается как аргумент num_chars функции RIGHT.

ПОДСТАВИТЬ (A2; «», «#»; 2): Функция ЗАМЕНА заменит второй пробел на определенный символ # в ячейке A2. И вы получите следующий результат: «Tom Hill # Houston Texas US».
НАЙТИ («#», ПОДСТАВИТЬ (A2, «», «#», 2)) = НАЙТИ («#», «Том Хилл # Хьюстон, штат Техас, США»): Эта функция НАЙТИ получит позицию символа # в текстовой строке, возвращаемой функцией ПОДСТАВИТЬ. И он получит номер 9.
LEN(A2)-FIND(«#»,SUBSTITUTE(A2,» «,»#»,2))=25-9: Из общей длины ячейки A2 вычитается позиция второго пробела, чтобы получить номер оставшегося символа. Получится номер 16.

2. RIGHT(A2,LEN(A2)-FIND(«#»,SUBSTITUTE(A2,» «,»#»,2)))=RIGHT(A2, 16): Эта функция ВПРАВО используется для извлечения символов из правой части текста в ячейке A2.

Ноты:

1. Если есть какие-либо другие разделители для разделения вашей текстовой строки, вам просто нужно заменить символ пробела на другие, которые вам нужны.

2. Чтобы удалить текст до n-го определенного разделителя, измените n-е число по своему усмотрению, например, чтобы удалить текст после третьего пробела, примените формулу ниже:

=RIGHT(A2,LEN(A2)-FIND(«#»,SUBSTITUTE(A2,» «,»#»,3)))

Используемые относительные функции:

LEN:
Функция LEN возвращает количество символов в текстовой строке.

LEFT:
Функция LEFT извлекает заданное количество символов из левой части предоставленной строки.

RIGHT:
Функция RIGHT используется для извлечения определенного количества символов из правой части текстовой строки.

FIND:
Функция НАЙТИ используется для поиска строки в другой строке и возвращает начальную позицию строки внутри другой.

SUBSTITUTE:
Функция ЗАМЕНА в Excel заменяет текст или символы в текстовой строке другим текстом или символами.

Другие статьи:

Удаление текста до или после первого или последнего определенного символа из текстовых строк
В этом руководстве будет рассказано о том, как удалить текст до или после первого или последнего определенного символа, такого как пробел, запятая, из списка текстовых строк в Excel.

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

Удаление или удаление числовых символов из текстовых строк
Если вы хотите удалить все числа только из списка текстовых строк, но сохранить другие нечисловые символы, возможно, некоторые формулы в Excel могут оказать вам услугу.

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

Лучшие инструменты для работы в офисе

Kutools for Excel — Помогает вам выделиться из толпы

Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.

Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.

Вкладка Office — включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

Одна секунда для переключения между десятками открытых документов!
Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.

Источник

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

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

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

Удаляем все пробелы при помощи «Найти и заменить»
Используем формулу СЖПРОБЕЛЫ
Удаление начальных и концевых пробелов формулой
Как убрать разрыв строки и непечатаемые символы
Формула для удаления неразрывных пробелов
Как найти и удалить непечатаемый символ
Как цифры с пробелами преобразовать в число
Считаем пробелы при помощи формулы
Простой способ удаления пробелов без формул.

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

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

Как убрать пробелы в Excel при помощи «Найти и заменить»

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

Удаление двойных интервалов.

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

Итак, найдем и заменим двойные интервалы независимо от их расположения.

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

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

Нажмите «Заменить все».

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

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

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

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

Найти: одинарный пробел.

Заменить на: оставьте это поле пустым.

Нажмите «Заменить все».

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

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

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

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

Стандартный синтаксис очень простой:

=СЖПРОБЕЛЫ(A2)

Где A2 — ячейка, из которой вы хотите удалить.

Как показано на следующем скриншоте, СЖПРОБЕЛЫ успешно удалила всё до и после текста, а также лишние интервалы в середине строки.

И теперь вам нужно только заменить значения в исходном столбце новыми. Самый простой способ сделать это — использовать Специальная вставка > Значения.

Формулы для удаления начальных и концевых пробелов.

В некоторых ситуациях вы можете вводить двойные или даже тройные интервалы между словами, чтобы ваши данные были более удобочитаемыми. Однако вам нужно избавиться от ведущих пробелов (находящихся в начале), например:

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

=ПСТР(A2;НАЙТИ(ПСТР(СЖПРОБЕЛЫ(A2);1;1);A2);ДЛСТР(A2))

Это выражение в начале вычисляет позицию первого знака в строке. Затем вы передаете это число другой функции ПСТР, чтобы она возвращала всю текстовую строку (длина строки рассчитывается с помощью ДЛСТР), начиная с позиции первого знака.

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

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

=ЛЕВСИМВ(A2;МАКС((ПСТР(A2&ПОВТОР(» «;99);СТРОКА(A2:A100);1)<>» «)*СТРОКА(A2:A100)))

И обратите снимание, что ее нужно вводить как формулу массива (с Ctrl+Shift+Enter). В столбце A выровнять по правому краю получилось плохо из-за разного количества концевых пробелов в каждой ячейке. В столбце B эта проблема решена, и можно красиво расположить текст.

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

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

Функция СЖПРОБЕЛЫ может избавиться от пробелов, но не может устранить непечатаемые символы. Технически она предназначена для удаления только значения 32 в 7-битной системе ASCII , которое как раз и является кодом пробела.

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

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

=СЖПРОБЕЛЫ(ПЕЧСИМВ(A2))

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

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

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

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

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

Если после использования формулы СЖПРОБЕЛЫ и ПЕЧСИМВ некоторые упрямые знаки все еще остаются, то скорее всего, вы скопировали / вставили данные из интернета или из другой программы, и несколько неразрывных пробелов все же прокрались в вашу таблицу.

Чтобы избавиться от неразрывных пробелов (html-код   ), замените их обычными, а затем попросите функцию СЖПРОБЕЛЫ удалить их:

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

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

Неразрывный пробел имеет код 160 в 7-битной системе ASCII, поэтому вы можете определить его с помощью СИМВОЛ(160).
Функция ПОДСТАВИТЬ используется для превращения неразрывных пробелов в обычные.
И, наконец, вы вставляете ПОДСТАВИТЬ в СЖПРОБЕЛЫ, чтобы окончательно удалить всё лишнее.

Если ваш рабочий лист также содержит непечатаемые символы, дополнительно к описанному выше используйте ещё функцию ПЕЧСИМВ, чтобы избавиться от пробелов и других ненужных знаков одним махом:

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

Следующий скриншот демонстрирует разницу в результатах:

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

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

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

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

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

=КОДСИМВ(ЛЕВСИМВ(A2;1))

Конечный пробел или непечатаемый символ в конце строки:

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

Пробел или непечатаемый символ в середине строки, где n — позиция проблемного знака:

= КОДСИМВ(ПСТР(A2, n, 1)))

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

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

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

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

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

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

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

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

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

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

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

Чтобы удалить все пробелы одним махом, используйте ПОДСТАВИТЬ, как описано в предыдущем примере, с той лишь разницей, что вы заменяете знак пробела, возвращаемый при помощи СИМВОЛ(32), ничем («»):

=ПОДСТАВИТЬ(A2; СИМВОЛ(32); «»)

Или вы можете просто ввести его (» «) в формуле, например:

=ПОДСТАВИТЬ(A2; “ “; «»)

Результатом этого будет текст, состоящий из цифр. Если же в качестве результата вам нужны именно числа, добавьте перед формулой два знака “-“ (минус). Любая математическая операция автоматически превращает цифры в число. А дважды применив минус, то есть дважды умножив на минус 1, мы не изменим величину числа и его знак.

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

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

Вычислите всю длину строки с помощью функции ДЛСТР: ДЛСТР (A2)
Замените все пробелы ничем: ПОДСТАВИТЬ(A2; » «; «»)
Вычислить длину строки без пробелов: ДЛСТР(ПОДСТАВИТЬ(A2; » «; «»))
Вычтите этот результат из первоначальной длины.

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

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

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

=ДЛСТР(A3)-ДЛСТР(СЖПРОБЕЛЫ(A3))

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

Теперь, когда вы знаете, сколько пробелов содержит каждая ячейка, вы можете безопасно удалить лишние, используя функцию СЖПРОБЕЛЫ.

Простой способ удаления пробелов без формул.

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

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

После установки Ultimate Suite добавляет на ленту Excel несколько полезных кнопок, таких как «Удалить пробелы» , «Удалить символы» , «Преобразовать текст» и многое другое.

Всякий раз, когда вы хотите удалить лишние пробелы в таблицах Excel, выполните следующие 4 быстрых шага:

Выделите ячейки (диапазон, весь столбец или строку), в которых вы хотите удалить лишние пробелы.
Нажмите кнопку «Trim Spaces» на вкладке «Ablebits Data».
Выберите один или несколько вариантов:
- Обрезать начальные и конечные пробелы.
- Удалить лишние пробелы между словами, кроме одного.
- Удалить неразрывные пробелы ( )
- Удалить лишние переносы строк до и после значений, между значениями оставить только один.
- Удалить все переносы строк в ячейке.
Нажмите кнопку «Trim» .

Готово! Все лишние пробелы удалены одним щелчком мыши:

Здесь мы вместе с лишними пробелами удалили еще и переводы строки, чтобы значения располагались в привычном виде, в одну строку.

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

ознакомительную версию Ultimate Suite. Благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!

Формула ЗАМЕНИТЬ и ПОДСТАВИТЬ для текста и чисел — В статье объясняется на примерах как работают функции Excel ЗАМЕНИТЬ (REPLACE в английской версии) и ПОДСТАВИТЬ (SUBSTITUTE по-английски). Мы покажем, как использовать функцию ЗАМЕНИТЬ с текстом, числами и датами, а также…

Как убрать пробелы в числах в Excel — Представляем 4 быстрых способа удалить лишние пробелы между цифрами в ячейках Excel. Вы можете использовать формулы, инструмент «Найти и заменить» или попробовать изменить формат ячейки. Когда вы вставляете данные из…

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

Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…

Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…

5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…

Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…

Источник

ateccc

Пользователь

Сообщений: 7
Регистрация: 17.10.2014

Заранее прошу прощения, если решение уже есть. Но по поиску не нашел.
Суть задачи:
Есть список названий товаров, последнее слово после пробела — это код товара. Он может быть разным и текст и цифры.
Необходимо: удалить слово после последнего пробела.
А в иделе — еще и поместить его в соседний столбец.
Excel 2003

Прикрепленные файлы

пример.xls (28.5 КБ)

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

18.10.2014 00:03:43

Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(A2;ПОИСК("/";ПОДСТАВИТЬ(A2;" ";"/";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;" ";))));50);")";);"(";)

ateccc

Пользователь

Сообщений: 7
Регистрация: 17.10.2014

Может не совсем понятно сформулировал, но мне важно получить значения Названия товара без слова после последнего пробела, а выделить последнее слово в отдельный столбец это уже не главная а второстепенная задача.
Было: Портмоне мужской Armani (11082)
Стало: Портмоне мужской Armani

Поможете ?

Изменено: ateccc — 18.10.2014 00:20:54

Doober

Пользователь

Сообщений: 2204
Регистрация: 09.04.2013

18.10.2014 00:12:30

Не успеешь за формулистами. .
Удаляет с первого столбца и переносит в соседний

Код

Sub Extract()
    Dim Rng As Range
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Pattern = "s(S+)$"
    For Each Rng In ActiveSheet.UsedRange.Columns(1).Cells
        If Rng.Value <> "" Then
            bRes = RegExp.test(Rng.Value)
            If bRes Then
                Set oMatches = RegExp.Execute(Rng.Value)
                Rng.Offset(0, 1) = Replace(Replace(oMatches(0).subMatches(0), "(", ""), ")", "")
                Rng.Value = RegExp.Replace(Rng.Value, "")
            End If
        End If
    Next
End Sub

<#0>

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	Это проще. Как там у скульпторов? Отсекаете у показанной формулы лишнее — и получаете… если не шедевр, то нужную формулу =ЛЕВСИМВ(A2;ПОИСК(«/»;ПОДСТАВИТЬ(A2;» «;»/»;ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;» «;))))-1)

ateccc Пользователь Сообщений: 7 Регистрация: 17.10.2014	vikttur, у меня Ваша формула не работает, предлагает внести исправления по количеству скобок, можете скинуть файл с формулой уже. Doober, спасибо. Макрос работает)

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	ateccc, тут в соседней теме говорили про избыточное цитирование — у Вас та же проблема. Зачем вообще процитировали, да ещё всё целиком?

ateccc Пользователь Сообщений: 7 Регистрация: 17.10.2014

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

18.10.2014 01:36:15

Цитата
— Вы за меня и кушать будете? Двое_из_ларца: — Ага!

Прикрепленные файлы

коды.xls (29.5 КБ)

ateccc Пользователь Сообщений: 7 Регистрация: 17.10.2014

JeyCi

Пользователь

Сообщений: 3357
Регистрация: 27.11.2013

#11

20.10.2014 23:51:32

Цитата
Doober пишет: Не успеешь за формулистами. Удаляет с первого столбца и переносит в соседний RegExp.Pattern = «s(S+)$»

Doober , это, наверно, я ещё не успеваю понимать так быстро… но весь код прочитать смогла :oops: кроме главного нюанса… вижу: s

пробел — ( — без пробела несколько символов — ) — окончание строки…

но ведь «(» соответствует последовательность «(» синтаксисом регулярок … правильно ли я вижу?? или «s(» по логике «пробел и скобка» тоже правильно?… или символ всегда пишется между слэшем и скобкой (например, n( -новая строка начинающаяся со скобки или что-нибудь ещё) … или скобка всегда понятна и без слэша?… пользовалась

подсказкой Максима

, но подумала многое, а уверенности никакой … ребята, извините, но я уже по буквам начинаю разбираться… p.s. с клавиатурой мы летом разобрались вашими стараниями…

Изменено: JeyCi — 20.10.2014 23:52:01

чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок — обратитесь к собеседнику на ВЫ — ответ на ваш вопрос получите — а остальное вас не касается (п.п.п. на форумах)

JeyCi

Пользователь

Сообщений: 3357
Регистрация: 27.11.2013

#12

10.12.2014 17:43:11

всё-таки сработало…
— не могу не поддержать ветку идеей

отсюда

— вошла во вкус к Replace’у

Код

Sub t()
  With CreateObject("vbscript.regexp")
    .ignorecase = True: .Global = True: .MultiLine = True: .Pattern = "(s(S+)$)"
    For Each c In Selection: c.Value = .Replace(c.Value, ""): Next 
  End With
End Sub

Johny

Пользователь

Сообщений: 2737
Регистрация: 21.12.2012

#13

10.12.2014 21:15:52

Так?

Код

' Без последней части
Function FixCell(cell As Range)
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True: .Pattern = "^(.+)s+(?.+)?$"
        FixCell = .Execute(cell.Value)(0).SubMatches(0)
    End With
End Function

' Последняя часть
Function LastPart(cell As Range)
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True: .Pattern = "^.+s+(?(.+?))?$"
        LastPart = .Execute(cell.Value)(0).SubMatches(0)
    End With
End Function

Прикрепленные файлы

пример.xls (74 КБ)

There is no knowledge that is not power

JeyCi

Пользователь

Сообщений: 3357
Регистрация: 27.11.2013

#14

10.12.2014 22:15:36

тогда попробую расшифровать — чтобы понять

Цитата
Johny пишет: Pattern = «^(.+)s+(?.+)?$»

? распознаём: (шаблон любое количество символов), пробел или несколько пробелов, открывающая скобка, несколько символов, закрывающая скобка, символ, конец строки… точки означают отношение к объекту ( VBScript.RegExp)… ^ значит исключаем это рег. выражение… FixCell = .Execute(cell.Value)(0).SubMatches(0) — вобщем убираем? (SubMatches(0)) первое встретившееся такое выражение: всё что в скобках (.+), пробелы s+, открывающую скобку (?, закрывающая скобка после нескольких символов .+)? за которыми конец строки $

Цитата
Johny пишет: Pattern = «^.+s+(?(.+?))?$»

вобщем убираем первое встретившееся (SubMatches(0)) такое выражение: любое количество символов .+, пробел или несколько s+, открывающую скобку (?, за которой шаблон (.+?), который не совсем понимаю ЗАЧЕМ вносить в исключения, после чего закрывающая скобка )? за которыми конец строки $

Johny

признайтесь честно что написали , а то лишили меня спокойного сна… если я неверно расшифровываю, то предупредите please… а то ведь не усну… особенно выделенное зелёным смущает… и скажу честно, я не ТС и на вопрос «так ли?» — ответить всё равно не смогу :oops: а понять интересно

Johny

Пользователь

Сообщений: 2737
Регистрация: 21.12.2012

#15

10.12.2014 22:40:48

Кстати, в FixCell (? и )? необязательны — их можно удалить. Привожу пояснение с этой поправкой.

Код
.Pattern = "^(.+)s+.+$"

Всё очень просто.
^ и $ определяют всю строку.
Раз нам надо «до последнего пробела», то мы знаем, что первый пробел с конца.
Символ $ нам даёт конец текста. .+ даёт весь текст в конце, а s+ и есть наш последний пробел.
Всё, что до него — это наша искомая строка, то есть с начала строки ^ до последнего пробела s+.

LastPart.

Код
"^.+s+(?(.+?))?$"

Тут принцип такой же, что и в FixCell, но я увидел, что в некоторых словах нет скобок вокруг последнего слова. А так как автору нужен текст без скобок, то (? и )? помогают нам исключить их, если таковые есть. Другими словами, если их не написать, то (.+?) заберёт и скобки, но они нам не нужны. Остальное — всё как в FixCell.
Что касается SubMatches(0), то это просто наш искомый текст, заключённый в скобки. Он считается слева направо: первый — 0, второй — 1 и так далее.
Если использовать Early Binding, то можно расписать попонятней и подробней. Для этого указываем библиотеку (Tools -> References ->Microsoft VBScript Regular Expressions 5.5).

Код

 Function LastPart(cell As Range)
    
    Dim re As RegExp
    Dim mc As MatchCollection
    Dim m As Match
    Dim submc As SubMatches
    
    Set re = New RegExp
    re.Pattern = "^.+s+(?(.+?))?$"
    Set mc = re.Execute(cell.Value)
    Set m = mc.Item(0)
    Set submc = m.SubMatches
    LastPart = submc.Item(0)
    
End Function

Изменено: Johny — 10.12.2014 22:43:29

There is no knowledge that is not power

B.Key Пользователь Сообщений: 633 Регистрация: 26.02.2014	Можно и по попроще разбиваем сплитом на массив, переоределям размерность массива и джоиним новый массив

Johny

Пользователь

Сообщений: 2737
Регистрация: 21.12.2012

#17

10.12.2014 23:00:58

Цитата
B.Key пишет: Можно и по попроще

Я дико извиняюсь, но какой смысл вкладывается в слово «попроще»?

There is no knowledge that is not power

JeyCi

Пользователь

Сообщений: 3357
Регистрация: 27.11.2013

ой, господа, мне это до следующего года занятие — уложить всё это в мозг… поэтому не ждите быстрого понимания … но всё равно большое спасибо за ответ!.. теперь засну… потому что в общем и целом

Johny

, кажется мне, вы за сегодня успели дать мне и статью почитать и сами по-быстрому резюме той статьи составили… respect… успехов вам по жизни — чтобы всё было так же быстро, (очень) полномасштабно и с пользой
p.s.

B.Key

мы тут просто случайно за рэгэксп зацепились (не судите строго) ради интереса для общего развития — на примере этой ветки… и кстати очень простые объяснения получились у

Johny

… читать приятнее, чем нобелевских лауреатов или ещё кого — там вообще ничего не понять… а тут на форуме очень многих очень приятно читать

Изменено: JeyCi — 10.12.2014 23:13:56

B.Key Пользователь Сообщений: 633 Регистрация: 26.02.2014	Попроще это без регэкспа ——— Для меня это просто, для обычного поль зователя это вынос мозга.

Johny

Пользователь

Сообщений: 2737
Регистрация: 21.12.2012

#20

10.12.2014 23:10:11

Цитата
B.Key пишет: Попроще этобез регэкспа

Феерично! Ну хотя да — огород из джоинов и сплитов, действительно, будет попроще. Лет десять назад я бы так и сделал.

There is no knowledge that is not power

B.Key

Пользователь

Сообщений: 633
Регистрация: 26.02.2014

#21

11.12.2014 00:23:48

Johny, куда уж мне до Вас с Вашим опытом и 10 летним стажем.

Скрытый текст

RAN

Пользователь

Сообщений: 7091
Регистрация: 21.12.2012

#22

11.12.2014 00:48:55

B.Key,

Код
Function insTxt(txt) insTxt = Left$(txt, InStrRev(txt, " ") - 1) End Function

Изменено: RAN — 11.12.2014 00:49:22

B.Key

Пользователь

Сообщений: 633
Регистрация: 26.02.2014

#23

11.12.2014 00:56:13

Я согласен полностью, instr еще быстрее и проще, меня удивило другое:

Цитата
Johny пишет: Лет десять назад я бы так и сделал.

Johny

Пользователь

Сообщений: 2737
Регистрация: 21.12.2012

#24

11.12.2014 21:37:12

Цитата
B.Key пишет: меня удивило другое:

Ну вот через 10 лет и поймёте…

There is no knowledge that is not power

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле

Johny Пользователь Сообщений: 2737 Регистрация: 21.12.2012	Я имел ввиду, что B.Key через 10 лет прокачает скилл и будет использовать регэкспы. There is no knowledge that is not power

Юрий М Модератор Сообщений: 60585 Регистрация: 14.09.2012 Контакты см. в профиле	Может и будет, но показанный им пример говорит о том, что по скорости выигрыш не за Вами)) На моей машине: 0,0625 split 6,796875 regexp === Вот и хотелось бы понять, почему лет через 10 B.Key должен будет перейти на регулярки?

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	Где наш главный защитник крепких регулярных выражений? Саша!!! Ты где?! 0,0546875 split 6,773438 regexp

Johny Пользователь Сообщений: 2737 Регистрация: 21.12.2012	А с Early Binding? There is no knowledge that is not power

RAN

Пользователь

Сообщений: 7091
Регистрация: 21.12.2012

#30

12.12.2014 00:01:59

Цитата
vikttur пишет: Саша!!! Ты где?!

Я за него.
Всего 1 строчка, и если не сравнялись, то существенно приблизились.

Скрытый текст

Источник

Как убрать информацию в ячейке после последнего пробела?

Автор amk1come, 02.08.2012, 08:59

« назад — далее »

Подскажите, пожалуйста, как убрать информацию в ячейке после последнего пробела (вместе с самим последним пробелом)?

Пример:

Исходник:

Долго снились мне вопли рыданий твоих

Результат:

Долго снились мне вопли рыданий

Спасибо!

Вот такой монстрик получился

=ЛЕВСИМВ(A1;ПОИСК(A1;ПОДСТАВИТЬ(A1;" ";A1;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))-1)
Если предвидятся ситуации, что в проверяемой ячейке вообще нет пробелов, то нужно добавить проверку на ошибку
Например, так:

=ЛЕВСИМВ(A1;ПОИСК(A1&" ";ПОДСТАВИТЬ(A1&" ";" ";A1&" ";МАКС(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""));1)))-1)
если слово нужно оставлять
и так:

=ЛЕВСИМВ(A2;ПОИСК(A2;ПОДСТАВИТЬ(A2;" ";A2;МАКС(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;" ";""));1)))-1)
если не нужно

Скажи мне, кудесник, любимец ба’гов…

Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

Или такой:
=ПСТР(A1;1;ПРОСМОТР(999;ПОИСК(» «;A1;СТРОКА(1:999)))-1)

Профессиональные приемы работы в Microsoft Excel
►
Обмен опытом
►
Microsoft Excel
►
Как убрать информацию в ячейке после последнего пробела?

Источник

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

С помощью функции «Найти и заменить»

С помощью формул

С помощью функции «Заполнить»

С помощью Visual Basic

Excel убрать все после пробела

Простое разделение ячеек на несколько ячеек / строк (список столбцов) в Excel

Как удалить символы После знака в Excel

Видеоинструкция

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

Удаление знака после запятой в Эксель

После пробела в Excel

Вам также будет интересно

Комментарии 2

Удалить текст после второго или n-го определенного разделителя (пробела, запятой и т. Д.) Из текстовых строк

Пояснение к формуле:

Ноты:

Удалить текст перед вторым или n-м разделителем (пробел, запятая и т. Д.) Из текстовых строк

Пояснение к формуле:

Ноты:

Используемые относительные функции:

Другие статьи:

Лучшие инструменты для работы в офисе

Kutools for Excel — Помогает вам выделиться из толпы

Вкладка Office — включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

Как убрать пробелы в Excel при помощи «Найти и заменить»

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

Формулы для удаления начальных и концевых пробелов.

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

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

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

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

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

Простой способ удаления пробелов без формул.

Как убрать информацию в ячейке после последнего пробела?

А вот еще интересные статьи: