Как да разделяте числа и текст от низ в Excel

Много пъти получавам смесени данни от полето и сървъра за анализ. Тези данни обикновено са мръсни, като колоната е смесена с номер и текст. Докато извършвам почистване на данни преди анализ, разделям числата и текста в отделни колони. В тази статия ще ви кажа как можете да го направите.

Сценарий:
Така че един наш приятел от Exceltip.com зададе този въпрос в секцията за коментари. „Как да разделя числа, идващи преди текст и в края на текста, използвайки Excel формула. Например 125EvenueStreet и LoveYou3000 и т.н.

За извличане на текст използваме RIGHT, LEFT, MID и други текстови функции. Просто трябва да знаем броя на текстовете, които да извлечем. И тук първо ще направим същото.
Извличане на номер и текст от низ, когато числото е в края на низа
За горния пример подготвих този лист. В клетка А2 имам низ. В клетка В2 искам текстовата част, а в С2 цифровата част.

Така че просто трябва да знаем позицията, от която започва числото. След това ще използваме ляво и друга функция. Така че, за да получим позиция на първо число, използваме по -долу общата формула:
Обща формула за получаване на позиция на първо число в низ:

= MIN (ТЪРСЕНЕ ({0,1,2,3,4,5,6,7,8,9}, String_Ref & "0123456789")

Това ще върне позицията на първото число.
За горния пример напишете тази формула във всяка клетка.

= MIN (ТЪРСЕНЕ ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))

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

Той ще върне 15, тъй като първото намерено число е на 15 -та позиция в Text. Ще го обясня по -късно.

Сега, за да получим текст, отляво просто трябва да вземем 15-1 символ от низ. Така че ще използваме
НАЛЯВО функция за извличане на текст.
Формула за извличане на текст отляво

