Как разделить номера и текст из строки в Excel
Часто я получаю смешанные данные с поля и с сервера для анализа. Эти данные обычно грязные, столбец смешан с числом и текстом. При очистке данных перед анализом я разделяю числа и текст в отдельные столбцы. В этой статье я расскажу, как это сделать.
Сценарий:
Один наш друг с Exceltip.com задал этот вопрос в разделе комментариев. «Как разделить числа перед текстом и в конце текста с помощью формулы Excel. Например, 125EvenueStreet и LoveYou3000 и т. Д. » Для извлечения текста мы используем RIGHT, LEFT, MID и другие текстовые функции. Нам просто нужно знать, сколько текстов нужно извлечь. И здесь мы сделаем то же самое в первую очередь.
Извлечение числа и текста из строки, когда число находится в конце строки. Для примера выше я подготовил этот лист. В ячейке A2 у меня есть строка. В ячейке B2 я хочу текстовую часть, а в C2 — числовую часть.
Поэтому нам просто нужно знать позицию, с которой начинается число. Затем воспользуемся Left и другой функцией. Итак, чтобы получить позицию первого числа, мы используем приведенную ниже общую формулу:
Общая формула для определения позиции первого числа в строке:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},String_Ref&"0123456789")
Это вернет позицию первого числа.
В приведенном выше примере запишите эту формулу в любую ячейку.
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))
Извлечь текстовую часть
Он вернет 15, так как первое найденное число находится на 15-й позиции в тексте. Я объясню это позже.
Теперь, чтобы получить текст слева, нам просто нужно получить 15-1 символ из строки. Поэтому мы будем использовать link: / excel-text-extract-text-from-a-string-in-excel-using-excels-left-and-right-function [ЛЕВАЯ функция для извлечения текста.]
Формула для извлечения текста слева
=LEFT(A5,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))-1)
Здесь мы просто вычли 1 из числа, возвращаемого функцией MIN (link: / text-excel-search-function [SEARCH] (\ {0,1,2,3,4,5,6,7,8,9 }, A5 & «0123456789»)).
Извлечь часть номера
Теперь, чтобы получить числа, нам просто нужно получить числовые символы из первого найденного числа. Таким образом, мы вычисляем общую длину строки, вычитаем позицию первого найденного числа и прибавляем к нему 1. Просто. Да, это звучит сложно, это просто.
Формула для извлечения чисел справа
=RIGHT(A5,LEN(A5)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))+1)
Здесь мы просто получили общую длину строки с помощью `link: / len-functio [LEN function], а затем вычли позицию первого найденного числа и затем добавили к нему 1. Это дает нам общее количество чисел. Узнайте больше о //excel-text/extract-text-from-a-string-in-excel-using-excels-left-and-right-function.html[извлечение текста с помощью функций LEFT и RIGHT в Excel.] `
Таким образом, функциональная часть LEFT и RIGHT проста. Сложная часть — это MIN и SEARCH Part, которая дает нам позицию первого найденного числа. Давай поймем это.
Как это работает
Мы знаем, как работают функции ВЛЕВО и ВПРАВО. Мы рассмотрим основную часть этой формулы, которая определяет позицию первого найденного числа, а именно:
MIN (link: / text-excel-search-function [ПОИСК] (\ {0,1,2,3,4,5,6,7,8,9}, String & «0123456789»)
Функция link: / text-excel-search-function [SEARCH function] возвращает позицию текста в строке.
Функция SEARCH (‘text’, ‘string’) принимает два аргумента, сначала текст вы хотите искать, вторая строка, в которой вы хотите искать.
Здесь, в SEARCH, в позиции текста у нас есть массив чисел от 0 до 9. А в позиции строки у нас есть строка, которая объединена с » 0123456789 «с использованием оператора & *. Почему? Я вам скажу.
-
Каждый элемент в массиве \ {0,1,2,3,4,5,6,7,8,9} будет искать в данной строке и вернет свою позицию в строке формы массива с тем же индексом в массиве.
-
Если какое-либо значение не найдено, это вызовет ошибку. Следовательно, все формулы приведут к ошибке. чтобы избежать этого, мы объединили числа «0123456789» в тексте. Чтобы он всегда находил каждое число в строке.
Эти числа находятся в конце следовательно, не вызовет никаких проблем.
-
Теперь функция MIN возвращает наименьшее значение из массива, возвращаемого функцией SEARCH. Это наименьшее значение будет первым числом в строке. Теперь, используя эту функцию ЧИСЛО и ВЛЕВО и ВПРАВО, мы можем разделить текстовую и строковую части.
Давайте рассмотрим наш пример. В A5 есть строка с названием улицы и номером дома. Нам нужно разделить их по разным ячейкам.
Сначала давайте посмотрим, как мы получили позицию первого числа в строке.
MIN (SEARCH (\ {0,1,2,3,4,5,6,7,8,9}, A5 & «0123456789»)): это будет преобразовано в MIN (SEARCH (\ {0,1,2, 3,4,5,6,7,8,9}, ”Monta270123456789 *”))
Теперь, как я объяснил, поиск будет искать каждое число в массиве \ {0,1,2,3,4,5,6,7,8,9} в Monta270123456789 и вернет его позицию в виде массива. Возвращенный массив будет \ {8,9,6,11,12,13,14,7,16,17}. Как?
0 будет искать в строке. Он находится в позиции 8. Следовательно, наш первый элемент — 8. Обратите внимание, что наш исходный текст состоит всего из 7 символов. Возьми. 0 не является частью Monta27. Следующая 1 будет найдена в строке, и она также не является частью исходной строки, и мы получим ее позицию 9.
Следующие 2 будут найдены. Поскольку это часть исходной строки, мы получаем ее индекс как 6.
Точно так же каждый элемент находится в некоторой позиции.
-
Теперь этот массив передается в
link: / statistics-excel-min-function [MIN function]как MIN (\ {8,9,6,11,12,13,14,7,16,17}). МИН возвращает 6 — позицию первого числа в исходном тексте.
И история после этого довольно проста. Мы используем этот номер для извлечения текста и чисел с помощью функций ВЛЕВО и ВПРАВО.
Извлечь число и текст из строки, когда число находится в начале строки. В приведенном выше примере число было в конце строки. Как извлечь число и текст, если число находится в начале.
Я подготовил аналогичную таблицу, как указано выше. У него просто номер в начале.
Здесь мы воспользуемся другой техникой. Мы посчитаем длину чисел (здесь 2) и извлечем это количество символов слева от String.
Таким образом, метод = LEFT (строка, количество чисел)
Для подсчета количества символов это формула.
Общая формула для подсчета числа чисел:
=SUM(LEN(string)-LEN(SUBSTITUTE(string,{"0","1","2","3","4","5","6","7","8","9"},""))
Здесь ** link: / excel-text-formulas-excel-substitute-function [SUBSTITUTE function] заменит каждое найденное число на «» (пробел). Если число найдено, на замену и в массив будет добавлена новая строка, в массив будет добавлена другая исходная строка. Таким образом, у нас будет массив из 10 строк.
Теперь функция LEN будет возвращать длину символов в массиве этих строк.
Затем из длины исходных строк мы вычтем длину каждой строки, возвращаемой функцией SUBSTITUTE. Это снова вернет массив.
Теперь SUM сложит все эти числа. Это количество чисел в строке.
Извлечь числовую часть из строки
Теперь, когда мы знаем длину чисел в строке, мы заменим эту функцию на LEFT.
Поскольку у нас есть строка A11, наша:
Формула для извлечения чисел слева
=LEFT(A11,SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))))
Извлечь текстовую часть из строки
Поскольку мы знаем количество чисел, мы можем вычесть его из общей длины строки, чтобы получить числовые алфавиты в строке, а затем использовать правую функцию для извлечения этого количества символов справа от строки.
Формула для извлечения текста из СПРАВА
=RIGHT(A11,LEN(A2)-SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))))
Как это работает
Основная часть в обеих формулах — СУММ (LEN (A11) -LEN (SUBSTITUTE (A11, \ {«0», «1», «2», «3», «4», «5», «6», «7», «8», «9»}, «»)))
который вычисляет первое появление числа. Только обнаружив это, мы можем разделить текст и число с помощью функции LEFT. Итак, давайте разберемся в этом.
-
ЗАМЕНА (A11, \ {«0», «1», «2», «3», «4», «5», «6», «7», «8», «9»}, » «):
Эта часть возвращает массив строк в A11 после замены этих чисел ничем / пробелом («»). Для 27Monta он вернет \ {«27Monta», «27Monta», «7Monta», «27Monta», «27Monta», «27Monta», «27Monta», «2Monta», «27Monta», «27Monta»}.
LEN (SUBSTITUTE (A11, \ {«0», «1», «2», «3», «4», «5», «6», «7», «8», «9»}, » «)):
Теперь часть ЗАМЕНА обернута функцией LEN. Это возвращаемая длина текстов в массиве, возвращенная функцией ЗАМЕНА. В результате у нас будет \ {7,7,6,7,7,7,7,6,7,7}.
LEN (A11) -LEN (SUBSTITUTE (A11, \ {«0», «1», «2», «3», «4», «5», «6», «7», «8», » 9 «},» «)):
Здесь мы вычитаем каждое число, возвращаемое вышеуказанной частью, из длины фактической строки. Длина исходного текста — 7. Следовательно, у нас будет \ {7-7,7-7,7-6, ….}. В итоге у нас будет \ {0,0,1,0,0,0,0,1,0,0}.
СУММ (LEN (A11) -LEN (ПОДСТАВИТЬ (A11, \ {«0», «1», «2», «3», «4», «5», «6», «7», «8» , «9»}, «»))):
Здесь мы использовали СУММ для суммирования массива, возвращаемого вышеуказанной частью функции.
Это даст 2. Это количество чисел в строке.
Теперь, используя это, мы можем извлекать тексты и числа и разбивать их по разным ячейкам. Этот метод будет работать как с типом текста, когда число находится в начале, так и когда оно находится в конце. Вам просто нужно правильно использовать функции LEFT и RIGHT.
Используйте функцию SplitNumText для разделения чисел и текстов из строки
Вышеупомянутые методы немного сложны и бесполезны, когда текст и числа смешаны. Для разделения текста и чисел используйте эту пользовательскую функцию.
Синтаксис:
=SplitNumText(string, op)
Строка: строка, которую вы хотите разделить.
Op: это логическое значение. Передайте 0 или false, чтобы получить текстовую часть. Для части числа передайте истину или любое число больше 0.
Например, если строка находится в A20, то
Формула для извлечения чисел из строки:
=SplitNumText(A20,1)
И
Формула извлечения текста из строки:
=SplitNumText(A20,0)
Скопируйте приведенный ниже код в модуль VBA, чтобы вышеуказанная формула работала.
Function SplitNumText(str As String, op As Boolean) num = "" txt = "" For i = 1 To Len(str) If IsNumeric(Mid(str, i, 1)) Then num = num & Mid(str, i, 1) Else txt = txt & Mid(str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Function
Этот код просто проверяет каждый символ в строке, если это число или нет. Если это число, то оно сохраняется в переменной num, иначе в переменной txt. Если пользователь передает true для op, то возвращается num, иначе возвращается txt.
На мой взгляд, это лучший способ отделить число и текст от строки.
Вы можете скачать книгу здесь, если хотите.
Так что да, ребята, это способы разделить текст и числа в разных ячейках. Дайте мне знать, если у вас есть какие-либо сомнения или лучшее решение в разделе комментариев ниже. Всегда весело общаться с парнями.
Щелкните ссылку ниже, чтобы загрузить рабочий файл:
`link: /wp-content-uploads-2019-11-Split-Number-and-Text-from-A-Cell.xls [__ Разделить номер и текст из ячейки]
Статьи по теме:
link: / mail-send-and-receive-in-vba-how-to-extract-domain-name-from-email-in-excel [Как извлечь доменное имя из электронной почты в Excel]
link: / excel-text-formulas-split-numbers-and-text-from-string-in-excel [Разделить числа и текст из строки в Excel]
Популярные статьи:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-Повышение-продуктивность [50 ярлыков Excel для повышения вашей производительности]
link: / формулы-и-функции-введение-функции vlookup [Функция ВПР в Excel]
link: / tips-countif-in-microsoft-excel [СЧЁТЕСЛИ в Excel 2016]
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]