Извлечь числа из строки текста в Excel, естественно можно с помощью формул. Например, в этом может помочь следующая формула массива:
Тем не менее, у использованной выше формулы есть определенные минусы:
• Во-первых, все числа, например, из текста «Задача 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, в котором нужно применить функцию (лучше его копию)
2. Открыть редактор VBA с помощью комбинации клавиш Alt+F11
3. В верхнем левом углу нажать на «Insert» и затем «Module».
4. Скопировать текст функции и вставить в открывшееся окно в центре редактора VBA
5. Сохранить файл в формате xlsm (формат xlsx не сохраняет макросы!). Для этого открываем окно сохранить как при помощи клавиши F12 либо File -> Save as -> Browse. По открытии окна сохранения файла в поле «Тип файла» выбираем «Книга Excel с поддержкой макросов»
6. Подтверждаем сохранение. Теперь функция может использоваться как самая обычная функция на рабочем листе Excel. То есть ставим знак равно, и прописываем название нашей пользовательской функции «extractDelimitedNumbers». В скобках указываем текст, из которого должны быть извлечены числовые значения:
Если тебе интересно узнать, как устроен принцип работы этой функции и как её можно написать самостоятельно, то в следующем видео я подробно об этом рассказал:
Конечно, для этого видео нужно уже владеть определенными знаниями VBA. Если ты еще совсем новичок в области VBA, то также могу предложить посмотреть вот этот курс VBA: