Лучшая альтернатива функции СЦЕПИТЬ и объединить текст в Excel

Как расцепить текст в Excel. Расцепление текста с помощью применения специального инструмента и формулы. Подробное описание каждого метода с фото.

Как сцепить два текста в один с помощью и без функции Excel?

В примере показанным на рисунке таблица содержит первые два столбца:

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

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

объединить имя и фамилию.

Для этого необходимо попарно объедини тексты из ячеек столбцов таблицы в общие строки.

На рисунке проиллюстрированный пример решения данной задачи. Для этой цели в примере должен использоваться оператор «&» (END вместо оператора «+»), который позволяет объединять значения ячеек в формулах:

пример решения задачи.

Как показано выше на рисунке с помощью символа «&» можно соединить любые текстовые значения из разных ячеек без использования функции: =СЦЕПИТЬ(). В этом примере объединены значения ячеек A2 и B2 с добавленным между ними символом пробела (указанный в формуле между кавычками как текстовое значение).

соединить любые текстовые значения.

Для объединения текстов в одну строку программа Excel предлагает встроенную функцию
=СЦЕПИТЬ(A2;” “;B2), которая объединяет текстовые значения в одну строку. Однако на практике мы не рекомендуем использовать функцию СЦЕПИТЬ, а в место нее просто применять в формулах оператор &, так как она имеет ряд своих недостатков.

3 главных недостатка функции СЦЕПИТЬ по сравнению с оператором &:

  1. Ограниченное максимальное количество объединяемых значений ячеек до 255.
  2. Функция объединяет только текстовые значения, а оператор & объединит любые значения ячеек.
  3. Функция требует немного больше затрат времени и ресурсов как у программы Excel (в процессе вычислений с использованием сложных формул), так и у пользователя (в процессе ввода функции в строку формул).

Интересный факт! В Excel нет обратной функции РАСЦЕПИТЬ и нет специального символа оператора. Для решения такой задачи применяются формулы с комбинациями текстовых функций. Например маркером для расцепления текста послужит символ пробела « » , а дальше:

  • чтобы расцепить только имя используем формулу: =ЛЕВСИМВ(C2;ПОИСК(” “;C2;1))
  • чтобы расцепить только фамилию – формула: =ПРАВСИМВ(C4;ПОИСК(” “;C4;1)+1)

функция РАСЦЕПИТЬ.

Фактически функция СЦЕПИТЬ устарела, но все еще доступна пользователям в новых версиях программы Excel.

Первый метод: применение автоматического инструмента

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

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

1

  1. Передвигаемся в подраздел «Данные» и жмем ЛКМ по элементу «Текст по столбцам».

kak-rascepit-tekst-v-excel2

  1. Отобразилось окошко, имеющее наименование «Мастер распределения текста по столбцам». Ставим пунктик рядом с надписью «с разделителями». Обычно в качестве разделителя выступает пробел, но на дальнейшем этапе можно отредактировать вид разделения.

kak-rascepit-tekst-v-excel3

  1. Выставляем отметку около того символа, который мы планируем использовать в качестве разделителя. Внизу окошка находится поле «Образец разбора данных». Здесь можно предварительно посмотреть полученный результат.

kak-rascepit-tekst-v-excel4

  1. На следующем этапе задаем новый формат колонок, а также ту локацию, в которую будет перемещена новая информация. После проведения всех процедур щелкаем левой клавишей мышки на «Готово», чтобы сохранить внесенные изменения.

kak-rascepit-tekst-v-excel5

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

kak-rascepit-tekst-v-excel6

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

Видео: Разделить текст по столбцам в Excel / Text to Columns (Урок 6) [Eugene Avdukhov, Excel Для Всех]

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

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

Особенности разъединения ячеек

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

Распределение текста с разделителями на 3 столбца.

Предположим, у вас есть список одежды вида Наименование-Цвет-Размер, и вы хотите разделить его на 3 отдельных части. Здесь разделитель слов – дефис. С ним и будем работать.

  1. Чтобы извлечь Наименование товара (все символы до 1-го дефиса), вставьте следующее выражение в B2, а затем скопируйте его вниз по столбцу:

=ЛЕВСИМВ(A2; ПОИСК(“-“;A2;1)-1)

Здесь функция мы сначала определяем позицию первого дефиса (“-“) в строке, а ЛЕВСИМВ извлекает все нужные символы начиная с этой позиции. Вы вычитаете 1 из позиции дефиса, потому что вы не хотите извлекать сам дефис.

  1. Чтобы извлечь цвет (это все буквы между 1-м и 2-м дефисами), запишите в C2, а затем скопируйте ниже:

