Изтеглете примерната работна книга
Този урок ще ще ви покаже как да намерите и извлечете число от текстов низ в Excel и Google Sheets.
Намерете и извлечете номер от низ
Понякога вашите данни могат да съдържат числа и текст и искате да извлечете числовите данни.
Ако числовата част е от дясната или от лявата страна на низа, сравнително лесно е да разделите номера и текста. Ако обаче числата са вътре в низа, т.е. между два текстови низа, ще трябва да използвате много по -сложна формула (показана по -долу).
TEXTJOIN - Извличане на числа в Excel 2016+
В Excel 2016 беше въведена функцията TEXTJOIN, която може да извлече числата от всяка точка на текстовия низ. Ето формулата:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1)*1), "")) |
Нека да видим как работи тази формула.
Функциите ROW, INDIRECT и LEN връщат масив от числа, съответстващи на всеки знак във вашия буквено -цифров низ. В нашия случай „Monday01Day“ има 11 знака, така че след това функцията ROW ще върне масива {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1)*1), ""))) |
След това функцията MID извлича всеки знак от текстовия низ, създавайки масив, съдържащ вашия оригинален низ:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Умножаването на всяка стойност в масива с 1 ще създаде масив, съдържащ грешки Ако символът на масива е текст:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE! !}), "")) |
След това функцията IFERROR премахва стойностите на грешките:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Оставяйки само функцията TEXTJOIN, която свързва останалите числа заедно.
Имайте предвид, че формулата ще ви даде всички цифрови знаци заедно от вашия низ. Например, ако вашият буквено -цифров низ е Monday01Day01, той ще ви даде 0101 в резултат.
Извличане на номера - преди Excel 2016
Преди Excel 2016 можете да използвате много по -сложен метод за извличане на числа от текст. Можете да използвате функцията FIND заедно с функциите IFERROR и MIN, за да определите както първата позиция на числовата част, така и първата позиция на текстовата част след числото. След това просто извличаме числовата част с функцията MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"a" , "b", "c", "d", "e", "f", "g", "h", "I", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (IFERROR (FIND (FIND ( {0,1,2,3,4,5,6,7,8,9}, B3), 999999999))) |
Забележка: Това е формула от масив, трябва да въведете формулата, като натиснете CTRL + SHIFT + ENTER, вместо само ENTER.
Нека да видим стъпка по стъпка как работи тази формула.
Намерете номера на юмрука
Можем да използваме функцията FIND за намиране на началната позиция на номера.
| 1 | = MIN (IFERROR (FIND ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
За аргумента find_text на функцията FIND използваме константата на масива {0,1,2,3,4,5,6,7,8,9}, което кара функцията FIND да извърши отделно търсене за всяка стойност в константата на масива.
Аргументът within_text на функцията FIND в нашия случай е Monday01Day, в който 1 може да бъде намерен на позиция 8 и 0 на позиция 7, така че нашият масив с резултати ще бъде: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
Използвайки функцията IFERROR, ние заменяме грешките #VALUE с 999999999. След това просто търсим минимума в този масив и следователно получаваме мястото на първото число (7).
Моля, обърнете внимание, че горната формула е формула на масив, трябва да натиснете Ctrl+Shift+Enter, за да я задействате.
Намерете първия текстов знак след числото
Подобно на намирането на първото число в низа, ние използваме функцията FIND, за да определим къде текстът започва отново след числото, използвайки добре и аргумента start_num на функцията.
| 1 | = MIN (IFERROR (FIND ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Тази формула работи много подобно на предишната, използвана за намиране на първото число, само че ние използваме букви в нашата константа на масива и следователно числата причиняват грешки #VALUE. Моля, обърнете внимание, че започваме търсене само след позицията, определена за първото число (това ще бъде аргументът start_num), а не от началото на низа.
Не забравяйте да натиснете Ctrl+Shift+Enter, за да използвате горната формула.
Не остава нищо друго, освен да сглобите всичко това.
Извличане на номер от два текста
След като имаме началната позиция на числовата част и началото на текстовата част след това, просто използваме функцията MID, за да извлечем желаната цифрова част.
| 1 | = MID (B3, C3, D3-C3) |
Друг метод
Без да го обяснявате по -подробно, можете също да използвате сложната формула по -долу, за да получите номера от низа.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT) ("1:" & LEN (B3))), 1))**ROW (INDIRECT ("1:" & LEN (B3) )), 0), ROW (INDIRECT ("1:" & LEN (B3))))+1,1)*10^ROW (INDIRECT ("1:" & LEN (B3)))/10) |
Моля, имайте предвид, че тази формула по -горе ще ви даде 0, когато в низа не е намерено число и че водещите нули ще бъдат пропуснати.
Намерете и извлечете номер от низ в текст в Google Таблици
Примерите, показани по -горе, работят по същия начин в Google Sheets, както и в Excel.
