Как разделить текст в Excel (эксель): ТЕКСТРАЗД и формулы

=ТЕКСТДО(A2;" ") возвращает всё до первого пробела, поэтому из Ana Silva получится Ana, а =ТЕКСТПОСЛЕ(A2;" ") возвращает всё после него, Silva. Это функции ТЕКСТДО и ТЕКСТПОСЛЕ (по-английски TEXTBEFORE и TEXTAFTER). Протяните обе формулы вниз, и каждое полное имя разделится на два столбца. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой, как выше.

ФормулыСбросить

B2fx

=TEXTBEFORE(A2," ")

	A	B	C
1	Full name	First	Last
2	Ana Silva	Ana	Silva
3	Ben Okafor	Ben	Okafor
4	Chen Wu	Chen	Wu
5	Dara Kelly	Dara	Kelly
6	Eli Novak	Eli	Novak

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ТЕКСТДО(A2;" ")

Измените имя в столбце A, и оба столбца изменятся следом. ТЕКСТДО, ТЕКСТПОСЛЕ и ТЕКСТРАЗД требуют Microsoft 365 или Excel 2024. Вариант с ЛЕВСИМВ и НАЙТИ для Excel 2021 и старше описан ниже.

ТЕКСТРАЗД: разделить ячейку на столбцы

=ТЕКСТРАЗД(A2;" ") (по-английски TEXTSPLIT) режет текст на каждом пробеле и выводит каждую часть в отдельную ячейку. Одна формула заполняет столько столбцов, сколько получилось частей:

ФормулыСбросить

B2fx

=TEXTSPLIT(A2," ")

	A	B	C	D	E
1	Text
2	Ana Maria Silva	Ana	Maria	Silva
3	Paris, Lyon, Nice	Paris	Lyon	Nice

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ТЕКСТРАЗД(A2;" ")

• Разделитель может быть длиннее одного символа: ", " делит по запятой с пробелом, поэтому у частей нет пробела в начале.
• Оставьте место для результата. Если ячейка справа не пуста, формула вместо этого покажет #ПЕРЕНОС! (по-английски #SPILL!). Таблицы на этой странице показывают ошибки под английскими именами.
• Части всегда текстовые, даже цифры, поэтому СУММ их пропускает. Поставьте два минуса впереди, =--ТЕКСТРАЗД(A2;","), если нужны числа.

Третий аргумент это разделитель строк. С обоими разделителями ТЕКСТРАЗД строит из одной ячейки маленькую таблицу: запятые разделяют столбцы, а точки с запятой строки.

ФормулыСбросить

A4fx

=TEXTSPLIT(A2,",",";")

	A	B
1	Teams
2	Ana,Red;Ben,Blue;Chen,Green
3
4	Ana	Red
5	Ben	Blue
6	Chen	Green

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ТЕКСТРАЗД(A2;",";";")

Ещё две формы, которые работают в Excel, показаны здесь как текст:

=TEXTSPLIT(A2,,", ")        one piece per row: the column delimiter is left out
=TEXTSPLIT(A2,{";",","})    several delimiters in braces: splits at ; or ,

В русском Excel первая формула записывается как =ТЕКСТРАЗД(A2;;", "), а вторая остаётся в английской форме, потому что разделители внутри фигурных скобок зависят от региональных настроек. Первая превращает excel, formulas, text в столбец из трёх ячеек: так список, набранный в одной ячейке, раскладывается по строкам. Вторая превращает red;green,blue в red, green, blue.

Вторые имена и «Фамилия, Имя»

ТЕКСТПОСЛЕ(A2;" ") возвращает всё после первого пробела, поэтому для Ana Maria Silva вернёт Maria Silva. Чтобы получить последнее слово, считайте с конца, указав номер вхождения -1. Для имён вида Silva, Ana делите по запятой с пробелом:

ФормулыСбросить

C2fx

=TEXTAFTER(A2," ",-1)

	A	B	C
1	Full name	First	Last
2	Ana Maria Silva	Ana	Silva
3	Ben Okafor	Ben	Okafor
4	Chen Li Wu	Chen	Wu
5	Cher	Cher
6
7	Last, First	First	Last
8	Silva, Ana	Ana	Silva

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ТЕКСТПОСЛЕ(A2;" ";-1)

Когда разделителя нет, ТЕКСТДО и ТЕКСТПОСЛЕ возвращают #Н/Д (по-английски #N/A), а в имени из одного слова, например Cher, пробела нет. В строке 5 используется шестой аргумент, значение, которое нужно вернуть вместо ошибки: =ТЕКСТДО(A5;" ";;;;A5) возвращает всё имя, а ТЕКСТПОСЛЕ(...;"") возвращает пустую ячейку.

ФормулыСбросить

C2fx

	A	B	C
1	Email	User	Domain
2	ana.silva@coddy.tech	ana.silva

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

Ваша очередь: В C2 верните домен адреса из A2: всё, что стоит после @.

ПроверитьПоказать ответ

Разделить имена в старых версиях Excel

Без ТЕКСТДО и ТЕКСТПОСЛЕ найдите позицию пробела функцией НАЙТИ и отрежьте текст вокруг неё функциями ЛЕВСИМВ, ПСТР и ПРАВСИМВ:

ФормулыСбросить

B2fx

=LEFT(A2,FIND(" ",A2)-1)

	A	B	C
1	Full name	First	Last
2	Ana Silva	Ana	Silva
3	Ben Okafor	Ben	Okafor
4	Chen Wu	Chen	Wu

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1)

• НАЙТИ(" ";A2) это позиция пробела: 4 в Ana Silva.
• ЛЕВСИМВ(A2;4-1) берёт 3 символа перед ним.
• ПСТР(A2;4+1;100) берёт до 100 символов начиная после пробела. 100 означает просто «больше любого имени»; =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2)) даёт тот же результат с точной длиной.

Эти формулы возвращают #ЗНАЧ! (по-английски #VALUE!), когда пробела нет. Оберните их в ЕСЛИОШИБКА, =ЕСЛИОШИБКА(ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1);A2), чтобы вместо ошибки вернуть всю ячейку.

ФормулыСбросить

B2fx

	A	B
1	Code	Prefix
2	KT-1043-BLUE

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

Ваша очередь: Верните часть кода товара до первого дефиса в B2 с помощью ЛЕВСИМВ и НАЙТИ.

ПроверитьПоказать ответ

Текст по столбцам и мгновенное заполнение

Чтобы разделить столбец один раз, без формул:

1. Выделите столбец с полными именами. Убедитесь, что столбцы справа пусты, потому что «Текст по столбцам» их перезапишет.
2. Данные > Текст по столбцам.
3. Выберите с разделителями, нажмите «Далее», отметьте пробел (или запятая, или введите другой символ в поле другой), нажмите «Далее».
4. Первая часть заменит полные имена. Чтобы их сохранить, укажите в поле Поместить в пустую ячейку справа от них и нажмите «Готово».

Мгновенное заполнение быстрее, когда есть образец: введите Ana рядом с Ana Silva, нажмите Enter, затем Ctrl+E или Данные > Мгновенное заполнение, и Excel заполнит имена в остальных строках. Оба инструмента записывают постоянные значения. Если имена потом изменятся, разделение не обновится; формула обновится.