Как вытащить слово в отдельную колонку

Первые слова ячеек 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 слов во всех выделенных ячейках. И либо вставить в соседний столбец, либо оставить в изначальных ячейках. За режим вывода отвечает маленький флажок в надстройке:

Хотите так же быстро извлекать слова по их позиции в Excel?
!SEMTools поможет с этой и решит многие другие задачи за пару кликов!

Как вытащить слово в отдельную колонку

Отделить часть текста (функция ПСТР)

В EXCEL есть очень удобная функция для «вытаскивания» из текста или слова определенного заданного нами количества символов.

Зачастую такие задачи возникают при обработке кодов, артикулов, номеров телефонов и т.д.

В нашем примере мы «вытащим» левую трехзначную часть кода и правую часть, имеющую различную разрядность без использования ПРАВСИМВ и ЛЕВСИМВ и их сочетаний с другими вспомогательными функциями.

Для этого:

1. В ячейке напротив кода введем =ПСТР( и нажмем fx.

2. В аргументах функции укажем ячейку с исходным текстом , первоначальным кодом.
3. Зададим Начальную позицию (номер символа, с которого начнет вытаскивать текст функция).
4. Количество знаков – то самое к-во, которое должно быть «вытащено» из текста или строки. Пробел и символы – также являются знаками.

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

Для этого:

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

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

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

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

Как вытащить слово в отдельную колонку

Проблема в следующем: Есть столбец, в нем значения состоящие из нескольких слов разделенных пробелом. Как вытащить на другие ячейки эти слова отдельно от всей ячейки? Первое слово достаточно просто найти — =ПСТР(A1;1;НАЙТИ(» «;A1)-1), а остальные чет не допираю. Помогите пожалуйста.

Пользователь
Сообщений: 39 Регистрация: 01.01.1970
17.08.2010 11:19:25

Проблема в следующем: Есть столбец, в нем значения состоящие из нескольких слов разделенных пробелом. Как вытащить на другие ячейки эти слова отдельно от всей ячейки? Первое слово достаточно просто найти — =ПСТР(A1;1;НАЙТИ(» «;A1)-1), а остальные чет не допираю. Помогите пожалуйста.

Пользователь
Сообщений: 2764 Регистрация: 22.12.2012
На лицо ужасный, добрый внутри
17.08.2010 11:23:31

Function Substring(Текст, Символ_разделитель, Номер_фрагмента) As String
‘—————————————————————————————
‘ URL : http://www.planetaexcel.ru/tip.php?aid=54
‘ Purpose : Выделяет из текста n-ный субстринг, ориентируясь по символам-разделителям
‘ Notes : Substring(текст; символ_разделитель; номер_фрагмента), где
‘ текст — текст, который делим
‘ символ_разделитель — символ, который надо считать разделителем фрагментов
‘ номер_фрагмента — порядковый номер фрагмента, который нам нужен
‘—————————————————————————————
On Error Resume Next
Substring = Split(Application.Trim(Текст), Символ_разделитель)(Номер_фрагмента — 1)
End Function

С уважением, Алексей (ИМХО: Excel-2003 — THE BEST. )
Пользователь
Сообщений: 23820 Регистрация: 22.12.2012
17.08.2010 11:24:28

Ну или макрос для допиливания 🙂

Sub tt()
For Each cc In [a1:a4]
arr = Split(Application.Trim(cc))
For i = 0 To UBound(arr)
cc.Offset(, i + 1) = arr(i)
Next
Next
End Sub

Пользователь
Сообщений: 2764 Регистрация: 22.12.2012
На лицо ужасный, добрый внутри
17.08.2010 11:25:53
ИМХО функция намного проще. Всего одна строка и никакого цикла.
С уважением, Алексей (ИМХО: Excel-2003 — THE BEST. )
Пользователь
Сообщений: 1125 Регистрация: 23.12.2012
17.08.2010 11:29:58

Вот такая формула. В А1 текст, N — порядковый номер слова, которое надо вытащить.
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(N-1)+1;ДЛСТР(1)))

Пользователь
Сообщений: 23820 Регистрация: 22.12.2012
17.08.2010 11:32:03

Но с другой стороны:
http://www.planetaexcel.ru/tip.php?aid=54
Олег
23.06.2008 А как то же самое сделать с помощью макроса, чтобы не вставлять все время в новый лист те же форлулы.

