Извлечение чисел из текста в Excel

Извлечь числа из строки текста в Excel, естественно можно с помощью формул. Например, в этом может помочь следующая формула массива:

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

Тем не менее, у использованной выше формулы есть определенные минусы:


• Во-первых, все числа, например, из текста «Задача 5 от 19 Ноября» выдаются не разделёнными, образую таким образом одно слитное число, тогда же как информация о том, что числа на самом деле в оригинальном тексте разделены другими словами потенциально может быть важной.


• Во-вторых, это формула массива, что также означает определенные ограничения при использовании подобной формулы в дэшбордах, её понимании менее заинтересованными в Excel коллегами и т.д.


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

Function extractDelimitedNumbers(ByVal strOriginalText As String) As String

Dim strExtractedNumbers As String

Dim lngTextLength As Long

lngTextLength = Len(strOriginalText)

Dim lngPositionCounter As Long

'Проверка, указано ли название файла

If strOriginalText <> "" Then

'Проверка каждой позиции названия

For lngPositionCounter = 1 To lngTextLength

'Если число...

If IsNumeric(Mid(strOriginalText, lngPositionCounter, 1)) = True Then

'... то сохраняем в переменную

strExtractedNumbers = strExtractedNumbers & Mid(strOriginalText, lngPositionCounter, 1)

'Разделение отдельно стоящих в названии чисел с помощью "_"

If lngPositionCounter + 1 <= lngTextLength Then

If IsNumeric(Mid(strOriginalText, lngPositionCounter + 1, 1)) = False Then

strExtractedNumbers = strExtractedNumbers & "_"

End If

End If

End If

Next lngPositionCounter

'Удаляем по итогу лишний нижний пробел, если таковой имеется

If Right(strExtractedNumbers, 1) = "_" Then

strExtractedNumbers = Left(strExtractedNumbers, Len(strExtractedNumbers) - 1)

End If

extractDelimitedNumbers = strExtractedNumbers

Else:

extractDelimitedNumbers = ""

End If

End Function

Как использовать этот код:

1. Открыть файл Excel, в котором нужно применить функцию (лучше его копию)

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

2. Открыть редактор VBA с помощью комбинации клавиш Alt+F11

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

3. В верхнем левом углу нажать на «Insert» и затем «Module».

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

4. Скопировать текст функции и вставить в открывшееся окно в центре редактора VBA

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

5. Сохранить файл в формате xlsm (формат xlsx не сохраняет макросы!). Для этого открываем окно сохранить как при помощи клавиши F12 либо File -> Save as -> Browse. По открытии окна сохранения файла в поле «Тип файла» выбираем «Книга Excel с поддержкой макросов»

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

6. Подтверждаем сохранение. Теперь функция может использоваться как самая обычная функция на рабочем листе Excel. То есть ставим знак равно, и прописываем название нашей пользовательской функции «extractDelimitedNumbers». В скобках указываем текст, из которого должны быть извлечены числовые значения:

Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост
Извлечение чисел из текста в Excel Microsoft Excel, Vba, Видео, Длиннопост

Если тебе интересно узнать, как устроен принцип работы этой функции и как её можно написать самостоятельно, то в следующем видео я подробно об этом рассказал:

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

MS, Libreoffice & Google docs

722 поста15K подписчика

Добавить пост

Правила сообщества

1. Не нарушать правила Пикабу

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях


Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.