Как найти пробел в экселе
Перейти к содержимому

Как найти пробел в экселе

  • автор:

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

  • Эти функции могут быть доступны не на всех языках.
  • Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

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

Замечание

  • Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.
  • В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (~).
  • Если значение find_text не найдено, #VALUE! Возвращается значение ошибки.
  • Если аргумент начальная_позиция опущен, то он полагается равным 1.
  • Если start_num не больше 0 (ноль) или больше длины аргумента within_text , #VALUE! Возвращается значение ошибки.
  • Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

В этой статье описаны синтаксис формулы и использование функции СЖПРОБЕЛЫ в Microsoft Excel.

Описание

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

Важно: Функция СЖПРОБЕЛЫ предназначена для удаления из текста знаков пробела 7-разрядного кода ASCII (значение 32). В наборе знаков Юникода существует дополнительный знак пробела, который называется знаком неразрывного пробела и имеет десятичное значение 160. Этот знак обычно используется на веб-страницах как сущность HTML  . Сама по себе функция СЖПРОБЕЛЫ не удаляет этот знак неразрывного пробела. Пример того, как обрезать оба пробела из текста, см. в разделе Десять основных способов очистки данных.

Синтаксис

Аргументы функции СЖПРОБЕЛЫ описаны ниже.

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

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.

=СЖПРОБЕЛЫ(» Доход за первый квартал «)

Удаляет начальные и конечные пробелы из текста в формуле («Доход за первый квартал»)

Доход за первый квартал

Подсветка лишних пробелов

highlight-bad-spaces1.png

Предположим, что мы создали форму для ввода данных пользователем – например, такую: При вводе всегда есть вероятность некорректного ввода информации, «человеческий фактор». Один из вариантов его проявления– лишние пробелы. Кто-то ставит их случайно, кто-то – намеренно, но, в любом случае, даже один лишний пробел будет представлять для вас проблему в дальнейшем при обработке введенной информации. Дополнительная «прелесть» в том, что их еще и не видно, хотя, если очень хочется, то можно сделать видимыми с помощью макроса. Безусловно, можно и нужно «причесывать» информацию после ввода с помощью специальных функций или макросов. А можно подсвечивать некорректно введенные данные прямо в процессе заполнения формы, оперативно сигнализируя об ошибке пользователю. Для этого:

  1. Выделите поля ввода, где нужна проверка на лишние пробелы (желтые ячейки в нашем примере).
  2. Выберите на Главной вкладке команду Условное форматирование – Создать правило (Home – Conditional Formatting — Create Rule) .
  3. Выберите тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cells to format) и введите в поле следующую формулу:

highlight-bad-spaces2.png

где D4 – адрес текущей ячейки (без знаков «$»).

В английской версии это будет, соответственно =G4<>TRIM(G4)

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

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

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

Ссылки по теме

  • Зачистка текста от лишних пробелов, непечатаемых символов, латиницы и т.д.
  • Инструменты для удаления лишних пробелов из надстройки PLEX
  • Защита листов, книг и файлов в Microsoft Excel

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

Из этой статьи Вы узнаете 2 быстрых способа удалить лишние пробелы между словами или все пробелы из ячеек Excel. Вы можете использовать функцию TRIM (СЖПРОБЕЛЫ) или инструмент Find & Replace (Найти и заменить), чтобы вычистить содержимое ячеек в Excel.

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

Следовательно, таблица выглядит малость неопрятно и становится трудной для использования. Казалось бы, простая задача может стать непростой. Например, найти покупателя с именем John Doe (нет лишних пробелов между частями имени), в то время как в таблице он сохранён как «John Doe«. Или числа, которые не могут быть просуммированы, а виноваты в этом опять лишние пробелы.

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

  • Удаляем все лишние пробелы между словами, а также отсекаем начальные и конечные пробелы
    • Используем функцию TRIM (СЖПРОБЕЛЫ)
    • Применяем инструмент Find & Replace (Найти и заменить)

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

    Предположим, есть таблица с двумя столбцами. В столбце Name в первой ячейке содержится имя John Doe, записанное корректно, т.е. без лишних пробелов. Все остальные ячейки содержат вариант записи с лишними пробелами между именем и фамилией, а также в начале и в конце (начальные и конечные пробелы). Во втором столбце, с названием Length, показано количество символов в каждом имени.

    Удаляем лишние пробелы в Excel

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

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

    1. Рядом со своими данными добавьте вспомогательный столбец. Можете назвать его Trim.
    2. В первой ячейке вспомогательного столбца (C2) введите формулу для удаления лишних пробелов: =TRIM(A2)
      =СЖПРОБЕЛЫ(A2) Удаляем лишние пробелы в Excel
    3. Скопируйте эту формулу в остальные ячейки столбца. Вы можете использовать советы из статьи Как вставить одинаковую формулу сразу во все выбранные ячейки .
    4. Замените исходный столбец полученными данными. Для этого выделите все ячейки вспомогательного столбца и нажмите Ctrl+C, чтобы скопировать данные в буфер обмена.Далее выделите первую ячейку исходного столбца (в нашем случае A2), нажмите Shift+F10 или клавишу вызова контекстного меню, а затем клавишу V (З).Удаляем лишние пробелы в Excel
    5. Удалите вспомогательный столбец.

    Готово! Мы удалили все лишние пробелы при помощи функции TRIM (СЖПРОБЕЛЫ). К сожалению, этот способ занимает много времени, особенно, когда таблица достаточно велика.

    Удаляем лишние пробелы в Excel

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

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

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

    Удаляем лишние пробелы в Excel

    1. Выделите один или несколько столбцов с данными, в которых нужно удалить лишние пробелы между словами.
    2. Нажмите Ctrl+H, чтобы открылось диалоговое окно Find & Replace (Найти и заменить).
    3. Введите два раза пробел в поле Find What (Найти) и один раз в поле Replace With (Заменить на).
    4. Нажмите кнопку Replace all (Заменить все), а затем ОК, чтобы закрыть появившееся информационное окно.
    5. Повторяйте шаг 4 до тех пор, пока не появится сообщение We couldn’t find anything to replace… (Мы не нашли ничего, что нужно было заменить…).

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

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

    Удаляем лишние пробелы в Excel

    Простейший способ избавиться от лишних пробелов – это воспользоваться стандартным инструментом Excel – Find & Replace (Найти и заменить).

    Удаляем лишние пробелы в Excel

    • Нажмите Ctrl+Space (Пробел), чтобы выделить все ячейки в столбце.
    • Нажмите Ctrl+H, чтобы открыть диалоговое окно Find & Replace (Найти и заменить).
    • В поле Find What (Найти) введите один пробел. Убедитесь, что поле Replace With (Заменить на) – пустое.
    • Нажмите кнопку Replace all (Заменить все), затем ОК. Вуаля! Все пробелы удалены.

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

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

    Здесь A1 – это первая ячейка столбца с числами или словами, в которой должны быть удалены все пробелы.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *