← Все курсы · Курс Excel

Текстовые функции Excel: LEFT, RIGHT, MID, LEN и TEXTJOIN.

Майкл Носито · Обновлено в августе 2026 г. · Все результаты на этой странице были обработаны в Excel перед публикацией.

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

Что вы делаете: MID взять с середины, LEFT и RIGHT с концов, LEN считать, TEXTJOIN чтобы склеить диапазон обратно с помощью разделителя. Затем, как только ваши коды не будут иметь одинаковую длину, замените подсчитанные позиции на FIND.

Краткая версия. Эти функции вырезают и объединяют текст по положению. Позиции, которые вы подсчитали на глаз; позиции, которые вы нашли с помощью FIND не.

Пять функций с результатами

Перед столом: А2 держится US-WES-2024. Сколько это символов, если считать тире?

Одиннадцать. Вот каждая функция в этой ячейке, запущенная в Эксель.

Две детали там ловят людей. LEFT(A2,3) возвращает US- с тире, потому что LEFT считает символы и понятия не имеет, что тире — особенное. И LEN принимает только один аргумент, что и делает =LEN(A2,3) неправильный ответ, а не более медленный.

MID читается как начало и длина: начните с 4, возьмите 3. Все остальное, что касается нарезки текста в Excel, — это эти два числа, либо введенные вами, либо рассчитанные по другой формуле.

День, когда коды станут длиннее

Первое: то же самое =MID(A2,4,3) работает с новым кодом, USA-WEST-2026. Что он возвращает?

-WE. Не ошибка, не пробел. Три символа, начиная с позиции 4, точно так, как указано, и теперь эти три символа представляют собой тире и две буквы. LEFT(A6,3) возвращает USA, что оказывается правильным, и RIGHT(A6,4) возвращает 2026, тоже верно. Ломается только средний, и он превращается в нечто похожее на данные.

Прежде чем читать дальше, скажите, почему это хуже, чем ошибка. Ошибка останавливает отчет. Значение типа -WE перетекает в сводную таблицу и появляется как регион, о котором никто никогда не слышал, месяцы спустя, перед кем-то еще.

Решение состоит в том, чтобы перестать считать и начать искать. FIND возвращает позицию символа, поэтому формула может сама определить границы.

=MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1) - FIND("-",A2) - 1)

Прочтите формулу в трех частях, и она перестанет пугать. FIND("-",A2) равно 3, поэтому начнем с 4. FIND("-",A2,FIND("-",A2)+1) — это второе тире в цифре 7. Семь минус три минус один — это три символа. В более длинном коде те же три части возвращают 4, 9 и 4, а вместо этого требуется четыре символа.

В Майкрософт 365 есть версия, которую можно прочитать сразу:

=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")

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

LEN и два пространства, которые никто не видит

Перед примером: поиск по столбцу имени продолжает возвращаться. #N/A хотя имя явно присутствует на обоих листах. Что нужно проверить в первую очередь?

Содержит ли клетка то, что кажется. Чтение ячейки Alvarez Вернулся LEN из 9, и LEN(TRIM(A7)) Вернулся 7. В нем есть два конечных пробела, и ничто на экране никогда не покажет вам этого.

=LEN(A7)-LEN(TRIM(A7))     ' how many extra spaces are in this cell

Пропустите это по столбцу, прежде чем отлаживать одиночный поиск. Любое число выше нуля означает пробел, а пробел означает, что точное совпадение не удастся. Полная процедура очистки, включая неразрывное пространство, которое TRIM невозможно удалить, находится в очистка запутанных данных.

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

Соединение: TEXTJOIN, CONCAT и амперсанд.

Первое: вам нужно, чтобы все коды формата А2:А5 были в одной ячейке, разделенные запятыми, с пропуском пробелов. Какая функция создана для этого?

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

Аргументами являются разделитель, затем ИСТИНА или ЛОЖЬ для игнорирования пустых ячеек, а затем диапазон. TRUE — это то, что вам нужно почти всегда, потому что оно устраняет пробел в данных, образующий две запятые подряд.