Пользователь
Сообщений: 1125 Регистрация: 23.12.2012
17.08.2010 11:40:36

Ошибся немного, в конце формулы не ДЛСТР(1), а ДЛСТР(A1)
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(N-1)+1;ДЛСТР(A1)))

Как вытащить слово в отдельную колонку

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

• Извлечь подстроку слева, посередине или справа
  Метод A: формулаМетод B: удобный инструмент
• Извлечь подстроку после или до указанного символа
  Метод A: формулаМетод B: удобный инструмент
• Извлечь подстроку между двумя символами
  Метод A: формулаМетод B: удобный инструмент расширения
• Извлечь адрес электронной почты из строки
• Извлекать числовые или буквенные символы из строки
• Скачать образец файла
  
• Другие операции (статьи), связанные с пространствами
  Извлечь n-й символ из строкиИзвлечь время из datetimeСкоро..

Извлечь подстроку слева, посередине или справа

Метод A: извлечь подстроку слева, посередине или справа с помощью формулы

В Excel есть несколько формул, которые помогут вам быстро извлечь часть текста.

Извлечь первые n символов

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

B3 — это ячейка, из которой вы извлекаете символы, 3 — это количество символов, которые вы хотите извлечь.

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

Извлечь последние n символов

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

= ПРАВЫЙ (B9,6)

B9 — это ячейка, из которой вы извлекаете символы, 6 — это количество символов, которые вы хотите извлечь.

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

Извлечь n символов из середины

Если вы хотите извлечь 3 символа, начинающиеся с 4-го символа строки, вы можете использовать следующую формулу:

B15 — это ячейка, из которой вы извлекаете символы, 4 представляет собой извлекаемые символы из 4-го символа (счетчик слева), 3 — это количество символов, которые вы хотите извлечь.

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

Примечание:

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

Метод B: извлечь подстроку слева, посередине или справа с помощью Kutools for Excel

Если вы не знакомы с формулами, вы можете попробовать Kutools for ExcelАвтора Извлечь текст функция, с которой легко справиться с этой работой.

Перед использованием утилиты извлечения текста, пожалуйста, уделите 3 минуты, чтобы бесплатно установить Kutools for Excel: Скачать сейчас бесплатно!

1. Выберите ячейки, из которых нужно извлечь подстроки, щелкните Кутулс > Текст > Извлечь текст.

2. Во всплывающем Извлечь текст диалог под Извлечь по местоположению tab, первые три параметра помогут вам извлечь подстроку слева, посередине или справа.

Первый символ N: извлечь подстроку слева. Например, извлеките первые 2 символа, установите этот флажок и введите 2 в текстовое поле.

Последний символ N: извлечь подстроку справа от строки. Например, извлеките последние 2 символа, установите этот флажок и введите 2 в текстовое поле.

Начальные и конечные символы: извлечь определенное количество символов из середины строки. Например, извлеките с 4-го символа по 9-й, отметьте эту опцию и введите 4 и 9 в текстовые поля отдельно.

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

3. Указав нужное местоположение, нажмите Ok, появится диалоговое окно для выбора ячейки для размещения извлеченной подстроки. Щелкните ОК.

Теперь подстрока извлечена.

Extract text string before, after or between characters without formula

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

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

Метод A: извлечь подстроку после или до определенного символа с помощью формулы

Предположим, вы хотите извлечь символы после символа « — »Из списка строк, используйте эту формулу:

= ВПРАВО (B3; LEN (B3) -ПОИСК («-«; B3))

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

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

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

= ВЛЕВО (B10; ПОИСК («-«; B10) -1)

Пример результата показан ниже:

Внимание

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

Метод B: извлечь подстроку после или до определенного символа Kutools for Excel

Для прямого извлечения подстроки после или до указанного символа вы можете использовать Извлечь текст полезности Kutools for Excel, который может помочь вам извлечь все символы после или до символа, а также может извлечь определенную длину символов до или после символа.

Перед использованием утилиты извлечения текста, пожалуйста, уделите 3 минуты, чтобы бесплатно установить Kutools for Excel: Скачать сейчас бесплатно!

1. Выберите ячейки, в которые нужно извлечь символы, нажмите Кутулс > Текст > Извлечь текст.

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

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

После текста: извлечь подстроки после введенного символа (ов). Например, введите — в текстовое поле, все символы после — будут извлечены.

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

