Sheets AcademySheets Academy
Премиум
Чистка данных и ВПР

Часть текста

Для письма клиенту нужна фамилия, для склада артикул, для итога количество. Но выгрузка часто кладёт всё это в ячейки вперемешку со словами. Вот как это выглядит:

fx
A
B
C
D
1
2
3
4
5
6
7
Клиент
Товар
Количество
Иванов Пётр Сергеевич
арт. 10234 Чайник
2 шт.
ПЕТРОВА Анна Игоревна
арт. 20871 Утюг
1 шт.
Ким Олег Борисович
арт. 30015 Тостер
12 шт.
Сидоренко Мария Павловна
арт. 41007 Миксер
3 шт.
Лист1

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

Кусок по номерам символов

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

  • ЛЕВСИМВ(текст;сколько) берёт символы с начала;
  • ПРАВСИМВ(текст;сколько) берёт символы с конца;
  • ПСТР(текст;откуда;сколько) берёт символы из середины, начиная с номера «откуда».
Текст в B2:а1р2т3.4␣516072839410␣11Ч12а13й14н15и16к17
=ЛЕВСИМВ(B2;4)арт.
Четыре символа с начала текста. Знак ␣ показывает пробел: он тоже символ и тоже получает номер.

Артикул в этой выгрузке всегда стоит на одном месте: пять цифр с шестого символа. Поэтому для него хватит =ПСТР(B2;6;5).

До пробела: НАЙТИ

С фамилией так не выйдет: в «Иванов» шесть букв, а в «Сидоренко» девять. Зато фамилия всегда кончается перед первым пробелом. Номер этого пробела находит функция НАЙТИ: первым аргументом пишут, что искать, вторым ячейку. Пробел берут в кавычки, как любой текст в формуле. Для «Иванов Пётр Сергеевич» формула =НАЙТИ(" ";A2) вернёт 7: шесть букв фамилии, и седьмым идёт пробел.

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

Впишите в B2 формулу =НАЙТИ(" ";A2) и протяните её до B5
fx
A
B
C
D
1
2
3
4
5
6
7
Клиент
Номер пробела
Фамилия
Иванов Пётр Сергеевич
ПЕТРОВА Анна Игоревна
Ким Олег Борисович
Сидоренко Мария Павловна
Лист1
Символы в A2:
И1в2а3н4о5в6␣7П8ё9т10р11␣12С13е14р15г16е17е18в19и20ч21
Под листом символы клиента из выделенной строки с номерами. НАЙТИ вернёт номер первого пробела.

Когда формула работает, отдельный столбец можно убрать и подставить НАЙТИ прямо на место B2:

=ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1)

Искать можно любой символ, не только пробел. НАЙТИ(",";A2) найдёт запятую в адресе «Казань, ул. Баумана», а НАЙТИ("@";A2) знак @ в адресе почты. Если символа в тексте нет, НАЙТИ вернёт ошибку #ЗНАЧ!. На этом строят проверку: функция ЕЧИСЛО отвечает ИСТИНА, только когда ей досталось число, поэтому ЕЧИСЛО(НАЙТИ("@";A2)) покажет ИСТИНА для адреса со знаком @ и ЛОЖЬ для адреса без него.

У Петровой фамилия пришла из выгрузки большими буквами: «ПЕТРОВА». Привести её к обычному виду помогает функция ПРОПНАЧ. Она делает первую букву каждого слова заглавной, а остальные строчными. Если «ПЕТРОВА» лежит в C3, то =ПРОПНАЧ(C3) вернёт «Петрова».

Число из текста: ЗНАЧЕН

Количество в выгрузке записано с припиской: «12 шт.». Формула до пробела уже вытащила цифры в столбец B, но СУММ в B6 насчитала 0:

fx
A
B
C
D
1
2
3
4
5
6
7
8
Из выгрузки
Цифры
Число
2 шт.
1 шт.
12 шт.
3 шт.
Всего штук
Лист1
Впишите в C2 формулу =ЗНАЧЕН(B2) и протяните её до C5: в C6 появится сумма.

ЛЕВСИМВ, ПРАВСИМВ и ПСТР всегда возвращают текст, даже если в нём одни цифры. Поэтому цифры в столбце B прижаты к левому краю, как любой текст, а СУММ их пропускает. Функция ЗНАЧЕН превращает текст из цифр в настоящее число, и оно встаёт к правому краю.

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

=ЗНАЧЕН(ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1))

Читают её изнутри наружу: НАЙТИ находит пробел, ЛЕВСИМВ берёт цифры до него, а ЗНАЧЕН делает из них число.

Приписку можно и не отрезать, а стереть. Функция ПОДСТАВИТЬ заменяет в тексте одни символы другими, и если заменить « шт.» на пустые кавычки, от «12 шт.» останется «12». Считать, где стоит пробел, тогда не нужно:

=ЗНАЧЕН(ПОДСТАВИТЬ(A2;" шт.";""))