✅ Извлечение чисел из строки в Excel (с помощью формул или VBA)

Содержание

Извлечение чисел из строки в Excel (Формула для Excel 2016)
Извлечение чисел из строки в Excel (для Excel 2013/2010/2007)
Разделение текста и чисел в Excel с помощью VBA

Посмотреть видео - Как извлекать числа из текстовой строки в Excel (с помощью формулы и VBA)

В Excel нет встроенной функции для извлечения чисел из строки в ячейке (или наоборот - удалить числовую часть и извлечь текстовую часть из буквенно-цифровой строки).

Однако это можно сделать с помощью коктейля из функций Excel или простого кода VBA.

Позвольте мне сначала показать вам, о чем я говорю.

Предположим, у вас есть набор данных, как показано ниже, и вы хотите извлечь числа из строки (как показано ниже):

Выбранный вами метод также будет зависеть от используемой версии Excel:

Для версий до Excel 2016 необходимо использовать немного более длинные формулы.
Для Excel 2016 вы можете использовать недавно представленную функцию TEXTJOIN
Метод VBA можно использовать во всех версиях Excel.

Щелкните здесь, чтобы загрузить файл примера

Извлечение чисел из строки в Excel (Формула для Excel 2016)

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

Кроме того, эта формула может извлекать числа в начале, конце или середине текстовой строки.

Обратите внимание, что формула TEXTJOIN, описанная в этом разделе, даст вам все числовые символы вместе. Например, если текст: «Цена 10 билетов составляет 200 долларов США», в результате вы получите 10200.

Предположим, у вас есть набор данных, как показано ниже, и вы хотите извлечь числа из строк в каждой ячейке:

Ниже приведена формула, которая даст вам числовую часть строки в 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 теперь просто объединяет строковые символы это остается (это только числа) и игнорирует пустую строку.

Совет профессионала: Если вы хотите проверить вывод части формулы, выберите ячейку, нажмите F2, чтобы перейти в режим редактирования, выберите часть формулы, для которой вы хотите получить результат, и нажмите F9. Вы сразу увидите результат. А затем не забудьте нажать Control + Z или нажать клавишу Escape. НЕ нажимайте клавишу ввода.

Загрузите файл примера

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

= TEXTJOIN (""; TRUE; IF (ISERROR (MID (A2; ROW (INDIRECT ("1:" & LEN (A2)))); 1) * 1), MID (A2; ROW (INDIRECT ("1:" & LEN (А2))), 1), ""))

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

Затем TEXTJOIN используется для объединения всех текстовых символов.

Осторожность: Хотя эта формула отлично работает, она использует изменчивую функцию (функция ДВССЫЛКА). Это означает, что в случае, если вы используете это с огромным набором данных, может потребоваться некоторое время, чтобы получить результаты. Лучше всего создать резервную копию, прежде чем использовать эту формулу в Excel.

Извлечение чисел из строки в Excel (для Excel 2013/2010/2007)

Если у вас есть Excel 2013. 2010. или 2007, вы не можете использовать формулу TEXTJOIN, поэтому вам придется использовать сложную формулу, чтобы это сделать.

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

Приведенная ниже формула сделает это:

= ЕСЛИ (СУММ (LEN (A2) -LEN (ПОДСТАВИТЬ (A2; {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))> 0, СУММПРОИЗВ (MID (0 & A2, LARGE (ИНДЕКС (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", чтобы использовать это. Для этой формулы работает простой ввод.

Заслуга этой формулы принадлежит замечательному форуму Mr. Excel.

Опять же, эта формула извлечет все числа в строке независимо от позиции. Например, если текст: «Цена 10 билетов составляет 200 долларов США», в результате вы получите 10200.

Разделение текста и чисел в Excel с помощью VBA

Если вам часто приходится разделять текст и числа (или извлекать числа из текста), вы также можете использовать метод VBA.

Все, что вам нужно сделать, это использовать простой код VBA для создания настраиваемой пользовательской функции (UDF) в Excel, а затем вместо использования длинных и сложных формул использовать эту формулу 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 = Результат Конечная функция

Вот шаги, чтобы создать эту функцию, а затем использовать ее на рабочем листе:

Перейдите на вкладку Разработчик.
Щелкните Visual Basic (вы также можете использовать сочетание клавиш ALT + F11)
В открывшейся серверной части редактора VB щелкните правой кнопкой мыши любой из объектов книги.
Перейдите во вкладку "Вставить" и нажмите "Модуль". Это вставит объект модуля для книги.
В окне кода модуля скопируйте и вставьте указанный выше код VBA.
Закройте редактор VB.

Теперь вы сможете использовать функцию 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 If Not (IsNumeric (Mid (CellRef, i, 1))) Then Result = Result & Mid (CellRef, i, 1 ) Далее i GetText = Результат Конечная функция

Вот шаги, чтобы создать эту функцию, а затем использовать ее на рабочем листе:

Перейдите на вкладку Разработчик.
Щелкните Visual Basic (вы также можете использовать сочетание клавиш ALT + F11)
В открывшейся серверной части редактора VB щелкните правой кнопкой мыши любой из объектов книги.
Перейдите во вкладку "Вставить" и нажмите "Модуль". Это вставит объект модуля для книги.
- Если у вас уже есть модуль, дважды щелкните по нему (нет необходимости вставлять новый, если он у вас уже есть).
В окне кода модуля скопируйте и вставьте указанный выше код VBA.
Закройте редактор VB.

Теперь вы сможете использовать функцию GetNumeric на листе. Поскольку мы проделали всю тяжелую работу в самом коде, все, что вам нужно сделать, это использовать формулу = GetText (A2).

Это мгновенно даст вам только числовую часть строки.