Екцел

Уклоните нумеричке знакове из ћелије

Strip Numeric Characters From Cell

Екцел формула: Уклоните нумеричке знакове из ћелијеОпшта формула | _+_ | Резиме

Да бисте уклонили нумеричке знакове из текстуалног низа, можете користити формулу засновану на ТЕКСТЈОИН функција . У приказаном примеру, формула у Ц5 је:





{= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (A1, ROW ( INDIRECT ('1:100')),1)+0), MID (A1, ROW ( INDIRECT ('1:100')),1),''))}

Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер, осим у Екцел 365 .

Објашњење

Екцел нема начин да пребаци слова у текстуалном низу у арраи директно у формули. Као заобилазно решење, ова формула користи функцију МИД, уз помоћ функција РОВ и ИНДИРЕЦТ за постизање истог резултата. Формула у Ц5, копирана, је:





 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, ROW ( INDIRECT ('1:100')),1)+0), MID (B5, ROW ( INDIRECT ('1:100')),1),''))

Ово изгледа прилично компликовано, али суштина је да ми стварамо арраи свих знакова у Б5 и тестирајте сваки знак да видите да ли је то број. Ако је тако, одбацујемо вредност и замењујемо је са празан низ (''). Ако није, додајемо не-нумерички знак у 'обрађен' низ. Коначно, користимо функцију ТЕКСТЈОИН (ново у програму Екцел 2019) за повезивање свих знакова заједно, занемарујући празне вредности.

како подићи нешто до моћи у екцелу

Радећи изнутра, МИД функција користи се за издвајање текста у Б5, један по један знак.Кључ је у РОВ и ИНДИРЕКТАН исечак овде:



 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, ROW ( INDIRECT ('1:100')),1)+0), MID (B5, ROW ( INDIRECT ('1:100')),1),''))

који покреће низ који садржи 100 бројева овако:

{1,2,3,4,5,6,7,8 .... 99,100}

Напомена: 100 представља максимални број знакова за обраду. Промените их тако да одговарају вашим подацима или користите функцију ЛЕН како је доле објашњено.

Овај низ улази у функцију МИД као старт_нум расправа. За н__цхарс , користимо 1.

формула за израчунавање старости од датума рођења

МИД функција враћа низ овако:

 
 ROW ( INDIRECT ('1:100'))

Напомена: додатне ставке у низу су уклоњене ради читљивости.

Овом низу додајемо нулу. Ово је једноставан трик који приморава Екцел да присили текст на број. Нумеричке текстуалне вредности попут '1', '2', '3', '4' итд. Се конвертују без грешака, али не-нумеричке вредности неће успети и изазвати грешку #ВАЛУЕ. Ми користимо ИФ функција са ИСЕРР функција да ухвате ове грешке. Када видимо грешку, знамо да имамо не-нумерички знак, па га уносимо у обрађени арраи са другом функцијом МИД:

 
{'3''4''6''5''3'' ''J''i''m'' ''M''c''D''o''n''a''l''d'''''''...}

Ако немој добија грешку, знамо да имамо број, па убацујемо празан низ ('') у низ уместо места.

Коначни резултат низа иде у функцију ТЕКСТЈОИН као тект1 расправа. За граничник , користимо празан низ ('') и за игноре_емпти испоручујемо ИСТИНИТО. ТЕКСТЈОИН онда спојени све вредности које нису празне у низу и враћа резултат.

Прецизна дужина низа

Уместо да кодирате број попут 100 у ИНДИРЕЦТ, можете користити ЛЕН функција да бисте направили низ са стварним бројем знакова у ћелији овако:

 
 MID (B5, ROW ( INDIRECT ('1:100')),1)

ЛЕН враћа број знакова у ћелији као број, који се користи уместо 100. То омогућава формули да аутоматски повећа било који број знакова.

Уклањање додатног простора

Када уклоните нумеричке знакове, можда ће вам остати вишак размака. Да бисте уклонили водећи и задњи размак и нормализовали размаке између речи, формулу приказану на овој страници можете умотати у ТРИМ функција :

како преместити колону у Екцел
 
 MID (A1, ROW ( INDIRECT ('1:'& LEN (A1))),1)

СА СЕКВЕНЦИЈОМ

Ин Екцел 365 , Нови СЕКУЕНЦЕ функција може заменити РОВ + ИНДИРЕЦТ код изнад:

 
= TRIM (formula)

Овде користимо СЕКУЕНЦЕ + ЛЕН за изградњу низа одговарајуће дужине у једном кораку.

Витх ЛЕТ

Ову формулу можемо додатно поједноставити помоћу ЛЕТ функција . Пошто је низ направљен два пута горе са СЕКУЕНЦЕ и ЛЕН, можемо дефинисати низ као променљиву и креирати га само једном:

 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, SEQUENCE ( LEN (B5)),1)+0), MID (B5, SEQUENCE ( LEN (B5)),1),''))

Овде вредност арраи се поставља само једном, а затим два пута користи унутар функције МИД.

Аутор Даве Брунс


^