В Excel няма вградена функция за извличане на числа от низ в клетка (и обратното - премахване на цифровата част и извличане на текстовата част от буквено-цифров низ).
Това обаче може да стане с помощта на функция на Excel или някакъв прост VBA код.
Нека първо ви кажа за какво говоря.
Да предположим, че имате набор от данни като по-долу и искате да извлечете числа от низове (като по-долу):
Избраният от вас метод зависи и от версията на Excel, която използвате:
- За версии на Excel преди 2016 г. трябва да използвате малко по-дълга формула
- За Excel 2016 можете да използвате нововъведената функция TEXTJOIN
- Методът VBA е наличен за всички версии на Excel
Съдържание
Извличане на числа от низове в Excel (формули на Excel 2016)
Тази формула работи само в Excel 2016, защото използва нововъведената функция TEXTJOIN.
Освен това, тази формула може да извлича числа в началото, края или средата на текстов низ.
Обърнете внимание, че формулата TEXTJOIN, описана в този раздел, събира всички цифрови знаци заедно.Например, ако текстът е "10 билета са $200", резултатът е 10200.
Да предположим, че имате набор от данни като следния и искате да извлечете числа от низове във всяка клетка:
Формулата по-долу ще ви даде числовата част от низ в Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Това е формула за масив, така че трябва да използвате " Control + Shift + Enter " вместо да използвате Enter.
Тази формула ще върне празен (празен низ), ако в текстовия низ няма числа.
Как действа тази формула?
Позволете ми да разбия тази формула и да се опитам да обясня как работи:
- ROW(INDIRECT(“1:”&LEN(A2))) – Тази част от формулата ще даде серия от числа, започващи от едно.Функцията LEN във формулата връща общия брой знаци в низа.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1) - Тази част от формулата ще върне масив от #VALUEs!Грешки или числа, базирани на низове.Всички текстови знаци в низа стават #VALUE!Грешките и всички стойности остават такива, каквито са.Това се случва, когато умножим функцията MID по 1.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – Когато се използва функцията IFERROR, всички #VALUEs се премахват!Грешка, ще останат само числа.Резултатът от тази част изглежда така - {"";"";"";"";"";"";"";""";"";"";"";"";""";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – функцията TEXTJOIN сега просто комбинира символи от низове Все още има (само числа) и игнорира празни низове.
Професионален съвет:Ако искате да проверите изхода на част от формулата, изберете клетката, натиснете F2, за да влезете в режим на редактиране, изберете частта от формулата, която искате да изведете, и натиснете F9.Ще видите резултатите веднага.След това не забравяйте да натиснете Control + Z или натиснете Escape.Не натискайте enter.
Можете също да използвате същата логика за извличане на части от текст от буквено-цифрови низове.Ето формулата за получаване на текстовата част от низа:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Малка промяна в тази формула е, че функцията IF се използва за проверка на масива, който получаваме от функцията MID за грешки.Ако е грешка, запазете стойността, в противен случай я заменете с празно.
След това използвайте TEXTJOIN, за да комбинирате всички текстови знаци.
注意: Въпреки че тази формула е валидна, тя използва променлива функция (ИНДИРЕКТНАТА функция).Това означава, че ако го използвате с огромен набор от данни, може да отнеме известно време, за да ви даде резултати.Най-добре е да създадете резервно копие, преди да използвате тази формула в Excel.
Извличане на числа от низове в Excel (за Excel 2013/2010/2007)
Ако имате Excel 2013. 2010. или 2007, не можете да използвате формули TEXTJOIN, така че трябва да използвате сложни формули, за да направите това.
Да предположим, че имате набор от данни като по-долу и искате да извлечете всички числа в низове във всяка клетка.
Формулата по-долу ще направи това:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Тази формула ще върне празен (празен низ), ако в текстовия низ няма числа.
Въпреки че това е формула за масив, виеНяма нуждаИзползвайте "Control-Shift-Enter", за да го използвате.За тази формула работи прост вход.
Заслугата за тази формула е на невероятнотоГ-н Excel форум.
Отново, тази формула ще извлече всички числа в низа, независимо от позицията.Например, ако текстът е "10 билета са $200", резултатът е 10200.
注意: Въпреки че тази формула е валидна, тя използва променлива функция (ИНДИРЕКТНАТА функция).Това означава, че ако го използвате с огромен набор от данни, може да отнеме известно време, за да ви даде резултати.Най-добре е да създадете резервно копие, преди да използвате тази формула в Excel.
Разделете текст и числа в Excel с VBA
Ако често се налага да разделяте текст и числа (или да извличате числа от текст), можете също да използвате метода VBA.
Всичко, което трябва да направите, е да създадете персонализирана функция, дефинирана от потребителя (UDF) в Excel, като използвате прост VBA код и след това да използвате тази VBA формула вместо дълга и сложна формула.
Позволете ми да ви покажа как да създадете две формули във VBA - една за извличане на числа и една за извличане на текст от низове.
Извличане на числа от низове в Excel (с помощта на VBA)
В тази част ще ви покажа как да създадете персонализирана функция, за да получите само числовата част от низ.
Ето VBA кода, който ще използваме, за да създадем тази персонализирана функция:
Функция GetNumeric(CellRef като низ) Dim StringLength като цяло число StringLength = Len(CellRef) За i = 1 до StringLength Ако IsNumeric(Mid(CellRef, i, 1)) Тогава Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Крайна функция на резултата
Ето стъпките за създаване на тази функция и след това да я използвате в работен лист:
Вече ще можете да използвате функцията GetText в работния лист.Тъй като сме свършили цялата тежка работа в самия код, всичко, което трябва да направите, е да използвате формулата =GetNumeric(A2).
Това незабавно ще ви даде числовата част от низа.
Имайте предвид, че тъй като работната книга вече съдържа VBA код, ще трябва да я запишете с разширение .xls или .xlsm.
Ако трябва да използвате тази формула често, можете също да я запишете в личната си работна книга с макроси.Това ще ви позволи да използвате тази персонализирана формула във всяка работна книга на Excel, която използвате.
Извличане на текст от низ в Excel (с помощта на VBA)
В тази част ще ви покажа как да създадете персонализирана функция, за да получите само текстовата част от низ.
Ето VBA кода, който ще използваме, за да създадем тази персонализирана функция:
Функция GetText(CellRef като низ) Dim StringLength като цяло число StringLength = Len(CellRef) За i = 1 до StringLength Ако не (IsNumeric(Mid(CellRef, i, 1))) Тогава Резултат = Резултат & Mid(CellRef, i, 1 ) Следваща i GetText = Функция за край на резултата
Ето стъпките за създаване на тази функция и след това да я използвате в работен лист:
Вече ще можете да използвате функцията GetNumeric в работния лист.Тъй като сме свършили цялата тежка работа в самия код, всичко, което трябва да направите, е да използвате формулата =GetText(A2).
Това незабавно ще ви даде числовата част от низа.
Имайте предвид, че тъй като работната книга вече съдържа VBA код, ще трябва да я запишете с разширение .xls или .xlsm.
Ако трябва да използвате тази формула често, можете също да я запишете в личната си работна книга с макроси.Това ще ви позволи да използвате тази персонализирана формула във всяка работна книга на Excel, която използвате.
