Како раздвојити бројеве и текст из низа у Екцелу

Много пута добијам мешовите податке са терена и сервера за анализу. Ови подаци су обично прљави, са колоном помешаном са бројем и текстом. Чишћење података пре анализе одвајам бројеве и текст у засебне колоне. У овом чланку ћу вам рећи како то можете учинити.

Сценариј:
Тако је један наш пријатељ на Екцелтип.цом поставио ово питање у одељку за коментаре. „Како да одвојим бројеве који долазе пре текста и на крају текста помоћу Екцел формуле. На пример 125ЕвенуеСтреет и ЛовеИоу3000 итд. ”

За издвајање текста користимо РИГХТ, ЛЕФТ, МИД и друге функције текста. Морамо само знати број текстова за издвајање. И овде ћемо прво учинити исто.
Издвојите број и текст из низа када је број на крају низа
За горњи пример припремио сам овај лист. У ћелији А2 имам стринг. У ћелији Б2 желим текстуални део, а у Ц2 бројчани део.

Зато само треба да знамо позицију одакле почиње број. Затим ћемо користити Лефт и друге функције. Дакле, да бисмо добили позицију првог броја користимо доњу генеричку формулу:
Општа формула за добијање позиције првог броја у низу:

= МИН (ПРЕТРАГА ({0,1,2,3,4,5,6,7,8,9}, Стринг_Реф & "0123456789")

Ово ће вратити позицију првог броја.
За горњи пример упишите ову формулу у било коју ћелију.

= МИН (ПРЕТРАГА ({0,1,2,3,4,5,6,7,8,9}, А5 & "0123456789"))

Део издвајања текста

Вратиће 15 јер је први пронађени број на 15. позицији у тексту. Објаснићу то касније.

Сада, да бисмо добили Тект, са леве стране морамо само да добијемо 15-1 знак из низа. Зато ћемо користити
ЛЕФТ функција за издвајање текста.
Формула за издвајање текста са леве стране

= ЛИЈЕВО (А5, МИН (ПРЕТРАГА ({0,1,2,3,4,5,6,7,8,9}, А5 & "0123456789"))-1)

Овде смо управо одузели 1 од било ког броја који је вратио МИН (СЕАРЦХ ({0,1,2,3,4,5,6,7,8,9}, А5 & "0123456789")).
Дио извода

Сада да бисмо добили бројеве само морамо да добијемо бројеве од првог пронађеног броја. Тако израчунавамо укупну дужину низ и одузети позицију првог пронађеног броја и додати 1 на то. Једноставно. Да, само звучи сложено, једноставно је.
Формула за издвајање бројева са десне стране

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

Овде смо управо добили укупну дужину низа помоћу функције ЛЕН, а затим смо одузели позицију прво пронађеног броја, а затим му додали 1. Ово нам даје укупан број бројева. Овде сазнајте више о издвајању текста помоћу функција ЛЕВО и ДЕСНО у Екцелу.

Дакле, део функције ЛЕВО и ДЕСНО је једноставан. Лукав део је МИН и СЕАРЦХ део који нам даје позицију првог пронађеног броја. Схватимо то.
Како то ради
Знамо како функционира функција ЛИЈЕВО и ДЕСНО. Истражићемо главни део ове формуле који добија позицију првог пронађеног броја, а то је: МИН (ПРЕТРАГА ({0,1,2,3,4,5,6,7,8,9}, Стринг & "0123456789 ")
СЕАРЦХ функција враћа позицију текста у низу. СЕАРЦХ (‘тект’, ‘стринг’) функција узима два аргумента, прво текст који желите да претражујете, други низ у коме желите да тражите.

- Овде у СЕАРЦХ -у на позицији текста имамо низ бројева од 0 до 9. А на позицији низа имамо низ који је спојен са "0123456789" користећи & оператер. Зашто? Ја ћу ти рећи.
- Сваки елемент у низу {0,1,2,3,4,5,6,7,8,9} биће претраживан у датом низу и вратиће своју позицију у низу у облику низа са истим индексом у низу.
- Ако ниједна вредност није пронађена, то ће изазвати грешку. Стога ће све формуле довести до грешке. Да бисмо то избегли, спојили смо бројеве „0123456789“ у тексту. Тако да увек пронађе сваки број у низу. Ови бројеви су на крају стога неће изазвати никакве проблеме.
- Сада функција МИН враћа најмању вредност из низа коју враћа функција СЕАРЦХ. Ова најмања вредност биће први број у низу. Користећи ову функцију БРОЈ и ЛИЈЕВО и ДЕСНО можемо подијелити дијелове текста и низа.

Погледајмо наш пример. У А5 имамо низ који има назив улице и кућни број. Морамо их раздвојити у различите ћелије.
Прво да видимо како смо добили наш први положај у низу.

- МИН (СЕАРЦХ ({0,1,2,3,4,5,6,7,8,9}, А5 & "0123456789")): ово ће се превести у МИН (СЕАРЦХ ({0,1,2,3, 4,5,6,7,8,9}, ”Монта270123456789”))

Као што сам објаснио, претрага ће претраживати сваки број у низу {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.
Слично, сваки елемент се налази на некој позицији.

- Сада је овај низ прослеђен функцији МИН као МИН ({8,9,6,11,12,13,14,7,16,17}). МИН враћа 6 која је позиција првог броја пронађеног у оригиналном тексту.
  А прича после овога је прилично једноставна. Користимо овај број за екстракцију текста и бројева помоћу функција ЛИЈЕВО и ДЕСНО.

Издвајање броја и текста из низа када је број на почетку низа
У горњем примеру, Нумбер је био на крају низа. Како да извучемо број и текст када је број на почетку.

Припремио сам сличну табелу као горе. Има само број на почетку.

Овде ћемо користити другачију технику. Бројаћемо дужину бројева (што је 2 овде) и извући ћемо тај број знакова са леве стране низа.
Дакле, метода је = ЛЕФТ (низ, број бројева)
За израчунавање броја знакова ово је формула.
Општа формула за бројање бројева:

= СУМ (ЛЕН (стринг) -ЛЕН (СУБСТИТУТЕ (стринг, {"0", "1", "2", "3", "4", "5", "6", "7", "8") , "9"}, ""))

Овде,

- - СУБСТИТУТЕ функција ће сваки пронађени број замијенити са “” (празно). Ако се пронађе број који је замењен и нови низ ће бити додат у низ, други мудри оригинални низ ће бити додат у низ. На овај начин ћемо имати низ од 10 низова.
  - Сада ће функција ЛЕН вратити дужину знакова у низу тих низова.
  - Затим ћемо од дужине оригиналних низова одузети дужину сваког низа враћеног функцијом СУБСТИТУТЕ. Ово ће поново вратити низ.
  - Сада ће СУМ додати све ове бројеве. Ово је бројање бројева у низу.

Издвоји део броја из низа

Пошто знамо дужину бројева у низ, заменићемо ову функцију у ЛЕВО.
Пошто имамо наш низ А11 наш:

Формула за издвајање бројева из леве стране

= ЛИЈЕВО (А11, СУМ (ЛЕН (А11) -ЛЕН (ЗАМЈЕНА (А11, {"0", "1", "2", "3", "4", "5", "6", "7" , "8", "9"}, ""))))

Издвоји део текста из низа

Пошто знамо број бројева, можемо га одузети од укупне дужине низа да бисмо добили нумеричке абецеде у низу, а затим помоћу десне функције извући тај број знакова с десне стране низа.

Формула за издвајање текста из ДЕСНО

= ДЕСНО (А11, ЛЕН (А2) -СУМ (ЛЕН (А11) -ЛЕН (ЗАМЕНА (А11, {"0", "1", "2", "3", "4", "5", "6 "," 7 "," 8 "," 9 "}," "))))

Како то ради
Главни део обе формуле је СУМ (ЛЕН (А11) -ЛЕН (ЗАМЕНА (А11, {"0", "1", "2", "3", "4", "5", "6", " 7 "," 8 "," 9 "}," "))) која израчунава прво појављивање броја. Тек након што ово пронађемо, можемо подијелити текст и број помоћу функције ЛИЈЕВО. Па хајде да разумемо ово.

- - ЗАМЕНА (А11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""): Овај део враћа низ стрингова у А11 након што ове бројеве замените ничим/празним (“”). За 27Монта вратиће {"27Монта", "27Монта", "7Монта", "27Монта", "27Монта", "27Монта", "27Монта", "2Монта", "27Монта", "27Монта"}.
  - ЛЕН (ЗАМЕНА (А11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, "" )): Сада је део СУБСТИТУТЕ замотан помоћу функције ЛЕН. Ова дужина враћања текстова у низу враћена функцијом СУБСТИТУТЕ. Као резултат, имаћемо {7,7,6,7,7,7,7,6,7,7}.
  - ЛЕН (А11) -ЛЕН (ЗАМЕНА (А11, {"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}.
  - СУМ (ЛЕН (А11) -ЛЕН (ЗАМЕНА (А11, {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, ""))): Овде смо користили СУМ за сумирање низа враћеног горњим делом функције. Ово ће дати 2. Што је број бројева у низу.

Сада помоћу овога можемо издвојити текстове и број и подијелити их у различите ћелије. Ова метода ће радити са обе врсте текста, када је број на почетку и када је на крају. Потребно је само да на одговарајући начин користите функције ЛЕФТ и РИГХТ.
За раздвајање бројева и текстова из низа користите функцију СплитНумТект
Горе наведене методе су мало сложене и нису корисне када се помешају текст и бројеви. За подјелу текста и бројева користите ову кориснички дефинирану функцију.

Синтакса:

= СплитНумТект (стринг, оп)

Низ: Низ који желите да поделите.
Оп: ово је боолеан. Прођите 0 или лажно да бисте добили део текста. За део броја, положите истина или било који број већи од 0.
На пример, ако је низ у А20,
Формула за издвајање бројева из низа је:

= СплитНумТект (А20,1)

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

= СплитНумТект (А20,0)

Копирајте доњи код у ВБА модул да би горња формула радила.

Функција СплитНумТект (стр Ас Стринг, оп Ас Боолеан) нум = "" ткт = "" Фор и = 1 То Лен (стр) Иф ИсНумериц (Мид (стр, и, 1)) тхен нум = нум & Мид (стр, и , 1) Остало ткт = ткт & Мид (стр, и, 1) Енд Иф Нект и Иф оп = Труе тхен СплитНумТект = нум Елсе СплитНумТект = ткт Енд Иф Енд Функција

Овај код једноставно проверава сваки знак у низу, ако је његов број или не. Ако је то број, онда се чува у нум променљивој елсе у променљивој ткт. Ако корисник пренесе труе за оп онда се враћа нум иначе се враћа ткт.

Ово је најбољи начин за раздвајање броја и текста из низа по мом мишљењу.
Радну свеску можете преузети овде ако желите.

Па да, момци, ово су начини да поделите текст и бројеве у различите ћелије. Јавите ми ако имате било каквих недоумица или бољег решења у одељку за коментаре испод. Увек је забавно комуницирати са момцима.

Кликните на доњу везу за преузимање радне датотеке:

Поделите број и текст из ћелије

Популарни чланци:
50 Екцел пречица за повећање продуктивности
Функција ВЛООКУП у Екцелу
ЦОУНТИФ у програму Екцел 2016
Како се користи функција СУМИФ у програму Екцел