Извлеките первое слово с помощью текстовых формул
Предположим, у вас есть следующий набор данных, в котором вы хотите получить первое слово из каждой ячейки.
Приведенная ниже формула сделает это:
= ЕСЛИОШИБКА (ЛЕВО (A2; НАЙТИ (""; A2) -1); A2)
Позвольте мне объяснить, как работает эта формула.
Часть формулы FIND используется для поиска позиции символа пробела в текстовой строке. Когда формула находит позицию символа пробела, функция LEFT используется для извлечения всех символов перед этим первым символом пробела в текстовой строке.
Хотя одной формулы LEFT должно быть достаточно, она выдаст ошибку, если в ячейке есть только одно слово и нет пробелов.
Чтобы справиться с этой ситуацией, я заключил формулу LEFT в формулу IFERROR, которая просто возвращает исходное содержимое ячейки (поскольку нет пробелов, указывающих, что она пуста или содержит только одно слово).
В этом методе хорошо то, что результат получается динамичным. Это означает, что если вы измените исходную текстовую строку в ячейках в столбце A, формула в столбце B автоматически обновится и даст правильный результат.
Если вам не нужна формула, вы можете преобразовать ее в значения.
Извлечение чисел из строки в Excel (Формула для Excel 2016)
Эта формула будет работать только в Excel 2016, поскольку в ней используется недавно представленная функция TEXTJOIN.
Кроме того, эта формула может извлекать числа в начале, конце или середине текстовой строки.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите извлечь числа из строк в каждой ячейке:
Ниже приведена формула, которая даст вам числовую часть строки в Excel.
= TEXTJOIN (""; ИСТИНА; ЕСЛИОШИБКА ((СРЕДНЕЕ (A2; СТРОКА (КОСВЕННАЯ ("1:" & LEN (A2))); 1) * 1); ""))
Это формула массива, поэтому вам нужно использовать ‘Control + Shift + Enter‘Вместо использования Enter.
Если в текстовой строке нет чисел, эта формула вернет пустую строку (пустую строку).
Как работает эта формула?
Позвольте мне сломать эту формулу и попытаться объяснить, как она работает:
- ROW (INDIRECT («1:» & LEN (A2))) — эта часть формулы даст серию чисел, начиная с единицы. Функция LEN в формуле возвращает общее количество символов в строке. В случае «Стоимость 100 долларов США» будет возвращено 19. Формулы, таким образом, станут СТРОКА (КОСВЕННАЯ («1:19»). Затем функция СТРОКА вернет серию чисел — {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
- (MID (A2, ROW (INDIRECT («1:» & LEN (A2))), 1) * 1) — эта часть формулы вернет массив #VALUE! ошибки или числа на основе строки. Все текстовые символы в строке становятся #VALUE! ошибки и все числовые значения остаются как есть. Это происходит, когда мы умножили функцию MID на 1.
- ЕСЛИОШИБКА ((MID (A2, ROW (INDIRECT («1:» & LEN (A2))), 1) * 1), »») — при использовании функции ЕСЛИОШИБКА все символы #VALUE! Будут удалены. ошибки, и останутся только цифры. Вывод этой части будет выглядеть так — {«»; »»; «»; »»; »»; »;» »;»; »»; »»; »;» »;» »; ””; ””; ””; 1; 0; 0}
- = TEXTJOIN («», TRUE, IFERROR ((MID (A2, ROW (INDIRECT («1:» & LEN (A2))), 1) * 1), »»)) — функция TEXTJOIN теперь просто объединяет строковые символы это остается (это только числа) и игнорирует пустую строку.
Совет профессионала:
Загрузите файл примера
Вы также можете использовать ту же логику для извлечения текстовой части из буквенно-цифровой строки. Ниже приведена формула, которая получит текстовую часть из строки:
= TEXTJOIN (""; TRUE; IF (ISERROR (MID (A2; ROW (INDIRECT ("1:" & LEN (A2)))); 1) * 1), MID (A2; ROW (INDIRECT ("1:" & LEN (А2))), 1), ""))
Незначительное изменение в этой формуле заключается в том, что функция IF используется для проверки того, является ли массив, который мы получаем из функции MID, ошибками или нет. Если это ошибка, он сохраняет значение, иначе заменяет его пустым.
Затем TEXTJOIN используется для объединения всех текстовых символов.
Осторожность
Извлекаем в MS EXCEL число из конца текстовой строки
ячейку можно с все числа вПервый символ второй строки 1.
в Microsoft Excel. выщемить текст между заменить. На закладке но если, например, «т» (логическое ИСТИНАОписание аргументов: для округления дробных с помощью функции «объем», определяя позицию
восьмого символа, находит знаки: вопросительный знакЭти функции могут бытьФункции помощью формулы =—Т(C7&D7&E7&F7&G7&H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7)
строке нужно вывестиШЧисло_байтовФункция ЛЕВСИМВ возвращает первый SOURCE и Translation,
заменить в поле
коду товара предшествует эквивалентно числовому значениютекст – ссылка на чисел до ближайших ЛЕВСИМВ получилось вытащить слова «маржа» в знак, указанный в
Несколько чисел в текстовой строке (через WORD)
( доступны не наПОИСК или =СЦЕПИТЬ(C7;D7;E7;F7;G7;H7;I7;J7;K7;L7;M7;N7;O7;P7;Q7)+0 одним числом, т.е.Извлечем число из конца Необязательный. Количество символов, извлекаемых
- символ или несколько чтобы он был Найти вводите ПРАВСИМВ,
- (или находится в 1); ячейку, содержащую текст, целых значений с
- левую часть текста ячейке A3 и аргументе? всех языках.
- ИЕсли в текстовой строке текстовая строка 123Филато11в6а будет текстовой строки, например, функцией ЛЕВБ. первых символов текстовой как то по в поле заменить конкретно месте этогопри аргументах: («текст»;ЛОЖЬ) вернет или текстовая строка,
- использованием формул для из каждой ячейки заменяя этот знак
- искомый_текст) и звездочку (
Функция ПОИСКБ отсчитывает поПОИСКБ расположено несколько чисел преобразована в 123116) из строки «Филатова123»Скопируйте образец данных из строки на основе
Несколько чисел в текстовой строке (через формулы, все числа склеиваются в одно)
порядку в одном на – ПРАВСИМВ. кода) какой-то символ, пустое значение «», в которой требуется работы с текстом. по одному (первому) и последующие пять, в следующей позиции,
- * два байта нанаходят одну текстовую и их нужно или введите пробел получим «123». следующей таблицы и
- заданного числа символов. столбце и соответственно Жмёте Заменить всё. то можно использовать
- поскольку логическое ЛОЖЬ выделить требуемое количество
- Форма для округления числовых символу. знаков текстовой строкой и возвращает число). Вопросительный знак соответствует каждый символ, только
строку в другой вывести в разные (если в дальнейшемПусть текстовая строка Филатова123 вставьте их вФункция ЛЕВБ возвращает первый по строкам вниз,При этом ничего
Несколько чисел в текстовой строке (через формулы, все числа размещаются в разные ячейки)
функцию ПОИСК. эквивалентно числовому 0 символов слева. Аргумент значений имеет следующий «объем.» 9. Функция любому знаку, звездочка —
- если языком по и возвращают начальную ячейки, то можно потребуется вывести числа
- находится в ячейке ячейку A1 нового символ или несколько и потом обратно выделять не надо,Olga /pilot/ stepanova (нуль);
- обязателен для заполнения. вид:Пример 2. В таблицу
- Доход: объемПОИСК любой последовательности знаков. умолчанию является язык
позицию первой текстовой предложить следующий алгоритм в отдельные ячейки);A1 листа Excel. Чтобы первых символов текстовой вставить в этот замена произойдёт на: ctrl+x — вырезать,при аргументах: («текст»;15/7) вернет
– необязательный дляВ ячейке B3 введена Excel подтягиваются данные=ПСТР(A3;ПОИСК(» «;A3)+1,4)всегда возвращает номер Если требуется найти с поддержкой БДЦС. строки (считая от (см. файл примера,
Как получить число с любой позиции в строке
Если ваша задача заключается в извлечении числа из любого места строки, вы можете воспользоваться следующей умопомрачительной формулой, опубликованной на MrExcel форум:
=СУММПРОИЗВ(СРЕДН(0&A2, НАИБОЛЬШИЙ(ИНДЕКС(ЧИСЛО(—СРЕДН(A2, СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))), 1)) * СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2) )), 0), СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))))+1, 1) * 10^СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)))/10)
Где A2 — исходная текстовая строка.
Разбор этой формулы потребует отдельной статьи, поэтому вы можете просто скопировать ее на свой рабочий лист, чтобы убедиться, что она действительно работает
Однако при изучении результатов можно заметить один незначительный недостаток — если исходная строка не содержит числа, формула возвращает ноль, как в строке 6 на скриншоте выше. Чтобы исправить это, вы можете обернуть формулу в оператор IF, логическая проверка которого проверяет, содержит ли исходная строка какое-либо число. Если это так, формула извлекает число, иначе возвращает пустую строку:
=ЕСЛИ(СУММ(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2, {«0″,»1″,»2″,»3″,»4″,»5″,»6″,»7″,» 8″,»9″}, «»)))>0, СУММПРОИЗВ(СРЕДН(0&A2, НАИБОЛЬШИЙ(ИНДЕКС(ЧИСЛО(—СРЕДН(A2,СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2))), 1))* СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2))),0), СТРОКА(ДВССЫЛ(«$1:$»&ДЛС(A2))))+1,1) * 10^СТРОКА(ДВССЫЛ («$1:$»&ДЛСТР(A2)))/10),»»)
Как показано на снимке экрана ниже, улучшенная формула прекрасно работает (спасибо Алексу, нашему гуру Excel, за это улучшение):
В отличие от всех предыдущих примеров результатом этой формулы является число
Чтобы убедиться в этом, просто обратите внимание на выровненные по правому краю значения в столбце B и усеченные ведущие нули
Кончик. В Excel 365 — Excel 2019 есть гораздо более простое решение с помощью функции TEXTJOIN. См. Как удалить текст и сохранить числа.
Извлекаем в EXCEL число из конца текстовой строки
Пусть текстовая строка Филатова123 находится в ячейке A1 . Чтобы извлечь число 123, расположенное справа, запишем формулу массива : =1*ПСТР(A1; ПОИСКПОЗ(ЛОЖЬ;ЕОШИБКА(1*ПСТР(A1;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР(A1)));1));0); 255)
Если число расположено в начале или середине значения, то формула работать не будет (см. файл примера ).
Общая формула, позволяющая извлечь число из начала, середины и конца текстовой строки посложнее и выглядит так:
О построении этой формулы смотрите ]]> здесь ]]> . Это также формула массива — не забудьте нажать CRTL+SHIFT+ENTER .
Несколько чисел в текстовой строке (через WORD)
Если в текстовой строке расположено несколько чисел, то вышеуказанные формулы работать не будут. В этом случае можно предложить следующий подход:
- скопируйте столбец с текстовыми строками, содержащие числа, в MS WORD;
- нажмите CTRL+H , т.е. вызовите инструмент Найти и Заменить ;
- В поле Найти введите ^$ (любая буква) или его выберите из меню (см. рисунок ниже);
- в поле Заменить на: оставьте пустым (если все числа в строке нужно вывести одним числом, т.е. текстовая строка 123Филато11в6а будет преобразована в 123116 ) или введите пробел (если в дальнейшем потребуется вывести числа в отдельные ячейки);
- нажмите ОК, буквы будут заменены пробелами или просто убраны;
- скопируйте столбец обратно в MS EXCEL.
Если требуется вывести полученные числа в отдельные ячейки, то используйте инструмент Текст-по-столбцам (мастер текстов) или материал статьи Разнесение текстовых строк по столбцам.
Несколько чисел в текстовой строке (через формулы, все числа склеиваются в одно)
Если в текстовой строке расположено несколько чисел, то для извлечения чисел можно вывести каждый символ текстовой строки в отдельную ячейку (см. файл примера, лист Общий случай ) для этого:
- подсчитайте количество букв в текстовой строке (ячейка А3 ) с помощью функции ДЛСТР() в ячейке B3 ;
- создайте табличку, состоящую из количества столбцов = количеству букв в текстовой строке;
- заголовкам столбцов присвойте порядковые номера ;
- в ячейку С3 введите формулу =ПСТР($A3;C$2;1) и протяните ее вправо, заполнив все столбцы.
Заменив формулу =ПСТР($A3;C$2;1) на =ЕСЛИ(ЕОШ(—ПСТР($A3;C$2;1));»»;—ПСТР($A3;C$2;1)) можно вывести только числовые значения. Собрать все числовые значения в одну ячейку можно с помощью формулы =—Т(C7&D7&E7&F7&G7&H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7) или =СЦЕПИТЬ(C7;D7;E7;F7;G7;H7;I7;J7;K7;L7;M7;N7;O7;P7;Q7)+0
Несколько чисел в текстовой строке (через формулы, все числа размещаются в разные ячейки)
Если в текстовой строке расположено несколько чисел и их нужно вывести в разные ячейки, то можно предложить следующий алгоритм (см. файл примера, лист Общий случай ):
- как в предыдущем примере каждый символ текстовой строки выводим в отдельную ячейку;
- с помощью функций СЖПРОБЕЛЫ() и ( СЦЕПИТЬ() или аперсанда &) выводим текстовую строку без букв (т.е. только числа), между числами — 1 пробел;
- с помощью функции ПОИСК() находим начальные позиции каждого числа;
- с помощью функции ПСТР(), ЛЕВСИМВ(), ПРАВСИМВ() выводим числа в отдельные ячейки.
Решение из файла примера позволяет извлекать от 2-х до 4-х чисел из текстовых строк длиной до 15 символов. При желании решение можно легко расширить на большее количество символов и чисел.
Совет: В статье Извлекаем число из начала текстовой строки приведено решение соответствующей задачи. В статье Извлекаем число из середины текстовой строки приведено решение соответствующей задачи.
Функция Split
Разбор текстовых строк в MS EXCEL
Cинтаксис.
Функция ПСТР возвращает указанное количество знаков, начиная с указанной вами позиции.
Функция Excel ПСТР имеет следующие аргументы:
Где текст — это исходная текстовая строка. Далее следует позиция первого символа, который вы хотите извлечь, и количество их для извлечения.
Все 3 аргумента обязательны.
Например, чтобы извлечь 6 знаков из A2, начиная с 17-го, используйте эту формулу:
Результат может выглядеть примерно так:
5 вещей, которые вы должны знать о функции Excel ПСТР
Как вы только что убедились, в использовании функции ПСТР в Excel нет ничего страшного. И помня следующие простые факты, вы избежите наиболее распространенных ошибок.
При решении реальных задач в Excel вам чаще всего потребуется использовать ПСТР в сочетании с другими функциями, как показано в следующих примерах.
VBA Excel. Функция Split (синтаксис, параметры, значения)
Использование функции Split в VBA Excel, ее синтаксис и параметры. Значения, возвращаемые функцией Split. Примеры использования.
Функция Split предназначена в VBA Excel для разделения строки на подстроки по специальным меткам — разделителям. Разделителем может быть как отдельный символ, так и строка из нескольких символов. Функция Split по своему действию является обратной функции Join, которая создает одну строку из массива подстрок.
- Синтаксис функции
- Параметры функции
- Возвращаемые значения
Синтаксис функции
Split (Expression,,,)
Обязательным параметром функции Split является Expression — если остальные параметры явно не указаны, используются их значения по-умолчанию.
Параметры функции
| Параметр | Описание | Значение по умолчанию |
| Expression | Строка, содержащая подстроки и разделители | Нет |
| Delimiter | Разделитель, представляющий один или более символов | Пробел |
| Limit | Максимальное число подстрок, на которые должна быть разделена входная строка | -1 |
| Compare* | Определяет, какое используется сравнение, двоичное — CompareMethod.Binary (0) или текстовое — CompareMethod.Text (1) |
*Если используется двоичное сравнение (0 или CompareMethod.Binary), функция чувствительна к регистру букв. Если используется текстовое сравнение (1 или CompareMethod.Text), функция не чувствительна к регистру букв.
Возвращаемые значения
Функция Split возвращает одномерный массив с индексацией от нуля, который содержит указанное параметром Limit число подстрок. Чаще всего, функция Split используется со значением параметра Limit по-умолчанию, равному -1, когда возвращаются все найденные в исходной строке подстроки.
Пример 1
Sub Test1()
Dim a() As String
a = Split(«vremya ne zhdet»)
MsgBox a(0) & vbNewLine & a(1) & vbNewLine & a(2)
End Sub
Результат в MsgBox: vremya ne zhdet
В первом примере используются Delimiter и Limit по-умолчанию.
Пример 2
Sub Test2()
Dim a() As String
a = Split(«vremya-ne-zhdet»,»-«, 2)
MsgBox a(0) & vbNewLine & a(1)
End Sub
Результат в MsgBox: vremya ne-zhdet
Во втором примере Delimiter = «-«, а Limit = 2.
Для присваивания результатов функции Split используется предварительно объявленный текстовый динамический массив, который можно использовать в строке присваивания с пустыми скобками или без них. В представленных выше примерах массив указан без скобок.
Вы можете скопировать коды из приведенных примеров в модуль VBA своей рабочей книги Excel, посмотреть, как они работают. Поэкспериментируйте, подставляя свои данные, чтобы на практике ознакомиться с возможностями функции Split.
Извлечь текст из середины текстовой строки
Далее мы обсудим, как извлечь текст из середины текстовой строки.
Чтобы извлечь текст из середины текстовой строки, нам нужно будет использовать функции RIGHT, SEARCH и LEN, чтобы получить текст с правой стороны строки, а затем использовать функции MID и LEN, чтобы получить текст в середине. . Мы также собираемся включить функцию TRIM, чтобы обрезать любые пробелы по обе стороны от текстовой строки.
| 12 | = RIGHT (B3; LEN (B3) -SEARCH («», B3) -LEN (TRIM (MID (B3, SEARCH («», B3,1) +1,ПОИСК («», B3, ПОИСК («», B3,1) +1) -ПОИСК («», B3,1)))) — 1) |
Эта формула будет работать, только если в текстовой строке более одного пробела. Если есть только один пробел, будет возвращена ошибка с #VALUE.
Чтобы решить эту проблему, для имен без отчества или инициалов мы можем использовать исходную формулу, используя функции MID и SEARCH.
| 1 | = MID (B3; ПОИСК («»; B3) +1,999)) |
Затем мы могли бы объединить две формулы с помощью функции ЕСЛИОШИБКА.
| 12 | = ЕСЛИОШИБКА (ПРАВО (B3; LEN (B3) -ПОИСК («»; B3) -LEN (TRIM (MID (B3; SEARCH («»; B3,1) +1,ПОИСК («», B3, ПОИСК («», B3,1) +1) -ПОИСК («», B3,1)))) — 1), MID (B3, ПОИСК («», B3) +1,999) ) |
Затем мы можем использовать функции MID и LEN, чтобы получить отчество или инициал.
| 1 | = СРЕДНЕЕ (B3; LEN (C3) + 1; LEN (B3) -LEN (C3 & D3)) |
Текстовые функции в Эксель
Пример 3. Проверьте правильность имени файла
В этом последнем примере мы хотим проверить правильность имени файла. Есть три правила.
- Должно заканчиваться на .pdf
- Он должен содержать АА
- Он должен содержать 1234 после А
В следующих таблицах показаны некоторые допустимые и недействительные элементы:
| Имя файла | Статус |
| AA1234.pdf | Действителен |
| AA_ljgslf_1234.pdf | Действителен |
| AA1234.pdf1 | Недействительно — не заканчивается на .pdf |
| 1234 AA.pdf | Недействительно — АА не до 1234 |
| 12_AA_1234_NM.pdf | Действителен |
Сначала мы сделаем это, используя функции InStr и Right.
Sub IspInstr()
Dim f As String: f = "AA_1234_(5).pdf"
' Сначала найдите АА, так как 1234 должен идти после
Dim lPos As Long: lPos = InStr(f, "AA")
' Ищите 1234 и убедитесь, что последние четыре символа - .pdf
Debug.Print InStr(lPos, f, "1234") > 0 And Right(f, 4) = ".pdf"
End Sub
Этот код очень грязный. К счастью для нас, у VBA есть . Мы можем проверить шаблон строки без необходимости искать элементы и позиции и т.д. Мы используем оператор Like в VBA для сопоставления с шаблоном. Пример ниже показывает, как это сделать.
Sub IspSravnenie()
Dim f As String: f = "AA_1234_(5).pdf"
' Определить шаблон
Dim pattern As String: pattern = "*AA*1234*.pdf"
' Проверьте каждый элемент по шаблону
Debug.Print f Like pattern ' ИСТИНА
End Sub
В приведенном выше примере звездочка в шаблоне относится к любому количеству символов.
Давайте разберем этот паттерн * AA * 1234 * .pdf
*- любая группа символов AA — точные символы AА
*- любая группа символов 1234 — точные символы 1234
*- любая группа символов .pdf — точные символы .pdf
Чтобы показать, что это работает правильно, давайте попробуем это на всех именах примеров в таблице.
Sub IspSravnenieTest()
' Создать коллекцию имен файлов
Dim coll As New Collection
coll.Add "AA1234.pdf"
coll.Add "AA_ljgslf_1234.pdf"
coll.Add "AA1234.pdf1"
coll.Add "1234 AA.pdf"
coll.Add "12_AA_1234_NM.pdf"
' Определить шаблон
Dim pattern As String: pattern = "*AA*1234*.pdf"
' Проверьте каждый элемент по шаблону
Dim f As Variant
For Each f In coll
Debug.Print f Like pattern
Next f
End Sub
На выходе:
ИСТИНАИСТИНАЛОЖЬЛОЖЬИСТИНА
Чтобы узнать больше о сопоставлении с шаблоном и ключевом слове Like, ознакомьтесь с этой .
Как извлечь число из конца текстовой строки.
Если у вас есть столбец буквенно-цифровых значений, в котором число всегда идет после текста, вы можете использовать одну из следующих формул, чтобы вытащить из них числа.
Важное замечание! В приведенных ниже формулах извлечение выполняется с помощью функций ПРАВСИМВ и ЛЕВСИМВ, которые относятся к категории текстовых функций. Эти функции всегда возвращают текст. В нашем случае результатом будет числовая подстрока, которая с точки зрения Excel также является текстом, а не числом. Если вам нужно, чтобы результат был числом (которое можно использовать в дальнейших вычислениях), оберните соответствующую формулу в функцию ЗНАЧЕН, или выполните с ней простейшую математическую операцию (например, двойное отрицание). Чтобы извлечь число из строки «текстовое число», первое, что вам нужно знать, — это с какой позиции начать операцию. Итак, давайте определим положение первой цифры с помощью этой общего выражения:
Чтобы извлечь число из строки «текстовое число», первое, что вам нужно знать, — это с какой позиции начать операцию. Итак, давайте определим положение первой цифры с помощью этой общего выражения:
О логике вычислений мы поговорим чуть позже. На данный момент просто замените слово «ячейка» ссылкой на позицию, содержащую исходный текст (в нашем случае A2), и запишите получившееся выражение в любую пустую клетку той же строки, скажем, в B2:
Хотя формула содержит константу массива, это обычное выражение, которое вводится обычным способом: нажатием клавиши Enter.
Как только позиция первой цифры определена, можно использовать функцию ПРАВСИМВ для извлечения числа. Чтобы узнать, сколько символов нужно извлечь, вы вычитаете позицию первой цифры из общей длины строки и добавляете единицу к результату, потому что первая цифра также должна быть включена:
Где A2 – исходная ячейка, а B2 – позиция первой цифры.
На следующем скриншоте показаны результаты:
Чтобы исключить вспомогательный столбец, содержащий позицию первой цифры, вы можете встроить формулу МИН непосредственно в функцию ПРАВСИМВ следующим образом:
Чтобы формула возвращала именно число, а не числовую строку, вложите ее в функцию ЗНАЧЕН:
Или просто примените двойное отрицание, использовав два знака «минус»:
Другой способ извлечь число из конца строки — использовать вот такое выражение:
Используя исходный текст в A2, вы записываете приведенную ниже формулу в B2 или любую другую пустую ячейку в той же строке, а затем копируете её вниз по столбцу:
Примечание. Эти формулы предназначены для случая, когда числа находятся только в конце текстовой строки. Если некоторые цифры также находятся в середине или в начале, то ничего не будет работать.
Этих недостатков не имеет третья формула, которая извлекает только последнее число в тексте, игнорируя все предыдущие:
На скриншоте ниже вы видите результат ее работы.
Как видите, цифры в начале или в середине текста игнорируются
Также обратите внимание, что результатом, как и в предыдущих формулах, является число, записанное в виде текста. Как превратить его в нормальное число, мы уже рассмотрели выше в этой статье
Примечание. Если вы используете Excel 2019 или более ранние версии, нужно использовать формулу массива, нажав при вводе комбинацию Ctrl+Shift+Enter. Если у вас Office365, вводите как обычно, через Enter.
Синтаксис.
Она подсчитывает и возвращает длину текста. Здесь всего один аргумент, который, естественно, обязателен:
Где текст — это текстовая строка, для которой вы хотите подсчитать число знаков. Нет ничего проще, правда?
Ниже вы найдете пару простых формул, чтобы получить общее представление о том, как это работает.
=ДЛСТР(123) — возвращает 3, потому что в текстовый аргумент передается 3 цифры .
=ДЛСТР(«яблоко») — возвращает 6, потому что слово яблоко состоит из 6 букв. Как и любая другая формула Excel, она требует заключения текстовых выражений в двойные кавычки, которые не учитываются при подсчёте.
Впрочем, на практике вы, скорее всего, будете указывать ссылки на ячейки, а не записывать какой-то текст в саму формулу.
Например, чтобы получить длину текста в A1, вы должны записать:
=ДЛСТР(A1)
Ниже приведены более содержательные примеры с подробными объяснениями и скриншотами.
На первый взгляд функция ДЛСТР выглядит настолько простой, что не требует дополнительных пояснений. Однако есть несколько полезных приемов, которые могут помочь вам настроить её под ваши конкретные нужды.
Удаление текста с помощью заливки Flash
Flash Fill — это инструмент, который был представлен в Excel 2013 и доступен во всех последующих версиях.
Он работает, выявляя закономерности, когда вы вручную вводите данные, а затем экстраполируете их, чтобы получить данные для всего столбца.
Поэтому, если вы хотите удалить текст до или после определенного символа, вам просто нужно показать flash fairy, как будет выглядеть результат (введя его вручную пару раз), и flash fill автоматически распознает узор и даст вам результаты.
Позвольте показать вам это на примере.
Ниже у меня есть набор данных, из которого я хочу удалить весь текст после запятой.
Вот шаги, чтобы сделать это с помощью Flash Fill:
- В ячейке B2, которая является соседним столбцом наших данных, введите вручную «Джеффри Хаггинс» (что является ожидаемым результатом).
- В ячейке B3 введите «Тим Скотт» (ожидаемый результат для второй ячейки).
- Выберите диапазон B2: B10
- Перейдите на вкладку «Главная»
- В группе «Редактирование» щелкните раскрывающийся список «Заливка».
- Нажмите на Flash Fill.
Вышеупомянутые шаги дадут вам результат, как показано ниже:
Вы также можете использовать сочетание клавиш Control + E для Flash Fill после выбора ячеек в столбце результатов (столбец B в нашем примере)
Flash Fill — замечательный инструмент, и в большинстве случаев он может распознать узор и дать вам правильный результат. но в некоторых случаях он может не распознать шаблон правильно и может дать неверные результаты.
Так что не забудьте еще раз проверить результаты Flash Fill.
И точно так же, как мы удалили весь текст после определенного символа с помощью флэш-заливки, вы можете использовать те же шаги, чтобы удалить текст перед определенным символом. просто покажите вручную в соседнем столбце flash fill, как результат должен выглядеть как мой Интернет, и он сделает все остальное.