В Excel нет встроенной функции для извлечения чисел из строки в ячейке (и наоборот — удаления числовой части и извлечения текстовой части из буквенно-цифровой строки).
Однако это можно сделать с помощью функции Excel или простого кода VBA.
Позвольте мне сначала рассказать вам, о чем я говорю.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите извлечь числа из строк (как показано ниже):
Выбранный вами метод также зависит от версии Excel, которую вы используете:
- Для версий Excel до 2016 года вам нужно использовать немного более длинную формулу.
- Для Excel 2016 вы можете использовать недавно представленную функцию TEXTJOIN.
- Метод VBA доступен для всех версий Excel
Содержание:
Извлечение чисел из строк в Excel (формулы Excel 2016)
Эта формула работает только в Excel 2016, поскольку в ней используется недавно введенная функция СОЕДИНЕНИЕ ТЕКСТОВ.
Кроме того, эта формула может извлекать числа в начале, конце или середине текстовой строки.
Обратите внимание, что формула TEXTJOIN, описанная в этом разделе, объединяет все числовые символы.Например, если текст «10 билетов стоят 200 долларов», результат будет 10200.
Предположим, у вас есть набор данных, подобный следующему, и вы хотите извлечь числа из строк в каждой ячейке:
Приведенная ниже формула даст вам числовую часть строки в Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Это формула массива, поэтому вам нужно использовать " Контрол + Shift + Ввод " вместо использования Enter.
Эта формула вернет пробел (пустую строку), если в текстовой строке нет чисел.
Как работает эта формула?
Позвольте мне разбить эту формулу и попытаться объяснить, как она работает:
- СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))) - Эта часть формулы дает ряд чисел, начиная с единицы.Функция ДЛСТР в формуле возвращает общее количество символов в строке.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {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!Ошибки или числа на основе строк.Все текстовые символы в строке становятся #ЗНАЧ!Ошибки и все значения остаются как есть.Это происходит, когда мы умножаем функцию MID на 1.
- ЕСЛИОШИБКА((СРЕДН(A2,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))),1)*1)",") – При использовании функции ЕСЛИОШИБКА все #ЗНАЧ удаляются!Ошибка, останутся только цифры.Вывод этой части выглядит так - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – теперь функция TEXTJOIN просто объединяет строковые символы. (только числа) и игнорирует пустые строки.
Профессиональный совет:Если вы хотите проверить вывод частичной формулы, выберите ячейку, нажмите F2, чтобы войти в режим редактирования, выберите часть формулы, которую вы хотите вывести, и нажмите F9.Вы сразу увидите результаты.Затем не забудьте нажать Control + Z или Escape.Не нажимайте ввод.
Вы также можете использовать ту же логику для извлечения частей текста из буквенно-цифровых строк.Вот формула для получения текстовой части из строки:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Небольшое изменение в этой формуле заключается в том, что функция ЕСЛИ используется для проверки массива, который мы получаем из функции СРЕДН, на наличие ошибок.Если это ошибка, сохраните значение, в противном случае замените его пустым.
Затем используйте TEXTJOIN для объединения всех текстовых символов.
注意: Хотя эта формула действительна, в ней используется изменчивая функция (функция ДВССЫЛ).Это означает, что если вы используете его с огромным набором данных, получение результатов может занять некоторое время.Перед использованием этой формулы в Excel лучше всего создать резервную копию.
Извлечение чисел из строк в Excel (для Excel 2013/2010/2007)
Если у вас Excel 2013, 2010 или 2007, вы не можете использовать формулы TEXTJOIN, поэтому для этого вам придется использовать сложные формулы.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите извлечь все числа в строках в каждой ячейке.
Формула ниже сделает это:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Эта формула вернет пробел (пустую строку), если в текстовой строке нет чисел.
Хотя это формула массива, выНет необходимостиИспользуйте «Control-Shift-Enter», чтобы использовать его.Для этой формулы работает простой ввод.
Заслуга в этой формуле принадлежит удивительномуМистер Эксель Форум.
Опять же, эта формула будет извлекать все числа в строке независимо от позиции.Например, если текст «10 билетов стоят 200 долларов», результат будет 10200.
注意: Хотя эта формула действительна, в ней используется изменчивая функция (функция ДВССЫЛ).Это означает, что если вы используете его с огромным набором данных, получение результатов может занять некоторое время.Перед использованием этой формулы в Excel лучше всего создать резервную копию.
Разделяйте текст и числа в Excel с помощью VBA
Если вам часто нужно отделять текст от чисел (или извлекать числа из текста), вы также можете использовать метод VBA.
Все, что вам нужно сделать, это создать пользовательскую определяемую пользователем функцию (UDF) в Excel с помощью простого кода VBA, а затем использовать эту формулу VBA вместо длинной и сложной формулы.
Позвольте мне показать вам, как создать две формулы в VBA: одну для извлечения чисел и одну для извлечения текста из строк.
Извлечение чисел из строк в Excel (с использованием VBA)
В этой части я покажу вам, как создать пользовательскую функцию для получения только числовой части строки.
Вот код VBA, который мы будем использовать для создания этой пользовательской функции:
Функция GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = функция завершения результата
Вот шаги, чтобы создать эту функцию, а затем использовать ее на листе:
Теперь вы сможете использовать функцию GetText на листе.Поскольку мы уже сделали всю тяжелую работу в самом коде, все, что вам нужно сделать, это использовать формулу =GetNumeric(A2).
Это немедленно даст вам числовую часть строки.
Обратите внимание: поскольку рабочая книга теперь содержит код VBA, ее необходимо сохранить с расширением .xls или .xlsm.
Если вам необходимо часто использовать эту формулу, вы также можете сохранить ее в своей личной книге макросов.Это позволит вам использовать эту пользовательскую формулу в любой рабочей книге Excel, которую вы используете.
Извлечь текст из строки в Excel (используя VBA)
В этой части я покажу вам, как создать пользовательскую функцию для получения только текстовой части строки.
Вот код VBA, который мы будем использовать для создания этой пользовательской функции:
Функция GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Если не (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Next i GetText = функция завершения результата
Вот шаги, чтобы создать эту функцию, а затем использовать ее на листе:
Теперь вы сможете использовать функцию GetNumeric на листе.Поскольку мы сделали всю тяжелую работу в самом коде, все, что вам нужно сделать, это использовать формулу =GetText(A2).
Это немедленно даст вам числовую часть строки.
Обратите внимание: поскольку рабочая книга теперь содержит код VBA, ее необходимо сохранить с расширением .xls или .xlsm.
Если вам необходимо часто использовать эту формулу, вы также можете сохранить ее в своей личной книге макросов.Это позволит вам использовать эту пользовательскую формулу в любой рабочей книге Excel, которую вы используете.