=ПСТР(A2; ПОИСК(“-“;A2) + 1; ПОИСК(“-“;A2;ПОИСК(“-“;A2)+1) – ПОИСК(“-“;A2) – 1)

Логику работы ПСТР мы рассмотрели чуть выше.

  1. Чтобы извлечь размер (все символы после 3-го дефиса), введите следующее выражение в D2:

=ПРАВСИМВ(A2;ДЛСТР(A2) – ПОИСК(“-“; A2; ПОИСК(“-“; A2) + 1))

Аналогичным образом вы можете в Excel разделить содержимое ячейки в разные ячейки любым другим разделителем. Все, что вам нужно сделать, это заменить «-» на требуемый символ, например пробел (« »), косую черту («/»), двоеточие («:»), точку с запятой («;») и т. д.

Примечание. В приведенных выше формулах +1 и -1 соответствуют количеству знаков в разделителе. В нашем примере это дефис (то есть, 1 знак). Если ваш разделитель состоит из двух знаков, например, запятой и пробела, тогда укажите только запятую (“,”) в ваших выражениях и используйте +2 и -2 вместо +1 и -1.

Регулярные выражения

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

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

В Гугл таблицах

Формула Гугл таблиц, которая разделит текст и цифры, довольно незамысловата:

=TRIM(REGEXREPLACE(A1,”(d+|D+)”,”$1 “))

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

В Excel с надстройкой !SEMTools

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

Разделить текст и числа через пробел с помощью регулярных выражений в Excel с !SEMTools

Распространенные неполадки

Кавычки отображаются в строке результата.

Используйте точку с запятой для разделения текстовых элементов. Например: Excel отобразит результат функции =СЦЕПИТЬ (“Привет, “”мир!”) как “Привет,”мир!” с лишней кавычкой, так как пропущена точка с запятой между текстовыми аргументами.

Числа не нужно заключать в кавычки.

Слова расположены впритык друг к другу.

Если не поместить пробелы между текстовыми элементами, в строке результата они будут отображаться слитно. Добавьте пробелы в формулу с функцией СЦЕПИТЬ. Это можно сделать двумя способами.

Добавьте двойные кавычки с пробелом между ними (” “). Например: =СЦЕПИТЬ(“Привет,”; ” “; “мир!”).

Добавьте пробел после текстового аргумента. Например: =СЦЕПИТЬ(“Привет, “; “мир!”). К строке “Привет,” добавлен пробел.

Ошибка #ИМЯ? отображается вместо ожидаемого результата.

Ошибка #ИМЯ? обычно означает, что для текстового аргумента не хватает кавычек.

Второй метод: применение формулы расцепления текста

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

Этап первый: расцепление 1-го слова

Подробная инструкция выглядит так:

  1. Создаем 3 новых колонки с заголовками.