= НАЛЯВО (A5, MIN (ТЪРСЕНЕ ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789"))-1)

Тук току -що извадихме 1 от всяко число, върнато от MIN (SEARCH ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")).
Част за извличане на номер

Сега, за да получим числа, просто трябва да вземем числови знаци от първото намерено число. Така че изчисляваме общата дължина на низ и извадете позицията на първото намерено число и добавете 1 към него. Прост. Да, просто звучи сложно, просто е.
Формула за извличане на числа отдясно

= НАДЯСНО (A5, LEN (A5) -МИН (ТЪРСЕНЕ ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")+1)

Тук току -що получихме обща дължина на низ, използвайки функцията LEN и след това извадихме позицията на първо намерено число и след това добавихме 1 към него. Това ни дава общ брой числа. Научете повече тук за извличането на текст с помощта на функциите НАЛЯВО и НАДЯСНО в Excel.

Така че функционалната част НАЛЯВО и НАДЯСНО е проста. Сложната част е MIN и SEARCH Част, която ни дава позицията на първото намерено число. Нека го разберем.
Как работи
Знаем как работят функцията LEFT и RIGHT. Ще изследваме основната част от тази формула, която получава позицията на първото намерено число и това е: MIN (ТЪРСЕНЕ ({0,1,2,3,4,5,6,7,8,9}, String & "0123456789 ")
Функцията SEARCH връща позицията на текст в низ. Функцията SEARCH („текст“, „низ“) приема два аргумента, първо текстът, който искате да търсите, втори низ, в който искате да търсите.

- Тук, в SEARCH, на позиция текст имаме масив от числа от 0 до 9. А на позиция на низ имаме низ, който е свързан с "0123456789", използвайки & оператор. Защо? Аз ще ти кажа.
- Всеки елемент в масива {0,1,2,3,4,5,6,7,8,9} ще бъде търсен в даден низ и ще върне позицията си в низ от формата на масив при същия индекс в масива.
- Ако не се намери някаква стойност, това ще доведе до грешка. Следователно цялата формула ще доведе до грешка. За да избегнем това, сме свързали числата „0123456789“ в текст. Така че винаги намира всяко число в низ. Тези цифри са в крайна сметка следователно няма да създадат проблеми.
- Сега функцията MIN връща най -малката стойност от масива, върната от функцията SEARCH. Тази най -малка стойност ще бъде първото число в низ. Сега, използвайки тази функция NUMBER и LEFT и RIGHT, можем да разделим текста и низовите части.

Нека разгледаме нашия пример. В A5 имаме низ, който има име на улица и номер на къща. Трябва да ги разделим в различни клетки.
Първо нека видим как получихме позицията си на първо число в низ.

- MIN (ТЪРСЕНЕ ({0,1,2,3,4,5,6,7,8,9}, A5 & "0123456789")): това ще се преведе в MIN (ТЪРСЕНЕ ({0,1,2,3, 4,5,6,7,8,9}, ”Monta270123456789”))

Сега, както обясних, търсенето ще търси всяко число в масив {0,1,2,3,4,5,6,7,8,9} в Монта270123456789 и ще върне позицията си под формата на масив. Върнатият масив ще бъде {8,9,6,11,12,13,14,7,16,17}. Как?
0 ще се търси в низ. Намира се на 8 позиция. Следователно първият ни елемент е 8. Обърнете внимание, че оригиналният ни текст е с дължина само 7 знака. Вземи го. 0 не е част от Монта27.
Следващата 1 ще се търси в низ и също не е част от оригиналния низ и получаваме позиция 9.
Следващите 2 ще бъдат търсени. Тъй като е част от оригиналния низ, получаваме неговия индекс като 6.
По същия начин всеки елемент се намира на определена позиция.

- Сега този масив се предава на MIN функция като MIN ({8,9,6,11,12,13,14,7,16,17}). MIN връща 6, което е позицията на първото число, намерено в оригиналния текст.
  А историята след това е съвсем проста. Използваме този номер за извличане на текст и числа, като използваме функцията НАЛЯВО и НАДЯСНО.

Извличане на номер и текст от низ, когато числото е в началото на низа
В горния пример числото е в края на низа. Как да извлечем номер и текст, когато числото е в началото.

Подготвил съм подобна таблица както по -горе. Той просто има номер в началото.

Тук ще използваме различна техника. Ще преброим дължината на числата (която е 2 тук) и ще извлечем този брой знаци отляво на низа.
Така че методът е = LEFT (низ, брой на числата)
За да преброите броя знаци, това е формулата.
Обща формула за броене на числа:

= SUM (LEN (низ) -LEN (SUBSTITUTE (низ, {"0", "1", "2", "3", "4", "5", "6", "7", "8") , "9"}, ""))

Тук,

- - Функцията SUBSTITUTE ще замени всяко намерено число с “” (празно). Ако бъде намерено число, заместено и нов низ ще бъде добавен към масива, други мъдри оригинални низ ще бъдат добавени към масива. По този начин ще имаме масив от 10 низа.
  - Сега функцията LEN ще върне дължината на знаците в масив от тези низове.
  - След това от дължината на оригиналните низове ще извадим дължината на всеки низ, върнат от функцията SUBSTITUTE. Това отново ще върне масив.
  - Сега SUM ще добави всички тези числа. Това е броят на числата в низ.

Извличане на част номер от низ

Сега, тъй като знаем дължината на числата в низ, ще заменим тази функция в ляво.
Тъй като имаме нашия низ A11 наш:

Формула за извличане на числа от ЛЯВО

= НАЛЯВО (A11, SUM (LEN (A11) -LEN (ЗАМЕСТНИК (A11, {"0", "1", "2", "3", "4", "5", "6", "7" , "8", "9"}, ""))))

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

Тъй като знаем броя на числата, можем да го извадим от общата дължина на низ, за да получим числови азбуки в низ и след това да използваме дясната функция, за да извлечем този брой знаци отдясно на низ.

Формула за извличане на текст от RIGHT

= НАДЯСНО (A11, LEN (A2) -SUM (LEN (A11) -LEN (ЗАМЕСТНИК (A11, {"0", "1", "2", "3", "4", "5", "6 "," 7 "," 8 "," 9 "}," "))))

Как работи
Основната част и в двете формули е SUM (LEN (A11) -LEN (SUBSTITUTE (A11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))), която изчислява първата поява на число. Едва след като намерим това, можем да разделим текст и номер, използвайки функцията НАЛЯВО. Така че нека разберем това.

- - ЗАМЕСТИТЕЛ (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Тази част връща масив от низ в A11, след като замества тези числа с нищо/празно (“”). За 27Монта ще върне {"27Monta", "27Monta", "7Monta", "27Monta", "27Monta", "27Monta", "27Monta", "2Monta", "27Monta", "27Monta"}.
  - ОБЕКТ )): Сега SUBSTITUTE частта е обвита от LEN функция. Тази дължина на връщане на текстове в масив, върната от функцията SUBSTITUTE. В резултат ще имаме {7,7,6,7,7,7,7,6,7,7}.
  - LEN (A11) -LEN (ЗАМЕСТНИК (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}.
  - SUM (LEN (A11) -LEN (ЗАМЕСТНИК (A11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Тук използвахме SUM за сумиране на масива, върнат от горната част на функцията. Това ще даде 2. Кой е броят на числата в низ.

Сега, използвайки това, можем да извлечем текстовете и номера и да ги разделим в различни клетки. Този метод ще работи както с текст от тип, когато числото е в началото и когато е в края. Просто трябва да използвате функцията НАЛЯВО и НАДЯСНО по подходящ начин.
Използвайте функцията SplitNumText за разделяне на числа и текстове от низ
Горните методи са малко сложни и не са полезни при смесване на текст и числа. За разделяне на текст и числа използвайте тази дефинирана от потребителя функция.

Синтаксис:

= SplitNumText (низ, оп)

Низ: Низът, който искате да разделите.
Оп: това е логическо. Преминете 0 или невярно за да получите текстова част. За числова част, преминете вярно или произволно число, по -голямо от 0.
Например, ако низът е в A20, тогава,
Формулата за извличане на числа от низ е:

= SplitNumText (A20,1)

И
Формулата за извличане на текст от низ е:

= SplitNumText (A20,0)

Копирайте кода по -долу в модула VBA, за да работи горната формула.

Функция SplitNumText (str As String, op As Boolean) num = "" txt = "" For i = 1 To Len (str) If IsNumeric (Mid (str, i, 1)) Тогава num = num & Mid (str, i , 1) В противен случай txt = txt & Mid (str, i, 1) End If Next i If op = True then SplitNumText = num Else SplitNumText = txt End If End Функция

Този код просто проверява всеки знак в низ, ако е негов номер или не. Ако това е число, то се съхранява в num променлива else в txt променлива. Ако потребителят предаде true за op, тогава се връща num, в противен случай се връща txt.

Това е най -добрият начин за разделяне на номер и текст от низ според мен.
Можете да изтеглите работната книга тук, ако искате.

Така че да, момчета, това са начините за разделяне на текст и числа в различни клетки. Кажете ми, ако имате някакви съмнения или по -добро решение в секцията за коментари по -долу. Винаги е забавно да общуваш с момчета.

Щракнете върху връзката по -долу, за да изтеглите работния файл:

Разделяне на номер и текст от клетка

Популярни статии:
50 преки пътища на Excel за повишаване на вашата производителност
Функцията VLOOKUP в Excel
COUNTIF в Excel 2016
Как да използвате функцията SUMIF в Excel