Первые слова ячеек Excel

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

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

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

Первое слово ячейки Excel с большой буквы

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

Поэтому я рассмотрел эту задачу в отдельной статье “Как сделать первую букву ячейки заглавной“.

Вывести первое слово в отдельную ячейку — формула

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

=ЛЕВСИМВ(A1;ПОИСК(" ";A1&" ")-1)

Здесь A1 – ячейка с искомым словом.

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

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

Оставить только первое слово в ячейке

Простейший вариант сделать подобное в Excel – штатной процедурой “Найти и заменить“. Можно вызвать процедуру горячим сочетанием клавиш Ctrl + H, в первом окошке ввести пробел со звёздочкой (см. подстановочные символы в Excel), а второе оставить пустым как есть.

Есть и вариант с использованием !SEMTools, процедура находится в подразделе ИЗВЛЕЧЬ – Извлечь Слова – по порядку:

Взять первые 2/3/N слов ячейки

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

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

=ЛЕВСИМВ(A1;ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(A1&" ";" ";ЮНИСИМВ(23456);2))-1)

Формула использует особенные свойства функций ПОДСТАВИТЬ и ЮНИСИМВ:

• функция ПОДСТАВИТЬ позволяет заменить N-ую подстроку в ячейке на заданное значение
• а ЮНИСИМВ позволяет задать это значение настолько уникальным, насколько возможно, чтобы быть уверенным, что оно будет единственным в ячейке

Далее функция ПОИСК находит позицию этого символа, чтобы функция ЛЕВСИМВ взяла все что до него (из позиции вычитается единица).

Неплохая надёжная формула со своими преимуществами и недостатками.

Преимущество в том, что легко поддаётся модификации под задачу с извлечением 3,4 и далее слов, просто нужно заменить в формуле аргумент функции подставить, который 2, на соответствующее число.

Недостаток – ЮНИСИМВ работает только в Excel 2013 и старше. Вот аналог для более ранних версий, использующий функцию СИМВОЛ:

=ЛЕВСИМВ(A1;ПОИСК(СИМВОЛ(9);ПОДСТАВИТЬ(A1&" ";" ";СИМВОЛ(9);2))-1)

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

Процедуры !SEMTools для извлечения первых N слов

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

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