Excel как найти пробел в ячейке excel

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

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

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

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

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

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

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

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

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

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

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

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

Типичная ошибка, которая встречается в электронных таблицах, связана с невидимыми символами пробелов. [1] В ячейке В2 (рис. 1) находится формула, берущая название цвета в ячейке В1 и возвращающая соответствующий код из таблицы D1:E6: =ВПР(B1;D1:E6;2;ЛОЖЬ).

Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1

Скачать заметку в формате Word или pdf, примеры в формате Excel

В следующем примере формула в ячейке В2 возвращает ошибку, указывая, что значение Красный не было найдено в таблице. Сотни и тысячи пользователей Excel потратили массу времени, чтобы понять, почему подобные операции не работают. Ответ прост: в ячейке D5 написано не «Красный», а «Красный », то есть за словом следует пробел. Для Excel это две разные последовательности символов.

Рис. 2. Формула поиска не может найти в таблице слово «Красный»

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

• один ведущий пробел и более;
• один завершающий пробел и более;
• два и более последовательных пробела в самом тексте.

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

1. Выделите все ячейки с текстом, в которых хотите применить условное форматирование.
2. Выполните команду Главная —> Условное форматирование —> Создать правило, чтобы открыть диалоговое окно Создание правила форматирования (рис. 3).
3. В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек.
4. В области Измените описание правила введите формулу: =D2<>СЖПРОБЕЛЫ(D2). Данная формула предполагает, что ячейка D2 является верхней левой ячейкой в диапазоне. Если это не так, замените адрес верхней левой ячейки, который вы указали в шаге 1.
5. Нажмите кнопку Формат, чтобы отобразить диалоговое окно Формат ячеек, и выберите тип форматирования, которое хотите применить к ячейкам, содержащим лишние пробелы, — например, желтую заливку.
6. Нажмите Ok два раза.

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

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

Рис. 4. Условное форматирование выделило ячейки с лишними пробелами

Функция СЖПРОБЕЛЫ действует так, что формула, описанная в шаге 4, также применяет условное форматирование ко всем числовым ячейкам. Если в вашем диапазоне встречаются числа, используйте на шаге 4 формулу: =ЕСЛИ(НЕ(ЕНЕТЕКСТ(D2));D2<>СЖПРОБЕЛЫ(D2)). Кроме того, нужно иметь в виду, что функция СЖПРОБЕЛЫ не удаляет (и не воспринимает при условном форматировании) знак неразрывного пробела.

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. — СПб.: Питер, 2014. — С. 183–185.

Чтобы определить положение последнего пробела в строке надо с помощью формулы массива (для старых версий, начиная с Excel 2007 , вводится через CTRL+SHIFT+ENTER, новые Excel, с 2019 этого не требуют) составить формулу содержащую индекс каждого пробела в строке или ноль для других символов, а затем выбрать элемент с наибольшим значением.

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

=НАИБОЛЬШИЙ(массив; номер элемента)

Для самого большого значения (последний пробел) номер элемента равен 1

Для наибольшего значения меньше самого большого (предпоследний пробел) номер элемента равен 2 и так далее.

Чтобы последовательно сделать перебор всех символов строки воспользуемся массивом строк и функцией

=СТРОКА(ссылка на массив строк)

Массив строк можно задать статически для строк, имеющих одинаковую длину (элементы с индексами от 1 до 255):

=СТРОКА($1:$255)

или динамически (если строка в ячейке A1, элементы с индексами от 1 до последнего):

=СТРОКА( ДВССЫЛ( «1:»&ДЛСТР( A1 ))

Получаем такие формулы определяющие индекс последнего пробела строки

Статическая (рассматривает 255 элементов массива)

=НАИБОЛЬШИЙ( ЕСЛИ( ПСТР(A1; СТРОКА( $1:$255); 1)=» «; СТРОКА( $1:$255); 0); 1)

Динамическая (рассматривает с первого до последнего символа строки)

=НАИБОЛЬШИЙ( ЕСЛИ( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(A1))); 1)=» «; СТРОКА(

ДВССЫЛ( «1:»&ДЛСТР(A1))); 0); 1)

Пример использования

Индекс предпоследнего пробела строки вычисляется формулами:

Статическая (рассматривает 255 элементов массива)

=НАИБОЛЬШИЙ( ЕСЛИ( ПСТР(A1; СТРОКА( $1:$255); 1)=» «; СТРОКА( $1:$255); 0); 2)

Динамическая (рассматривает с первого до последнего символа строки)

=НАИБОЛЬШИЙ( ЕСЛИ( ПСТР(A1; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(A1))); 1)=» «; СТРОКА( ДВССЫЛ( «1:»&ДЛСТР(A1))); 0); 2)

При копировании формул в свой проект адрес ячейки исходной строки (A1) надо заменить на адрес ячейки своего проекта.