3. Нажмите Ok, появится диалоговое окно для выбора ячейки для размещения извлеченной подстроки. Нажмите OK.

Теперь строка до или после определенного символа (ов) была извлечена.

13 текстовых инструментов, которые вы должны иметь в Excel, которые повысят вашу эффективность на 90%

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

▲ Кроме инструментов, отображаемых на картинке, в Kutools for Excel есть еще 300 расширенных инструментов, которые могут решить ваши 82% головоломки Excel.

▲ Станьте экспертом по Excel за 5 минут, получите признание и продвижение по службе.

▲ 110000+ высокоэффективных сотрудников и 300+ всемирно известных компаний.

30-дневная бесплатная пробная версия, кредитная карта не требуется Узнать больше Скачать сейчас

Извлечь подстроку между двумя символами

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

Метод А: извлечение по формуле

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

=MID(LEFT(B3,FIND(«)»,B3)-1),FIND(«(«,B3)+1,LEN(B3))

В формуле B3 — это ячейка, из которой вы хотите извлечь строку, ( и ) — это два символа, между которыми вы хотите извлечь строку.

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

Внимание

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

Метод B: извлечение с помощью Kutools for Excel

In Kutools for Excelсотни функций, есть функция — Извлечь строки между указанным текстом может быстро извлекать подстроки между двумя символами.

Перед использованием утилиты извлечения текста, пожалуйста, уделите 3 минуты, чтобы бесплатно установить Kutools for Excel: Скачать сейчас бесплатно!

1. Выберите ячейку, которая использовалась для размещения извлеченной подстроки, щелкните Кутулс > Формула Помощник > Текст > Извлечь строки между указанным текстом.

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

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

3. Нажмите Ok, теперь первый результат получен, затем перетащите дескриптор автозаполнения на ячейки, которым нужна эта формула.

Наконечник:

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

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

2. во всплывающем Извлечь текст диалога под Извлечь по правилу вкладка, перейдите в Текст раздел, введите символы, между которыми вы хотите извлечь строку, и строка может быть заменена подстановочным знаком * . Если вы хотите извлечь строку фиксированной длины, подстановочный знак ? можно использовать, один? указать один символ.

Затем нажмите Добавить добавить правило к Описание правила .

3.Click Ok, появится диалоговое окно для выбора ячейки для размещения извлеченной подстроки. Нажмите OK.

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

Извлечь адрес электронной почты из строки

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

Перед использованием утилиты извлечения адреса электронной почты, пожалуйста, уделите 3 минуты, чтобы бесплатно установить Kutools for Excel: Скачать сейчас бесплатно!

1. Выберите ячейки, в которых будет извлечен адрес электронной почты, затем нажмите Кутулс > Текст > Извлечь адрес электронной почты.

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

3. Нажмите OK, адреса электронной почты в каждой ячейке были извлечены.

Извлекать числовые или буквенные символы из строки

Если есть список данных, смешанных с числовыми, алфавитными и специальными символами, вы просто хотите извлечь числа или буквенные значения, вы можете попробовать Kutools for Excel’s Удалить символы.

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

2. Затем выберите эту копию данных, нажмите Кутулс > Текст > Удалить символы.

3. в Удалить символы диалог, проверьте Нечисловой , нажмите Ok.

Теперь остались только числовые символы.

Чтобы извлечь только алфавитные значения, установите флажок Не альфа вариант в Удалить символы Диалог.

Скачать образец файла

Другие операции (статьи), связанные с преобразованием файлов

Извлечь время из строки даты и времени
Предоставляет трюки для извлечения времени (чч: мм: сс) или часов / минут / секунд только из строки даты и времени (мм / дд / гггг чч: мм: сс)

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

Извлечь n-й символ из строки
Здесь будут представлены методы извлечения n-го символа из строки, например, извлечение 3-го символа из строки a1b2c3, результат — b.

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

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

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

• Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
• Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение . Предотвращение дублирования ячеек; Сравнить диапазоны .
• Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
• Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
• Избранные и быстро вставляйте формулы , Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
• Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
• Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
• Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
• Группировка сводной таблицы по номер недели, день недели и другое . Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя .

Больше информации. Полнофункциональная 30-дневная бесплатная пробная версия . Покупка .

Вкладка Office — предоставляет интерфейс с вкладками в Office и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!