Гледајте видео - Како извући бројеве из текстуалног низа у Екцелу (помоћу формуле и ВБА)
У Екцелу нема уграђене функције за издвајање бројева из низа у ћелији (или обрнуто - уклоните нумерички део и извуците текстуални део из алфанумеричког низа).
Међутим, то се може учинити помоћу коктела Екцел функција или неког једноставног ВБА кода.
Дозволите ми да вам прво покажем о чему говорим.
Претпоставимо да имате скуп података као што је приказано испод и желите да извучете бројеве из низа (као што је приказано испод):
Начин који одаберете зависиће и од верзије програма Екцел коју користите:
- За верзије пре Екцел 2016, морате да користите нешто дуже формуле
- За Екцел 2016 можете користити ново представљену функцију ТЕКСТЈОИН
- ВБА метода се може користити у свим верзијама програма Екцел
Кликните овде за преузимање датотеке примера
Издвоји бројеве из низа у Екцелу (Формула за Екцел 2016)
Ова формула ће радити само у програму Екцел 2016 јер користи ново представљену функцију ТЕКСТЈОИН.
Такође, ова формула може издвојити бројеве који су на почетку, крају или средини текстуалног низа.
Имајте на уму да би вам формула ТЕКСТЈОИН приказана у овом одељку дала све нумеричке знакове заједно. На пример, ако је текст „Цена 10 карата 200 УСД“, као резултат ћете добити 10200.Претпоставимо да имате скуп података као што је приказано испод и желите да извучете бројеве из низова у свакој ћелији:
Испод је формула која ће вам дати нумерички део из низа у Екцелу.
= ТЕКСТЈОИН ("", ТРУЕ, ИФЕРРОР ((МИД (А2, РОВ (ИНДИРЕЦТ ("1:" & ЛЕН (А2)))), 1)*1), ""))
Ово је формула низа, па морате да користите „Цонтрол + Схифт + Ентер‘Уместо да користите Ентер.
У случају да у текстуалном низу нема бројева, ова формула би вратила празно (празан низ).
Како функционише ова формула?
Дозволите ми да разбијем ову формулу и покушам да објасним како то функционише:
- РОВ (ИНДИРЕКТНО (“1:” & ЛЕН (А2))) - овај део формуле би дао низ бројева који почињу од један. Функција ЛЕН у формули враћа укупан број знакова у низу. У случају “Цијена је 100 УСД”, вратит ће се 19. Формуле би тако постале РОВ (ИНДИРЕКТНА (“1:19”). Функција РОВ ће тада вратити низ бројева - {1; 2; 3 ; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
- (МИД (А2, РОВ (ИНДИРЕЦТ (“1:” & ЛЕН (А2))), 1)*1) - Овај део формуле би вратио низ од #ВАЛУЕ! грешке или бројеви засновани на низу. Сви текстуални знакови у низу постају #ВАЛУЕ! грешке и све нумеричке вредности остају такве какве јесу. То се дешава како смо помножили функцију МИД са 1.
- ИФЕРРОР ((МИД (А2, РОВ (ИНДИРЕЦТ (“1:” & ЛЕН (А2))), 1)*1), ””) - Када се користи функција ИФЕРРОР, уклониле би се све #ВАЛУЕ! грешке и остали би само бројеви. Излаз овог дела би изгледао овако - {“”; ””; ””; ””; ””; ””; ””; ””; ””; ””; ”” ”;” ”; ””; ””; ””; 1; 0; 0}
- = ТЕКСТЈОИН (“”, ТРУЕ, ИФЕРРОР ((МИД (А2, РОВ (ИНДИРЕЦТ (“1:” & ЛЕН (А2))), 1)*1), ””))) - Функција ТЕКСТЈОИН сада једноставно комбинује знакове низа то остаје (који су само бројеви) и занемарује празан низ.
Преузмите датотеку примера
Исту логику можете користити и за издвајање текстуалног дела из алфанумеричког низа. Испод је формула која би добила текстуални део из низа:
= ТЕКСТЈОИН ("", ТРУЕ, ИФ (ИСЕРРОР (МИД (А2, РОВ (ИНДИРЕЦТ ("1:" & ЛЕН (А2)))), 1)*1), МИД (А2, РОВ (ИНДИРЕЦТ ("1:" & ЛЕН (А2))), 1), ""))
Мања промена у овој формули је та што се функција ИФ користи за проверу да ли је низ који добијамо од функције МИД грешке или не. Ако је грешка, задржава вредност, иначе је замењује празном.
Затим се ТЕКСТЈОИН користи за комбиновање свих знакова текста.
Опрез: Иако ова формула ради одлично, она користи променљиву функцију (функција ИНДИРЕЦТ). То значи да ће, у случају да ово користите са великим скупом података, бити потребно неко време да добијете резултате. Најбоље је да направите резервну копију пре него што употребите ову формулу у Екцелу.Издвоји бројеве из низа у Екцелу (за Екцел 2013/2010/2007)
Ако имате Екцел 2013. 2010. или 2007, не можете користити формулу ТЕКСТЈОИН, па ћете за то морати користити компликовану формулу.
Претпоставимо да имате скуп података као што је приказано испод и желите да извучете све бројеве у низу у свакој ћелији.
Формула испод ће ово учинити:
= ИФ (СУМ (ЛЕН (А2) -ЛЕН (ЗАМЕНА (А2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}," ")))> 0, СУМПРОДУЦТ (МИД (0 & А2, ЛАРГЕ (ИНДЕКС (ИСНУМБЕР (-МИД (А2, РОВ (ИНДИРЕЦТ (" $ 1: $ "& ЛЕН (А2)))), 1)) * РОВ (ИНДИРЕЦТ ("$ 1: $" & ЛЕН (А2))), 0), РОВ (ИНДИРЕЦТ ("$ 1: $" & ЛЕН (А2))))+1,1) * 10^РОВ (ИНДИРЕЦТ ("$ 1: $" & ЛЕН (А2)))/10), "")
У случају да у текстуалном низу нема броја, ова формула би вратила празно (празан низ).
Иако је ово формула низа, ви не треба да бисте користили „Цонтрол-Схифт-Ентер“ да бисте ово користили. Једноставан унос функционише за ову формулу.
Заслуга за ову формулу припада невероватном форуму Мр. Екцел.
Поново, ова формула ће извући све бројеве у низу без обзира на позицију. На пример, ако је текст „Цена 10 карата 200 УСД“, као резултат ћете добити 10200.
Опрез: Иако ова формула ради одлично, она користи променљиву функцију (функција ИНДИРЕЦТ). То значи да ће, у случају да ово користите са великим скупом података, бити потребно неко време да добијете резултате. Најбоље је да направите резервну копију пре него што употребите ову формулу у Екцелу.Одвојите текст и бројеве у Екцелу помоћу ВБА
Ако често морате одвајати текст и бројеве (или издвајати бројеве из текста), можете користити и методу ВБА.
Све што треба да урадите је да употребите једноставан ВБА код за креирање прилагођене кориснички дефинисане функције (УДФ) у Екцелу, а затим уместо да користите дугачке и компликоване формуле, користите ту формулу ВБА.
Дозволите ми да вам покажем како да креирате две формуле у ВБА - једну за издвајање бројева и једну за издвајање текста из низа.
Издвојите бројеве из низа у Екцелу (помоћу ВБА)
У овом делу ћу вам показати како да креирате прилагођену функцију да бисте добили само нумерички део из низа.
Испод је ВБА код који ћемо користити за креирање ове прилагођене функције:
Функција ГетНумериц (ЦеллРеф Ас Стринг) Дим СтрингЛенгтх Ас Интегер СтрингЛенгтх = Лен (ЦеллРеф) Фор и = 1 То СтрингЛенгтх Иф ИсНумериц (Мид (ЦеллРеф, и, 1)) тхен Ресулт = Ресулт & Мид (ЦеллРеф, и, 1) Нект и ГетНумериц = Крајња функција резултата
Ево корака за креирање ове функције, а затим је користите на радном листу:
- Идите на картицу Девелопер.
- Кликните на Висуал Басиц (Можете користити и тастерску пречицу АЛТ + Ф11)
- У позадини ВБ Едитор која се отвори кликните десним тастером миша на било који објект радне свеске.
- Идите на Уметни и кликните на Модул. Ово ће уметнути објект модула за радну свеску.
- У прозору кода модула копирајте и залепите горе поменути ВБА код.
- Затворите ВБ Едитор.
Сада ћете моћи да користите функцију ГетТект на радном листу. Пошто смо урадили све тешке послове у самом коду, све што треба да урадите је да користите формулу = ГетНумериц (А2).
Ово ће вам тренутно дати само нумерички део низа.
Имајте на уму да пошто радна свеска сада има ВБА код, морате да је сачувате са екстензијом .клс или .клсм.
Преузмите датотеку примера
У случају да морате често користити ову формулу, можете је сачувати и у своју личну радну свеску за макрое. То ће вам омогућити да користите ову прилагођену формулу у било којој Екцел радној свесци са којом радите.
Издвајање текста из низа у Екцелу (помоћу ВБА)
У овом делу ћу вам показати како да креирате прилагођену функцију да бисте добили само текстуални део из низа.
Испод је ВБА код који ћемо користити за креирање ове прилагођене функције:
Функција ГетТект (ЦеллРеф Ас Стринг) Дим СтрингЛенгтх Ас Интегер СтрингЛенгтх = Лен (ЦеллРеф) Фор и = 1 То СтрингЛенгтх Иф Нот (ИсНумериц (Мид (ЦеллРеф, и, 1))) тхен Ресулт = Ресулт & Мид (ЦеллРеф, и, 1 ) Даље и ГетТект = Крајња функција резултата
Ево корака за креирање ове функције, а затим је користите на радном листу:
- Идите на картицу Девелопер.
- Кликните на Висуал Басиц (Можете користити и тастерску пречицу АЛТ + Ф11)
- У позадини ВБ Едитор која се отвори кликните десним тастером миша на било који објект радне свеске.
- Идите на Уметни и кликните на Модул. Ово ће уметнути објект модула за радну свеску.
- Ако већ имате модул, двапут кликните на њега (нема потребе за уметањем новог ако га већ имате).
- Ако већ имате модул, двапут кликните на њега (нема потребе за уметањем новог ако га већ имате).
- У прозору кода модула копирајте и залепите горе поменути ВБА код.
- Затворите ВБ Едитор.
Сада ћете моћи да користите функцију ГетНумериц на радном листу. Пошто смо урадили све тешке послове у самом коду, све што треба да урадите је да користите формулу = ГетТект (А2).
Ово ће вам тренутно дати само нумерички део низа.
Имајте на уму да пошто радна свеска сада има ВБА код, морате да је сачувате са екстензијом .клс или .клсм.
У случају да морате често користити ову формулу, можете је сачувати и у своју личну радну свеску за макрое. То ће вам омогућити да користите ову прилагођену формулу у било којој Екцел радној свесци са којом радите.
У случају да користите Екцел 2013 или старије верзије, а немате
