Научили смо како да уклонимо нумеричке вредности из ћелије у Екцелу 2016 и старијим. Формуле које смо користили биле су мало сложене, али сада су Екцел 2019 и 365 у игри.
Екцел 2019 и 365 уводе неке нове функције које олакшавају уклањање не нумеричких знакова и дохваћање само нумеричких вредности у новој ћелији. Користићемо формуле које нам у томе могу помоћи, погодније.
Општа формула
| =ТЕКСТЈОИН("",ИСТИНА,ИФЕРРОР(МИД(јумбле_тект,НИЗ(НумЦхарс), 1) +0, ""))) |
Јумблед_тект: Ово је изворни текст из којег желите издвојити све нумеричке вредности.
НумЦхарс: Ово је укупан број знакова које желите да обрадите. Јублед_тект не би требало да има више знакова од овог броја (знакови и нумерички заједно).
Погледајмо пример како бисмо разјаснили ствари.
Пример: Уклоните не нумеричке знакове и извуците све бројеве
Дакле, овде имамо помешан текст. Овај текст садржи неке бројеве и неке не нумеричке знакове. Морам да се решим не нумеричких знакова и добијем нумеричке вредности само у колони Д.
Не очекујем да ће укупан број знакова у збрканом тексту бити већи од 20. Дакле, вредност НумЦхарс -а је 20 овде. Овај број можете повећати ако је потребно.
Овде примените горњу генеричку формулу да бисте уклонили не нумеричке знакове.
| =ТЕКСТЈОИН("",ИСТИНА,ИФЕРРОР(МИД(Ц3,НИЗ(20),1)+0,"")) |
А када притиснете дугме за унос. Уклонићете све не нумеричке знакове. Превуците ову формулу надоле да бисте уклонили знакове из низа из свих ћелија у колони Ц3.
Како то функционише?
Прво да видимо како се ова формула решава корак по корак.
| 1-> ТЕКСТЈОИН("",ИСТИНА,ИФЕРРОР(МИД(Ц3,НИЗ(20),1)+0,"")) |
| 2-> ТЕКСТЈОИН("",ИСТИНА,ИФЕРРОР(МИД("12асв12в123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> ТЕКСТЈОИН("",ИСТИНА,ИФЕРРОР({"1"; "2"; "а"; "с"; "в"; "1"; "2"; "в"; "1"; "2"; "3"; ""; "" ; ""; ""; ""; ""; ""; ""; ""}}+0,"")) |
| 4-> ТЕКСТЈОИН("",ИСТИНА,ИФЕРРОР({1; 2; #ВАЛУЕ!;#ВАЛУЕ!;#ВАЛУЕ!; 1; 2; #ВАЛУЕ!; 1; 2; 3; #ВАЛУЕ!;#ВАЛУЕ!;…; #ВАЛУЕ!}+0,"")) |
| 5-> ТЕКСТЈОИН("",ИСТИНА,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Као што видите, формула почиње да се решава изнутра. Прво је решена функција СЕКУЕНЦЕ. Пошто смо прошли 20. Враћа низ бројева који почињу од 1 до 20.
Овај низ се послужује функцији МИД као почетни број. Средња функција иде до сваког индекса у низу и дели сваки знак. То можете видети у кораку 3.
Затим сваком знаку додајемо 0. У Екцелу ако покушате да додате број не -нумеричким знаковима, резултат је #ВАЛУЕ! Грешка. Тако добијамо низ бројева и #ВАЛУЕ! Грешке. Нумерички знакови су сада нестали.
Следећа функција ИФЕРРОР замењује све грешке #ВАЛУЕ са "" (празно). Сада нам преостају нумеричке вредности и размаци.
Коначно, овај низ се сервира функцији ТЕКСТЈОИН. Функција ТЕКСТЈОИН их повезује и добијамо низ који садржи само бројеве.
Побољшање формуле
Горња формула је користила тешко кодиран број за обраду броја знакова (узели смо двадесет). Али можда бисте желели да буде динамичан. У том случају, погађате, користићемо функцију ЛЕН. За обраду ће бити потребан тачан број знакова. Дакле, формула ће бити.
| = ТЕКСТЈОИН ("", ТРУЕ, ИФЕРРОР (МИД (јумбле_тект, СЕКУЕНЦЕ (ЛЕН(јумбле_тект)),1)+0,"")) |
Овде ће функција ЛЕН аутоматски открити тачан број знакова у алфанумеричком низу. Ово ће растеретити одређивање максималног броја знакова.
Алтернатива функције СЕКУЕНЦЕ
Ако не желите користити функцију СЕКУЕНЦЕ, тада можете користити комбинацију РОВ и ИНДИРЕЦТ функције за генерирање секвенцијалних бројева.
| = ТЕКСТЈОИН ("", ТРУЕ, ИФЕРРОР (МИД (јумбле_тект,РОВ(ИНДИРЕКТАН("1:"&ЛЕН(јумбле_тект))),1)+0,"")) |
ИНДИРЕЦТ ће претворити текст ("1:20") у стварни распон, а затим ће функција РОВ исписати све бројеве редова од 1 до 20. (20 је само на примјер. Може бити било који број).
Па да, момци, овако можете у Екцел -у да откинете не -нумеричке знакове из алфанумеричког низа. Надам се да сам вам довољно објаснио и да вам је овај чланак помогао. Ако имате питања у вези ове теме или било које друге екцел/ВБА теме. До тада наставите са Екцеллингом.
Поделите бројеве и текст из низа у програму Екцел 2016 и старијим: Када нисмо имали функцију ТЕКСТЈОИН, користили смо функције ЛЕФТ и РИГХТ са неким другим функцијама за раздвајање нумеричких и не нумеричких знакова из низа.
Издвојите текст из низа у Екцелу помоћу Екцел -ове функције ЛИЈЕВО И ДЕСНО: За уклањање текста у екцелу из низа можемо користити екцелове функције ЛЕФТ и РИГХТ. Ове функције нам помажу да динамички сечемо низове.
Уклоните почетне и крајње размаке из текста у програму Екцел: Водећи и задњи простор је тешко визуелно препознати и могу пореметити ваше податке. Скидање ових знакова из низа је основни и најважнији задатак у чишћењу података. Ево како то можете учинити лако у Екцелу.
Уклоните знакове са десне стране: За уклањање знакова с десне стране низа у Екцелу користимо функцију ЛИЈЕВО. Да, функција ЛЕФТ. Функција ЛЕФТ задржава задати број знакова са ЛЕФТ и уклања све са њене десне стране.
Уклоните нежељене знакове у програму Екцел: Да бисмо уклонили нежељене знакове из низа у Екцелу, користимо функцију ЗАМЕНА. Функција СУБСТИТУТЕ замењује дате знакове другим датим знаком и производи нови измењени низ.
Како уклонити текст у Екцелу почевши од позиције у Екцелу: За уклањање текста са почетне позиције у низу користимо функцију РЕПЛАЦЕ у Екцелу. Ова функција нам помаже да одредимо почетну позицију и број знакова за скидање.
Популарни чланци:
50 Екцел пречица за повећање продуктивности | Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.
Како се користи Екцел ВЛООКУП функција| Ово је једна од најчешће кориштених и популарних функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи Екцел ЦОУНТИФ функција| Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел | Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.