Из этой статьи вы узнаете, как отделить текст от чисел в Excel, удалить только текст или удалить все числа из ячейки Excel с помощью формул и пользовательских функций. Вы также узнаете, как можно разделить текст и числа из ячейки на два отдельных столбца.
Представьте себе: вы получаете данные для анализа и обнаруживаете, что в одном столбце смешаны числа с текстом. Например, сведения о продажах записаны как “500 000 руб.”. В большинстве ситуаций, безусловно, будет удобнее разместить числа и текст в отдельных столбцах для более детального изучения и возможности производить с ними вычисления или иные действия.
Если вы работаете с однородными данными, вы, вероятно, могли бы использовать функции ЛЕВСИМВ, ПРАВСИМВ и ПСТР для извлечения одинакового количества символов из одной и той же позиции. Но это идеальный сценарий. В реальной жизни вы, скорее всего, будете иметь дело с разнородными данными, где числа стоят перед текстом, после текста или между текстом, количество чисел и букв в каждой ячейке разное.
В приведенных ниже примерах представлены решения именно для таких случаев.
Как удалить текст из ячейки и оставить только числа
Решение работает в Excel 365, Excel 2021 и Excel 2019.
В Microsoft Excel 2019 появилось несколько новых функций, недоступных в более ранних версиях, и мы собираемся использовать одну из таких функций, а именно ОБЪЕДИНИТЬ, для удаления текста из ячейки, содержащей текст и числа.
Общая формула для Excel 2019 выглядит так:
ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИ(ЕОШ(ПСТР(ячейка ; СТРОКА(ДВССЫЛ( "1:"&ДЛСТР(ячейка ) )); 1) *1); ПСТР(ячейка ; СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ячейка ))); 1); ""))
В Excel 365 и 2021 можно использовать:
ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИ(ЕОШИБКА(ПСТР( ячейка ; ПОСЛЕД(ДЛСТР( ячейка )); 1)*1); ""))
На первый взгляд формулы могут показаться немного громоздкими и сложными, но они работают :)
Например, чтобы удалить все буквы из ячейки A2, введите одну из приведенных ниже формул в B2, а затем скопируйте ее при необходимости в нужное количество ячеек.
В Excel 365 – 2019:
=ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИОШИБКА(ПСТР(A2; СТРОКА(ДВССЫЛ( "1:"&ДЛСТР(A2))); 1) *1; ""))
В Excel 2019 и более ранних версиях ее необходимо ввести как формулу массива при помощи комбинации клавиш Ctrl + Shift + Enter. В динамическом массиве Excel (то есть, в последних версиях программы) это работает как обычная формула, которую можно просто вводить через Enter.
В Excel 365 и 2021:
=ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИОШИБКА(ПСТР(A2; ПОСЛЕД(ДЛСТР(A2)); 1) *1; ""))
В результате из ячейки будет удален весь текст и останутся только цифры:
Часто случается, что в тексте у вас есть десятичное число (например, сумма с рублями и копейками). И вы хотите оставить в ячейке именно это десятичное число, а не просто цифры. Такой случай вы видите в ячейке А8 на скриншоте выше.
Чтобы в этом случае удалить весь текст из ячейки, но оставить конкретный разделитель между числами, попробуйте эту формулу:
=ОБЪЕДИНИТЬ("";ИСТИНА; ЕСЛИОШИБКА(ПСТР( ячейка ;СТРОКА(ДВССЫЛ("1:"&ДЛСТР( ячейка )));1)*1; ЕСЛИ(ПСТР( ячейка ;СТРОКА(ДВССЫЛ("1:"&ДЛСТР( ячейка )));1)=".";" разделитель ";"")))
К примеру, чтобы оставить точку между целой и дробной частью числа и получить после удаления всех букв из ячейки не просто набор цифр, а нормальное десятичное число, используйте формулу
=ОБЪЕДИНИТЬ("";ИСТИНА; ЕСЛИОШИБКА(ПСТР(A2;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1)*1; ЕСЛИ(ПСТР(A2;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)));1)=".";".";"")))
А если вы хотите удалить текст и разделить оставшиеся числа, к примеру, пробелом, попробуйте такую формулу:
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(СЦЕП(ЕСЛИ(ЕЧИСЛО(--ПСТР(ячейка ;СТРОКА($1:$91);1)); ПСТР(ячейка ;СТРОКА($1:$91);1);" ")));" ";"[разделитель]")
К примеру, чтобы текст был удален и числа были разделены пробелами:
=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(СЦЕП(ЕСЛИ(ЕЧИСЛО(--ПСТР(A2;СТРОКА($1:$91);1)); ПСТР(A2;СТРОКА($1:$91);1);" ")));" ";" ")
Результат вы видите на скриншоте ниже.
Примечание. Для Excel 2016–2007 решение также существует, но формула гораздо сложнее. Вы можете найти ее в этой статье: Как быстро извлечь число из текста в Excel .
Пользовательская функция для удаления текста из ячейки
Решение работает для всех версий Excel.
Если вы используете более старую версию Excel или вам слишком сложно запомнить приведенные выше формулы, ничто не мешает вам создать собственную функцию с более простым синтаксисом и удобным именем, например RemoveText . Пользовательскую функцию (UDF) можно записать двумя способами:
Код VBA 1:
Здесь мы просматриваем каждый символ исходной строки один за другим и проверяем, является ли он числом или нет. Если встретилось число, то оно добавляется в итоговый результат.
Function RemoveText(str As String)
Dim sRes As String
sRes = ""
For i = 1 To Len(str)
If True = IsNumeric(Mid(str, i, 1)) Then
sRes = sRes & Mid(str, i, 1)
End If
Next i
RemoveText = sRes
End FunctionКод VBA 2:
Код создает объект для обработки регулярного выражения. Используя RegExp, мы удаляем из исходной строки все символы, кроме цифр от 0 до 9.
Function RemoveText(str As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[^0-9]"
RemoveText = .Replace(str, "")
End With
End FunctionНа небольших данных оба кода будут работать одинаково хорошо. На больших листах, где функция вызывается сотни или тысячи раз, второй код, использующий VBScript.RegExp, будет работать быстрее.
Подробные инструкции по вставке кода в книгу можно найти здесь: Как создать пользовательскую функцию VBA в Excel.
Более подробно об использовании регулярных выражений вы можете прочитать здесь: Удаление символов или текста при помощи регулярных выражений.
Какой бы подход вы ни выбрали, с точки зрения конечного пользователя применение функции удаления текста и оставления цифр очень простое:
RemoveText([текст])
Например, чтобы удалить текст из ячейки A2, формула будет следующей:
=RemoveText(A2)
Скопируйте формулу вниз по столбцу, и вы получите такой результат:
Примечание. И формулы, и пользовательская функция возвращают число, записанное как текст. Чтобы превратить его в настоящее число, умножьте результат на 1 (или дважды – на минус 1), прибавьте ноль, или оберните формулу функцией ЗНАЧЕН. Например:
=RemoveText(A2) + 0
=--RemoveText(A2)
=ЗНАЧЕН(RemoveText(A2))
Как удалить цифры из ячейки и оставить только текст
Решение работает в Excel 365, Excel 2021 и Excel 2019.
Формулы для удаления чисел из буквенно-цифровой ячейки во многом аналогичны тем, которые обсуждались выше.
Для Excel 365 – 2019:
ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИ(ЕОШ(ПСТР( ячейка ; СТРОКА(ДВССЫЛ( "1:"&ДЛСТР( ячейка ) )); 1) *1); ПСТР( ячейка ; СТРОКА(ДВССЫЛ("1:"&ДЛСТР( ячейка ))); 1); ""))
или
=ПОДСТАВИТЬ((СЦЕП(ЕСЛИ(НЕ(ЕЧИСЛО(--ПСТР( ячейка ;СТРОКА($1:$256);1))); ПСТР( ячейка ;СТРОКА($1:$256);1);"")));" ";" ")
В Excel 2019 не забудьте создать формулу массива, завершив ввод формулы комбинацией Ctrl + Shift + Enter.
В Excel 365 и 2021 чтобы удалить все цифры из буквенно-цифровой ячейки, вы можете использовать эту формулу:
ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИ(ЕОШИБКА(ПСТР(ячейка; ПОСЛЕД(ДЛСТР(ячейка)); 1)*1); ПСТР(ячейка; ПОСЛЕД(ДЛСТР(ячейка)); 1); ""))
Например, чтобы удалить цифры из ячейки A2, можно использовать одну из этих формул:
=ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИ(ЕОШ(ПСТР(A2; СТРОКА(ДВССЫЛ( "1:"&ДЛСТР(A2) )); 1) *1); ПСТР(A2; СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))); 1); ""))
=ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИ(ЕОШИБКА(ПСТР(A2; ПОСЛЕД(ДЛСТР(A2)); 1) *1); ПСТР(A2; ПОСЛЕД(ДЛСТР(A2)); 1); ""))
=ПОДСТАВИТЬ((СЦЕП(ЕСЛИ(НЕ(ЕЧИСЛО(--ПСТР(A2;СТРОКА($1:$256);1))); ПСТР(A2;СТРОКА($1:$256);1);"")));" ";" ")
В результате из ячейки удаляются все цифры, а буквы сохраняются:
Как видно на скриншоте выше, формула удаляет цифры из любой позиции строки: в начале, в конце и в середине. Однако есть существенное замечание: если строка начинается с числа, за которым следует пробел, этот пробел сохраняется, что приводит к проблеме с ведущими пробелами (как в ячейке B2).
Чтобы избавиться от лишних пробелов перед текстом, оберните формулу в функцию СЖПРОБЕЛЫ следующим образом:
=СЖПРОБЕЛЫ(ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИ(ЕОШ(ПСТР(A2; СТРОКА(ДВССЫЛ( "1:"&ДЛСТР(A2) )); 1) *1); ПСТР(A2; СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))); 1); "")))
или
=СЖПРОБЕЛЫ(ОБЪЕДИНИТЬ(""; ИСТИНА; ЕСЛИ(ЕОШИБКА(ПСТР(A2; ПОСЛЕД(ДЛСТР(A2)); 1)+0); ПСТР(A2; ПОСЛЕД(ДЛСТР(A2)); 1); "")))
И вот что получилось:
Теперь ваши результаты абсолютно идеальны!
Пользовательская функция для удаления цифр из ячейки
Решение работает для всех версий Excel.
Помня, что надежная формула должна быть простой, я поделюсь кодом пользовательской функции (UDF), позволяющей удалить все числа из текста.
Код VBA 1:
Function RemoveNumbers(str As String)
Dim sRes As String
sRes = ""
For i = 1 To Len(str)
If False = IsNumeric(Mid(str, i, 1)) Then
sRes = sRes & Mid(str, i, 1)
End If
Next i
RemoveNumbers = sRes
End FunctionКод VBA 2:
Function RemoveNumbers(str As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers2 = .Replace(str, "")
End With
End FunctionКак и в случае с описанной выше функцией RemoveText, второй код лучше использовать на больших листах для оптимизации производительности.
После добавления кода в вашу книгу вы сможете удалить все цифры из ячейки с помощью этой пользовательской функции:
RemoveNumbers([текст])
В нашем случае формула в B2:
=RemoveNumbers(A2)
Чтобы обрезать ведущие пробелы, если таковые имеются, вложите эту формулу в функцию СЖПРОБЕЛЫ, как это уже делали ранее:
=СЖПРОБЕЛЫ(RemoveNumbers(A2))
Как разделить числа и текст на отдельные столбцы
В ситуации, когда вы хотите разделить текст и числа на два столбца, было бы неплохо выполнить эту работу с помощью одной формулы, согласны? Для этого мы просто объединим код пользовательских функций RemoveText и RemoveNumbers в одну функцию с именем SplitTextNumbers , или просто Split, или как вам нравится :)
Код VBA 1:
Function SplitTextNumbers(str As String, is_remove_text As Boolean) As String
Dim sNum, sText, sChar As String
sCurChar = sNum = sText = ""
For i = 1 To Len(str)
sCurChar = Mid(str, i, 1)
If True = IsNumeric(sCurChar) Then
sNum = sNum & sCurChar
Else
sText = sText & sCurChar
End If
Next i
If True = is_remove_text Then
SplitTextNumbers = sNum
Else
SplitTextNumbers = sText
End If
End FunctionКод VBA 2:
Function SplitTextNumbers(str As String, is_remove_text As Boolean) As String
With CreateObject("VBScript.RegExp")
.Global = True
If True = is_remove_text Then
.Pattern = "[^0-9]"
Else
.Pattern = "[0-9]"
End If
SplitTextNumbers = .Replace(str, "")
End With
End FunctionНаша новая пользовательская функция требует двух аргументов:
SplitTextNumbers (строка, is_remove_text)
Где is_remove_text — логическое значение, указывающее, какие символы следует удалить:
- 1 — удалить текст и сохранить цифры
- 0 — удалить цифры и сохранить текст
Для нашего примера формулы принимают следующий вид:
Чтобы удалить текст и оставить только цифры:
=SplitTextNumbers(A2; 1)
Чтобы удалить все цифры:
=SplitTextNumbers(A2; 0)
Примечание. Чтобы избежать потенциальной проблемы с ведущими пробелами, я рекомендую всегда заключать формулу, удаляющую цифры, в функцию СЖПРОБЕЛЫ:
=СЖПРОБЕЛЫ(SplitTextNumbers(A2; 0))
Вот как можно удалить текст или цифры из ячейки Excel.
Надеюсь, эта информация будет вам полезна.