kak-rascepit-tekst-v-excel7

  1. Производим выбор той ячейки, в которой мы планируем расположить 1-е слово. Вписываем сюда формулу: =ЛЕВСИМВ(.

kak-rascepit-tekst-v-excel8

  1. Щелкаем левой клавишей мышки на «Аргументы функции».

kak-rascepit-tekst-v-excel9

  1. В поле «Текст» при помощи левой клавиши мышки указываем координаты ячейки. В нашем случае это А1.

kak-rascepit-tekst-v-excel10

  1. В поле «Количество_знаков» вбиваем формулу ПОИСК(), позволяющую автоматически подсчитать число символов до разделителя.

kak-rascepit-tekst-v-excel11

  1. Формула отобразилась в строчке для ввода формул. Жмем на нее ЛКМ, чтобы попасть в окошко «Аргументы функции».

kak-rascepit-tekst-v-excel12

  1. В строчку «Искомый_текст» вбиваем необходимый разделитель. Стандартно – это пробел. В строчку «Текст_для_поиска» вбиваем координату А1.

kak-rascepit-tekst-v-excel13

  1. Жмем ЛКМ на 1-ю функцию, чтобы снова попасть в окошко ее аргументов. Дописываем правее 2-го аргумента значение -1. Это позволит формуле учесть не сам пробел, а знак до него.

kak-rascepit-tekst-v-excel14

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

kak-rascepit-tekst-v-excel15

  1. Растягиваем новую ячейку вниз при помощи зажатия ЛКМ. Формула автоматическим образом подставилась в другие ячейки.

kak-rascepit-tekst-v-excel16

Этап второй: расцепление 2-го слова

Расцепление 2-го слова – это самый сложный этап, так как оно с 2-х сторон окружено разделителями. Подробная инструкция выглядит так:

  1. Для работы нам понадобится формула =ПСТР(. Записываем ее в ячейку, а затем передвигаемся в окошко для ввода аргументов.

kak-rascepit-tekst-v-excel17

  1. Эта формула позволяет отыскать необходимую строчку в текстовой информации. В строчку «Текст» вводим координату А1.

kak-rascepit-tekst-v-excel18

  1. В поле «Начальная_позиция» вбиваем формулу ПОИСК(), позволяющую автоматически подсчитать число символов до разделителя.

kak-rascepit-tekst-v-excel19

  1. Формула отобразилась в строчке для ввода формул. Жмем на нее ЛКМ, чтобы попасть в окошко «Аргументы функции».

kak-rascepit-tekst-v-excel20

  1. В строчку «Искомый_текст» вбиваем необходимый разделитель. Стандартно – это пробел. В строчку «Текст_для_поиска» вбиваем координату А1.

kak-rascepit-tekst-v-excel21

  1. Вернувшись в изначальное окошко, добавляем во второй аргумент значение +1 в конец. Это позволит осуществить начало счета со следующего знака после найденного разделителя.

kak-rascepit-tekst-v-excel22

  1. В строчку «Количество_знаков» снова вводим формулу ПОИСК().

kak-rascepit-tekst-v-excel23

  1. Аргументы оператора заполняем таким же образом, как и выше.

kak-rascepit-tekst-v-excel24

  1. Дополнительно здесь же в поле «Нач_позиция» необходимо еще раз прописать ПОИСК(), чтобы отыскать 2-й разделитель.

kak-rascepit-tekst-v-excel25

  1. Окошко аргументов для оператора ПОИСК() заполняем всем тем же образом, как и ранее.

kak-rascepit-tekst-v-excel26

  1. Передвигаемся к первому ПОИСКУ и добавляем значение +1 в аргумент «Нач_позиция».

kak-rascepit-tekst-v-excel27

  1. Жмем ЛКМ на элемент =ПСТР, находящийся в строчке формул. Наводим указатель в конец строчки «Количество_знаков».
  2. Добавляем в конце значение -ПОИСК(» «;A1)-1), чтобы закончить расчет разделителей.

kak-rascepit-tekst-v-excel28

  1. Закрываем окошко аргументов, чтобы убедиться в правильном выводе информации.
  2. Растягиваем новую ячейку вниз при помощи зажатия ЛКМ. Формула автоматическим образом подставилась в другие ячейки.

kak-rascepit-tekst-v-excel29

Третий этап: расцепление 3-го слова

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

  1. Вбиваем в необходимую ячейку формулу =ПРАВСИМВ( и перемещаемся в окошко аргументов.

kak-rascepit-tekst-v-excel30

  1. В строчку «Текст» вбиваем координату А1.

kak-rascepit-tekst-v-excel31

  1. В строчку «Число_знаков» вбиваем формулу ДЛСТР(А1). Она позволяет выявить число символов в данных.

kak-rascepit-tekst-v-excel32

  1. Добавляем в конец этой строчки знакомое нам значение -ПОИСК(). Передвигаемся в окошко аргументов данной формулы.

kak-rascepit-tekst-v-excel33

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

kak-rascepit-tekst-v-excel34

  1. В строчку «Нач_позиция» вбиваем дополнительный ПОИСК().

kak-rascepit-tekst-v-excel35

  1. Указываем здесь такие же данные.

kak-rascepit-tekst-v-excel36

  1. Возвращаемся к прошлому «ПОИСКУ».

kak-rascepit-tekst-v-excel37

  1. В конец строчки «Нач_позиция» добавляем значение +1.

kak-rascepit-tekst-v-excel38

  1. Подтверждаем все внесенные изменения.

kak-rascepit-tekst-v-excel39

  1. Готово! Мы реализовали процедуру расцепления 3-х слов. Каждое слово находится в отдельной колонке.

kak-rascepit-tekst-v-excel40

См. также

Разделение текста по столбцам с помощью функций

Нужна дополнительная помощь?

Выдергиваем слова с помощью макросов VBA

     Рассмотрим два способа разделить текст в ячейке:

  1. Выдергиваем отдельные слова по разделителю;
  2. Делим текст без пробелов.

     Способ №1.

     Поскольку вас интересует автоматическое деление текста, значит надо написать хорошую функцию на VBA и внедрить ее в рабочую книгу. Для начала переходим на вкладку «Разработчик» и выбираем «Visual Basic» или вызываем эту возможность с помощью горячего сочетания клавиш Alt+F11. (детальнее в статье «Как создать макрос в Excel»).

     Создаем новый модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

FunctionSubstring(Txt,Delimiter,n)AsString

DimxAsVariant

    x=Split(Txt,Delimiter)

    Ifn>0Andn1<=UBound(x)Then

        Substring=x(n1)

    Else

        Substring=“”

    EndIf

EndFunction

     Теперь эта функция появилась в «Мастер функций» в разделе «Определенные пользователем». Синтаксис созданной функции очень прост и выглядит так:

            =SUBSTRING (_Txt_;_Delimeter_;_n_), где:

  • – указываем ячейку текст в которой будем делить;
  • – это символ который служит разделителем (запятая, тире, пробел и т.д);
  • – ставим номер по порядку фрагмента, которой будем извлекать.

Slitii text 6 Как разделить текст в ячейке Excel?     Пример:

     Рассмотрим, как возможно изъять текст по разнообразным символам-разделителям. Для удобства и лучшей автоматизации не забывайтесь значения формулы закреплять абсолютными ссылками. Slitii text 7 Как разделить текст в ячейке Excel?     Способ №2.

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

     В этом поможет небольшая функция, которая автоматически сможет вставлять «пробел» перед всеми заглавными буквами в тексте. Используя Visual Basic, создайте новый модуль, как описано в первом способе и добавьте в него код:

FunctionCutWords(Txt AsRange)AsString

    Dim Out$

    IfLen(Txt)=0ThenExit Function

    Out=Mid(Txt,1,1)

         Fori=2ToLen(Txt)

        IfMid(Txt,i,1)Like“[a-zа-я]”AndMid(Txt,i+1,1)Like“[A-ZА-Я]”Then

            Out=Out&Mid(Txt,i,1)&” “

        Else

            Out=Out&Mid(Txt,i,1)

        EndIf

    Nexti

    CutWords=Out

EndFunction

     Как и предыдущая функция, эту также ищем в разделе «Определенные пользователем» и указываем в ее одном аргументе тот текст, который разлепим. Slitii text 8 Как разделить текст в ячейке Excel?     Пример:

     Используя микрофункцию на склеенный текст, теперь мы легко его разделим. Slitii text 9 Как разделить текст в ячейке Excel?     Я велосипед не придумывал, а предоставил рабочие макросы от Николая Павлова.

     А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

     Не забудьте поблагодарить автора!

Разбить текст и цифры с !SEMTools в 2 клика

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

Все, что вам нужно сделать, это:

  1. просто выделить диапазон или ячейку,
  2. вызвать процедуру,
  3. указать разделитель
  4. и нажать ОК.

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

Хотите так же быстро выполнять данную процедуру? Попробуйте тестовую версию надстройки !SEMTools:

скачать !SEMTools

Как разбить текст по ячейкам по маске (шаблону).

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

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

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

Решением является разбиение строки по следующей маске: * ERROR: * Exception: *

Здесь звездочка (*) представляет любое количество символов.

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

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

Итак, в начале ищем позицию первого разделителя.

=ПОИСК(“ERROR:”;A2;1)

Затем аналогичным образом находим позицию, в которой начинается второй разделитель:

=ПОИСК(“Exception:”;A2;1)

Итак, для ячейки A2 шаблон выглядит следующим образом:

С 1 по 20 символ – дата и время. С 21 по 26 символ – разделитель “ERROR:”. Далее – код ошибки. С 31 по 40 символ – второй разделитель “Exception:”. Затем следует описание ошибки.

Таким образом, в первый столбец мы поместим первые 20 знаков:

=–ЛЕВСИМВ(A2;ПОИСК(“ERROR:”;A2;1)-1)

Обратите внимание, что мы взяли на 1 позицию меньше, чем начало первого разделителя. Кроме того, чтобы сразу конвертировать всё это в дату, ставим перед формулой два знака минус. Это автоматически преобразует цифры в число, а дата как раз и хранится в виде числа. Остается только установить нужный формат даты и времени стандартными средствами Excel.

Далее нужно получить код:

=ПСТР(A2;ПОИСК(“ERROR:”;A2;1)+6;ПОИСК(“Exception:”;A2;1)-(ПОИСК(“ERROR:”;A2;1)+6))

Думаю, вы понимаете, что 6 – это количество знаков в нашем слове-разделителе “ERROR:”.

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

=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(“Exception:”;A2;1)+10))

Аналогично добавляем 10 к найденной позиции второго разделителя “Exception:”, чтобы выйти на координаты первого символа сразу после разделителя. Ведь функция говорит нам только то, где разделитель начинается, а не заканчивается.

Таким образом, ячейку мы распределили по 3 столбцам, исключив при этом слова-разделители.

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

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...