Када радите са текстуалним подацима у програму Екцел, можда ћете морати да уклоните текст пре или после одређеног знака или текстуалног низа.
На пример, ако имате име и податке о означавању људи, можда ћете желети да уклоните ознаку иза зареза и задржите само име (или обрнуто ако задржите ознаку и уклоните име).
Понекад се то може урадити једноставном формулом или брзим проналажењем и заменом, а понекад су потребне сложеније формуле или решења.
У овом водичу ћу вам показати како уклонити текст пре или после одређеног знака у Екцелу (користећи различите примере).
Па почнимо са неколико једноставних примера.
Уклоните текст након знака помоћу функције Пронађи и замени
Ако желите брзо уклонити сав текст након одређеног текстуалног низа (или пре текстуалног низа), то можете учинити помоћу функције Пронађи и замени и џокер знакова.
Претпоставимо да имате скуп података као што је приказано испод и желите да уклоните ознаку иза знака зареза и задржите текст испред зареза.
У наставку су наведени кораци за то:
- Копирајте и залепите податке из колоне А у колону Б (ово служи и за чување оригиналних података)
- Са изабраним ћелијама у колони Б, кликните на картицу Почетна
- У групи Уређивање кликните на опцију Пронађи и изабери
- У опцијама које се појављују у падајућем менију кликните на опцију Замени. Ово ће отворити дијалог Финд анд Реплаце
- У поље „Пронађи шта“ унесите ,* (тј. зарез праћен знаком звездице)
- Оставите поље „Замени са“ празно
- Кликните на дугме Замени све
Горе наведени кораци би пронашли зарез у скупу података и уклонили сав текст иза зареза (укључујући и зарез).
Пошто ово замењује текст из изабраних ћелија, добра је идеја да копирате текст у другу колону, а затим извршите ову операцију проналажења и замене, или направите резервну копију података тако да оригинални подаци буду нетакнути
Како ово ради?
* (знак звездице) је заменски знак који може представљати било који број знакова.
Када га користим после зареза (у пољу „Пронађи шта“), а затим кликнем на дугме Замени све, он проналази први зарез у ћелији и сматра га подударањем.
То је зато што се знак звездица (*) сматра подударањем са читавим низом текста који следи зарез.
Дакле, када притиснете дугме замени све, оно замењује зарез и сав текст који следи.
Белешка: Ова метода добро функционише, па у свакој ћелији имате само један зарез (као у нашем примеру). У случају да имате више зареза, овај метод би увек пронашао прву запету, а затим уклонио све иза ње. Дакле, не можете користити овај метод ако желите да замените сав текст после другог зареза и задржите први такав какав јесте.У случају да желите да уклоните све знакове испред зареза, промените унос поља Финд вхат тако што ћете испред зареза ставити знак звездице (*, уместо,*)
Уклоните текст помоћу формула
Ако вам је потребна већа контрола како пронаћи и заменити текст пре или после одређеног знака, боље је да користите уграђене текстуалне формуле у Екцелу.
Претпоставимо да имате доњи скуп података Где желите да уклоните сав текст иза зареза.
Испод је формула за то:
= ЛИЈЕВО (А2, ФИНД (",", А2) -1)
Горња формула користи функцију ФИНД за проналажење положаја зареза у ћелији.
Овај број позиције затим користи функција ЛЕФТ за издвајање свих знакова испред зареза. Пошто не желим зарез као део резултата, одузео сам 1 од резултујуће вредности формуле Финд.
Ово је био једноставан сценарио.
Узмимо једну мало сложенију.
Претпоставимо да имам следећи скуп података у коме желим да уклоним сав текст после другог зареза.
Ево формуле која ће то учинити:
= ЛИЈЕВО (А2, ФИНД ("!", ЗАМЈЕНА (А2, ",", "!", 2))-1)
Пошто овај скуп података има више зареза, не могу користити функцију ФИНД да добијем позицију прве зарезе и издвојим све лево од ње.
Морам некако да сазнам положај другог зареза, а затим да извучем све што је лево од другог зареза.
Да бих то урадио, употребио сам функцију СУБСТИТУТЕ да променим другу запету у ускличник. Ово ми даје јединствен лик у ћелији. Сада могу да користим положај узвичника да издвојим све лево од другог зареза.
Ова позиција узвичника се користи у функцији ЛИЈЕВО за издвајање свега прије другог зареза.
Засада је добро!
Али шта ако у скупу података постоји недоследан број зареза.
На пример, у доњем скупу података неке ћелије имају две зарезе, а неке ћелије са три зареза, а ја морам да издвојим сав текст пре последње зарезе.
У овом случају, морам некако да схватим позицију последње појаве зареза, а затим да издвојим све лево од ње.
Испод је формула која ће то учинити
= ЛЕФТ (А2, ФИНД ("!", СУБСТИТУТЕ (А2, ",", "!", ЛЕН (А2) -ЛЕН (СУБСТИТУТЕ (А2, ",", "))))-1)
Горња формула користи функцију ЛЕН за проналажење укупне дужине текста у ћелији, као и дужине текста без зареза.
Када одузмем ове две вредности, добија се укупан број зареза у ћелији.
Дакле, дао бих 3 за ћелију А2 и 2 за ћелију А4.
Ова вредност се затим користи у формули ЗАМЕНА за замену последњег зареза узвичником. А онда можете да употребите леву функцију да извучете све што је лево од ускличника (где је некада био последњи зарез)
Као што можете видети у примерима, коришћење комбинације текстуалних формула омогућава вам да се носите са многим различитим ситуацијама.
Такође, пошто је резултат повезан са оригиналним подацима, када промените оригиналне податке, резултат би се аутоматски ажурирао.
Уклоните текст помоћу Фласх Филл -а
Фласх Филл је алат који је представљен у програму Екцел 2013 и доступан је у свим верзијама након тога.
Функционише тако што идентификује обрасце када ручно унесете податке, а затим их екстраполирате како бисте добили податке за целу колону.
Дакле, ако желите да уклоните текст пре или после одређеног знака, само треба да покажете фласх вили како би изгледао резултат (ако га унесете ручно неколико пута), а флеш флеш би аутоматски разумео образац и дао вам Резултати.
Дозволите ми да вам ово покажем на примеру.
Испод имам скуп података у којем желим уклонити сав текст иза зареза.
Ево корака за то помоћу Фласх Филл -а:
- У ћелију Б2, која је суседна колона наших података, ручно унесите „Јеффери Хаггинс“ (што је очекивани резултат)
- У ћелију Б3 унесите „Тим Сцотт“ (што је очекивани резултат за другу ћелију)
- Изаберите опсег Б2: Б10
- Кликните на картицу Почетна
- У групи Уређивање кликните на падајућу опцију Попуни
- Кликните на Фласх Филл
Горе наведени кораци дали би вам резултат као што је приказано испод:
Такође можете да користите пречицу на тастатури Фласх Филл Цонтрол + Е. након одабира ћелија у колони резултата (колона Б у нашем примеру)
Фласх Филл је диван алат и у већини случајева може да препозна образац и да вам пружи прави резултат. али у неким случајевима можда неће моћи правилно да препозна образац и може вам дати погрешне резултате.
Зато обавезно двапут проверите резултате Фласх Филл-а
И баш као што смо уклонили сав текст након одређеног знака помоћу фласх филл -а, можете користити исте кораке за уклањање текста пре одређеног знака. само ручно покажите фласх филл како би резултат требало да изгледа као мој Интернет у суседној колони, а он ће учинити остало.
Уклоните текст помоћу ВБА (прилагођена функција)
Читав концепт уклањања текста пре или после одређеног знака зависи од проналажења положаја тог знака.
Као што се горе видело, пронаћи последњу појаву тог знака добро значи употребом мешавине формула.
Ако је ово нешто што често морате да радите, можете поједноставити овај процес стварањем прилагођене функције помоћу ВБА (назване кориснички дефинисане функције).
Када једном креирате, можете поново и поново користити ову функцију. Такође је много једноставнији и лакши за употребу (пошто се већина тешких терета врши помоћу ВБА кода на задњој страни).
Испод ВБА кода који можете користити за креирање прилагођене функције у Екцелу:
Функција ЛастПоситион (рЦелл Ас Ранге, рЦхар Ас Стринг) 'Ова функција даје последњу позицију наведеног знака' Овај код је развио Сумит Бансал (хттпс://трумпекцел.цом) Дим рЛен Ас Интегер рЛен = Лен (рЦелл) За и = рЛен То 1 Степ -1 Иф Мид (рЦелл, и - 1, 1) = рЦхар Тхен ЛастПоситион = и - 1 Екит Фунцтион Енд Иф Нект и Енд Фунцтион
Морате да поставите ВБА код у редован модул у ВБ Едитор -у или у личну радну свеску за макрое. Када га имате тамо, можете га користити као било коју другу редовну функцију радног листа у радној свесци.
Ова функција има 2 аргумента:
- Референца ћелије за коју желите да пронађете последњу појаву одређеног знака или текстуалног низа
- Знак или текстуални низ чију позицију морате да пронађете
Претпоставимо да сада имате доњи скуп података и желите да уклоните сав текст после последње зарезе и да имате само текст пре последње зарезе.
Испод је формула која ће то учинити:
= ЛЕФТ (А2, ЛастПоситион (А2, ",")-1)
У горњој формули сам навео да пронађем место последње зарезе. У случају да желите пронаћи положај неког другог знака или текстуалног низа, требали бисте то користити као други аргумент у функцији.
Као што видите, ово је много краће и лакше за употребу, у поређењу са формулом дугачког текста коју смо користили у претходном одељку
Ако поставите ВБА код у модул у радној свесци, ову прилагођену функцију моћи ћете да користите само у тој радној свесци. Ако желите да ово користите у свим радним свескама на систему, морате да копирате и залепите овај код у личну радну свеску за макрое.
Ово су неки од порука које можете користити за брзо уклањање текста пре или после одређеног знака у Екцелу.
Ако је то једноставна једнократна ствар, онда то можете учинити помоћу функције пронађи и замени. шта ако је мало сложеније, онда морате да користите комбинацију уграђених Екцел формула или чак да креирате сопствену прилагођену формулу користећи ВБА.
Надам се да вам је овај водич био користан.