CONCAT принимает диапазон, но без разделителя, поэтому все обрабатывается вместе. Амперсанд, как в =A2&", "&A3, отлично работает для двух или трех ячеек и становится нечитаемым на десяти. Использовать TEXTJOIN для диапазона, амперсанд на пару штук, и CONCAT редко.

Представьте себе, когда в последний раз вам требовался список идентификаторов, разделенных запятыми, для вставки в запрос или заявку. Это TEXTJOIN и около пятнадцати секунд.

Пограничные случаи, которые стоит знать

Текстовые функции возвращают текст, даже если он выглядит как число. =ISNUMBER(RIGHT(A2,4)) возвращает ЛОЖЬ. Умножьте на 1 и =ISNUMBER(RIGHT(A2,4)*1) возвращает ИСТИНА. Итак, год вытянулся с RIGHT не будет сортироваться или фильтроваться как число, пока вы его не преобразуете, что является той же проблемой, что и в Специальная вставка.

FIND чувствителен к регистру, SEARCH нет. =SEARCH("wes",A2) вернулся 4. =FIND("wes",A2) Вернулся #VALUE!, поскольку ячейка содержит WES в столицах. SEARCH также принимает подстановочные знаки ? и *. Использовать SEARCH если только случай не является частью того, что вы сопоставляете.

Пропущенный символ — это ошибка, а не ноль. Оба FIND и SEARCH возвращаться #VALUE! когда вещи нет, поэтому любая формула, построенная на них, нуждается в обертке: =IFERROR(your_formula,""). Это описано в IFERROR.

Попросите больше персонажей, чем существует, и вы получите то, что есть. LEFT и RIGHT не жалуйтесь, если из восьмизначной ячейки попросите 50 символов. MID начиная с конца, возвращается пустой текст. Ни то, ни другое не является ошибкой, поэтому никто не сообщит вам, что данные изменились.

Текст в столбцы и мгновенное заполнение выполняют ту же работу без формул. Разделение по разделителю один раз для единоразовой очистки выполняется быстрее на вкладке «Данные». Формулы займут свое место, когда файл снова появится в следующем месяце.

Почему позиционные формулы не работают

Каждая из этих функций работает с позициями, а позиция — это факт об одном конкретном значении, а не о ваших данных.

Когда ты пишешь MID(A2,4,3), вы не говорите «код региона». Вы говорите «символы с четвертого по шестой», и это оказывается код региона в строках, на которые вы смотрели, когда писали его. Формула понятия не имеет, что такое код региона, поэтому она не может заметить, когда длина кода перестает быть трехбуквенной.

FIND изменяет то, к чему привязана формула. Там написано «часть между разделителями», что относится к формату, а не к одной строке. Форматы изменяются реже, чем значения, поэтому FIND версия сохраняется в тот день, когда кто-то добавляет страну с более длинным кодом.

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

Как применить это в своей работе

1. Найдите в книге каждую формулу с введенной в ней позицией, например MID(x,4,3) или LEFT(x,2). Это те, которые ломаются тихо.
2. Для каждого из них спросите, действительно ли каждое значение в столбце имеет такую ​​форму. Отсортируйте столбец по длине с помощью =LEN() и посмотрите на оба конца.
3. Перепишите все, что не проходит проверку длины, с помощью FINDили с TEXTBEFORE и TEXTAFTER если они есть в вашем Эксель.
4. Оберните что-нибудь, используя FIND или SEARCH в IFERROR, поэтому отсутствующий разделитель отображается как пустой, а не как #VALUE! по всему отчету.
5. Прежде чем отлаживать любой неудачный поиск, запустите =LEN(cell)-LEN(TRIM(cell)) с обеих сторон. Это занимает десять секунд и чаще всего является причиной этого.

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

Шпаргалка

Единственная привычка, которую нужно сохранить

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

Какой столбец кода в ваших книгах взят из системы, которой управляет кто-то другой, и что происходит с вашими формулами на неделе, когда в них добавляется более длинный код?