Гледайте видео - Как да извлечете числа от текстов низ в Excel (използване на формула и VBA)
В Excel няма вградена функция за извличане на числата от низ в клетка (или обратно - премахнете цифровата част и извлечете текстовата част от буквено -цифров низ).
Това обаче може да стане с помощта на коктейл от функции на Excel или някакъв прост VBA код.
Нека първо ви покажа за какво говоря.
Да предположим, че имате набор от данни, както е показано по -долу и искате да извлечете числата от низа (както е показано по -долу):
Избраният от вас метод също ще зависи от версията на Excel, която използвате:
- За версии преди Excel 2016 трябва да използвате малко по -дълги формули
- За Excel 2016 можете да използвате ново въведената функция TEXTJOIN
- Методът VBA може да се използва във всички версии на Excel
Щракнете тук, за да изтеглите примерния файл
Извличане на числа от низ в Excel (Формула за Excel 2016)
Тази формула ще работи само в Excel 2016, тъй като използва нововъведената функция TEXTJOIN.
Също така, тази формула може да извлече числата, които са в началото, края или средата на текстовия низ.
Обърнете внимание, че формулата TEXTJOIN, обхваната в този раздел, ще ви даде всички цифрови знаци заедно. Например, ако текстът е „Цената на 10 билета е 200 USD“, в резултат ще получите 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 USD“, тя ще се върне 19. Така формулите ще станат ROW (ИНДИРЕКТНИ („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) - Тази част от формулата ще върне масив от #VALUE! грешки или числа въз основа на низ. Всички текстови знаци в низа стават #VALUE! грешки и всички числови стойности остават такива, каквито са. Това се случва, тъй като умножихме функцията MID с 1.
- IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””) - Когато се използва функцията IFERROR, тя ще премахне всички #VALUE! грешки и ще останат само числата. Резултатът от тази част ще изглежда така - {“”; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; 1; 0; 0}
- = TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””))) - Функцията TEXTJOIN сега просто комбинира символите на низ това остава (които са само числата) и игнорира празния низ.
Изтеглете примерния файл
Можете също да използвате същата логика, за да извлечете текстовата част от буквено -цифров низ. По -долу е формулата, която ще получи текстовата част от низа:
= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2)))), 1)*1), MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1), ""))
Малка промяна в тази формула е, че функцията IF се използва за проверка дали масивът, който получаваме от функцията MID, е грешка или не. Ако това е грешка, той запазва стойността иначе я заменя с празно място.
Тогава TEXTJOIN се използва за комбиниране на всички текстови знаци.
Внимание: Въпреки че тази формула работи чудесно, тя използва променлива функция (функцията INDIRECT). Това означава, че в случай че използвате това с огромен набор от данни, може да отнеме известно време, за да ви даде резултати. Най -добре е да създадете резервно копие, преди да използвате тази формула в 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 USD“, в резултат ще получите 10200.
Внимание: Въпреки че тази формула работи чудесно, тя използва променлива функция (функцията INDIRECT). Това означава, че в случай че използвате това с огромен набор от данни, може да отнеме известно време, за да ви даде резултати. Най -добре е да създадете резервно копие, преди да използвате тази формула в Excel.Отделете текст и числа в Excel с помощта на VBA
Ако отделянето на текст и числа (или извличането на числа от текста) е нещо, което често трябва, можете да използвате и метода VBA.
Всичко, което трябва да направите, е да използвате прост VBA код, за да създадете персонализирана потребителска функция (UDF) в Excel, а след това вместо да използвате дълги и сложни формули, използвайте тази формула VBA.
Нека ви покажа как да създадете две формули във VBA - една за извличане на числа и една за извличане на текст от низ.
Извличане на числа от низ в Excel (с помощта на VBA)
В тази част ще ви покажа как да създадете персонализираната функция, за да получите само числовата част от низ.
По -долу е кодът VBA, който ще използваме за създаване на тази персонализирана функция:
Функция GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Тогава Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Крайна функция на резултата
Ето стъпките за създаване на тази функция и след това да я използвате в работния лист:
- Отидете в раздела Разработчик.
- Кликнете върху Visual Basic (Можете също да използвате клавишната комбинация ALT + F11)
- В отварящия се бекенд на VB Editor щракнете с десния бутон върху някой от обектите на работната книга.
- Отидете на Вмъкване и щракнете върху Модул. Това ще вмъкне обекта на модула за работната книга.
- В прозореца с код на модул копирайте и поставете VBA кода, споменат по -горе.
- Затворете редактора на VB.
Сега ще можете да използвате функцията GetText в работния лист. Тъй като сме направили всички тежки повдигания в самия код, всичко, което трябва да направите, е да използвате формулата = GetNumeric (A2).
Това незабавно ще ви даде само цифровата част на низа.
Имайте предвид, че тъй като работната книга вече има код VBA в нея, трябва да я запишете с разширение .xls или .xlsm.
Изтеглете примерния файл
В случай, че се налага да използвате тази формула често, можете също да я запишете в личната си работна книга за макроси. Това ще ви позволи да използвате тази персонализирана формула във всяка от работните книги на Excel, с които работите.
Извличане на текст от низ в Excel (с помощта на VBA)
В тази част ще ви покажа как да създадете персонализираната функция, за да получите само текстовата част от низ.
По -долу е кодът VBA, който ще използваме за създаване на тази персонализирана функция:
Функция GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) then Result = Result & Mid (CellRef, i, 1 ) Следващ i GetText = Крайна функция на резултата
Ето стъпките за създаване на тази функция и след това да я използвате в работния лист:
- Отидете в раздела Разработчик.
- Кликнете върху Visual Basic (Можете също да използвате клавишната комбинация ALT + F11)
- В отварящия се бекенд на VB Editor щракнете с десния бутон върху някой от обектите на работната книга.
- Отидете на Вмъкване и щракнете върху Модул. Това ще вмъкне обекта на модула за работната книга.
- Ако вече имате модул, щракнете двукратно върху него (няма нужда да поставяте нов, ако вече го имате).
- Ако вече имате модул, щракнете двукратно върху него (няма нужда да поставяте нов, ако вече го имате).
- В прозореца с код на модул копирайте и поставете VBA кода, споменат по -горе.
- Затворете редактора на VB.
Сега ще можете да използвате функцията GetNumeric в работния лист. Тъй като сме направили всички тежки повдигания в самия код, всичко, което трябва да направите, е да използвате формулата = GetText (A2).
Това незабавно ще ви даде само цифровата част на низа.
Имайте предвид, че тъй като работната книга вече има код VBA в нея, трябва да я запишете с разширение .xls или .xlsm.
В случай, че се налага да използвате често тази формула, можете също да я запишете в личната си работна книга за макроси. Това ще ви позволи да използвате тази персонализирана формула във всяка от работните книги на Excel, с които работите.
В случай, че използвате Excel 2013 или предишни версии и нямате
