Урок за Excel – извличане на текст или число по конкретна позиция

В много случаи може да се наложи само да извлечете полезното съдържание от изречение или текстов низ в клетка, като извличане на провинцията от адреса, извличане на имейл адреса от изречение, извличане на номера на куриерската сметка от разговор, и така нататък. Този урок стеснява извличането до конкретна позиция в клетка и събира различни методи за подпомагане на извличането на текст или числа от клетка по конкретна позиция в Excel.

---

Извличане на текст по позиция

Този раздел събира общи позиции, където текстът може да бъде извлечен от клетка, и предоставя съответните методи за обработката им стъпка по стъпка. Можете да прегледате за повече подробности.

1. Извлечете броя знаци отляво или отдясно

За да извлечете броя знаци от лявата или дясната страна на низ, можете да опитате един от методите по-долу.

1.1 Извлечете първите или последните N знака с формули

Да предположим, че имате списък с текстови низове в колона B, както е показано на екранната снимка по-долу, за да извлечете първите 2 знака и последните 2 знака от всеки низ, можете да приложите следните формули.

Извличане на първите N знака от текстов низ

Функцията LEFT може да помогне за лесното извличане на първите N знака от текстов низ в Excel.

Обща формула

=LEFT(text_string,[num_chars])

Аргументи

Сега можете да приложите тази формула, за да извлечете първите 2 знака от клетките в колона B.

1. Изберете празна клетка, копирайте или въведете формулата по -долу и натиснете Въведете ключ за получаване на първия резултат. Изберете клетката с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да приложите формулата към други клетки.

=LEFT(B5,2)

Сега първите 2 знака във всяка клетка от диапазон B5:B10 са извлечени.

Извличане на последните N знака от текстов низ

Тук прилагаме функцията RIGHT, за да извлечем последните N знака от текстов низ в Excel.

Обща формула

=RIGHT(text_string,[num_chars])

Аргументи

Изберете празна клетка, копирайте или въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. След това изберете тази клетка с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да получите други резултати.

=RIGHT(B5,2)

1.2 Извлечете първите или последните N знака с невероятен инструмент

Въпреки че формулите по-горе са прости, за да извлечете първите или последните n знака от дълъг списък с текстов низ, все пак трябва да плъзнете манипулатора за автоматично попълване отгоре надолу, което може да отнеме малко време. Тук препоръчва Kutools за ExcelЕ Извличане на текст помощна програма за подпомагане на груповото извличане на първите или последните N знака от списък с текстов низ.

1. Изберете предварително списъка с текстови низове, от който искате да извлечете текст, и щракнете Kutools > Текст > Извличане на текст.

2. При изскачане Извличане на текст диалогов прозорец, трябва да конфигурирате както следва.

Забележка: За да направите резултата динамичен, когато текстовият низ се промени, можете да проверите Вмъкване като формула кутия.

3. При следващото изскачане Извличане на текст диалогов прозорец, изберете клетка за извеждане на извлечените знаци и след това щракнете OK.

След това посочените първи или последни N знака се извличат групово от избраните клетки.

Кликнете, за да научите повече за тази функция.

---

2. Извличане на текст преди или след определен знак/дума

За да извлечете текст преди или след определен знак или дума, различните сценарии в този раздел ще отговорят на вашите нужди.

2.1 Извличане на текст преди или след първия разделител (знак)

Както е показано на екранната снимка по-долу, за да извлечете текст преди или след първия разделител от всяка клетка в диапазона B4:B10, можете да приложите един от методите по-долу.

2.1.1 Извличане на текст преди първия разделител с формула

Прилагането на формула, базирана на функциите LEFT и FIND, може да помогне за извличане на текст преди първия разделител от клетка. Можете да следвате стъпките по-долу, за да го направите.

Обща формула

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

Аргументи

Изберете празна клетка, копирайте или въведете формулата по-долу в нея и натиснете Въведете ключ за получаване на първия резултат. Изберете първата клетка с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да получите текстовете преди първия разделител на другите клетки.

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 Извличане на текст след първия разделител с формула

Формулата по-долу помага да се извлече текст след първия разделител от клетка в Excel.

Обща формула

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

Аргументи

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 Извлечете текст преди или след първия разделител с невероятен инструмент

Тук силно препоръчва Извличане на текст полезност на Kutools за Excel. С тази функция можете лесно да извличате групово текстове преди или след първия разделител от диапазон от клетки.

1. Изберете диапазона от клетки, където искате да извлечете текста, и след това щракнете Kutools > Текст > Извличане на текст.

2. В Извличане на текст диалогов прозорец, трябва да конфигурирате както следва.

Забележка: За да направите резултата динамичен, когато текстовият низ се промени, можете да поставите отметка в квадратчето Вмъкване като формула.

3. След това още един Извличане на текст изскача диалогов прозорец, изберете клетка за извеждане на резултатите и щракнете OK.

След това текстовете преди или след първия разделител се извличат от избраните клетки наведнъж.

За да научите повече за тази функция, моля посетете: Бързо извличане на определен текст от клетки в Excel.

2.2 Извличане на текст преди или след последния разделител (знак)

В стъпките по-горе научихме методите за извличане на текст преди или след първия разделител от клетка. Както е показано на екранната снимка по-долу, този раздел ще ви покаже две формули за извличане на текст преди или след последния разделител от клетка. Можете да следвате стъпките по-долу, за да го направите.

2.2.1 Извличане на текст преди последния разделител с формула

За да извлечете текст преди последния разделител от клетка, можете да използвате функциите SEARCH, LEN и SUBSTITUTE вътре във функцията LEFT.

Обща формула

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

Аргументи

Изберете клетка, въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да извлечете текстовете от други текстови низове в същата колона.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 Извличане на текст след последния разделител с формула

След като извлечете текст преди последния разделител от клетка, можете да приложите формулата по-долу, за да извлечете текста след последния разделител, както ви е необходимо.

Обща формула

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

Аргументи

Изберете клетка, въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да извлечете текстовете от други текстови низове в същата колона.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 Извличане на текст след n-тия знак

Вижте примера по-долу, има списък с текстов низ в диапазона B4:B10, за да извлечете текста след третия знак от всяка клетка, можете да приложите формула, базирана на функцията MID и функцията LEN.

Обща формула

=MID(text_string,nth_char+1,LEN(text_string))

Аргументи

Изберете празна клетка, копирайте или въведете формулата по-долу в нея и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да получите други резултати.

=MID(B5,3+1,LEN(B5))

2.4 Извличане на n-та дума от текстов низ

Да предположим, че имате списък с текстов низ, както е показано на екранната снимка по-долу, и искате да извлечете само n-тата дума от текстовия низ, този раздел предоставя три метода, за да го направите.

2.4.1 Извличане на n-та дума с формула

Можете да комбинирате функциите TRIM, MID, SUBSTITUTE, REPT и LEN, за да извлечете n-тата дума от текстов низ в клетка.

Обща формула

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

Аргументи

В този случай диапазонът B5:B10 съдържа текстовите низове, D5:D10 съдържа числата, представляващи n-тата дума, нека приложим тази формула, за да извлечем n-тата дума от текстов низ.

Изберете празна клетка, копирайте или въведете формулата по-долу в нея и натиснете Въведете ключ за получаване на първия резултат. Изберете тази клетка с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да получите n-тата дума от други клетки.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Забележка: Можете директно да въведете n-то число във формулата, както следва.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Извлечете n-тата дума с дефинирана от потребителя функция

Освен горната формула, можете също да приложите дефинирана от потребителя функция за извличане на n-та дума от клетка в Excel.

1. Натисни Друг + F11 за да отворите Microsoft Visual Basic за приложения прозорец.

2. В Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модул, и след това копирайте VBA по-долу в прозореца на кода.

VBA код: Извлечете n-тата дума от текстов низ в клетка

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.

4. Върнете се в работния лист, съдържащ текстовите низове, от които искате да извлечете n-тата дума. Изберете празна клетка, копирайте или въведете формулата по-долу в нея и натиснете Въведете ключ за получаване на n-тата дума.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Забележка: във формулата D5 е клетката, която съдържа число, представляващо n-тата дума. Като алтернатива можете директно да замените референтната клетка с число.

5. Изберете клетката с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да извлечете n-тата дума от текстовите низове на други клетки.

2.4.3 Извлечете n-тата дума с невероятен инструмент

Ако не искате да прилагате ръчно формула или дефинирана от потребителя функция, предоставена по-горе, тук препоръчва Kutools за Excel Извлечете n-тата дума в клетката полезност. С тази функция можете лесно да извлечете n-тата дума от текстов низ в клетка само с няколко кликвания.

1. Изберете клетка, за да поставите резултата, и щракнете Kutools > Помощник за формула > Текст > Екстракт n-тата дума в клетката. Вижте екранна снимка:

2. В Помощник за формули диалогов прозорец, трябва да конфигурирате както следва.

3. След това n-тата (втората) дума се извлича от текстовия низ в клетка B5 и можете да видите, че едновременно се създава формула. Изберете тази клетка с резултати и плъзнете манипулатора й за автоматично попълване надолу, за да получите n-тата дума от други текстови низове.

Кликнете, за да научите повече за тази функция.

2.5 Извличане на текст преди или след n-тото появяване на разделител

Да предположим, че имате списък с текстови низове, както е показано на екранната снимка по-долу. За да извлечете текста преди или след второто появяване на интервал, този раздел предоставя две формули, които да ви помогнат да го направите.

2.5.1 Извличане на текст преди n-тото появяване на разделител

Можете да използвате функцията LEFT заедно с функциите SUBSTITUTE и FIND, за да извлечете текст преди n-тото появяване на разделител от клетка в Excel.

Обща формула

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

Аргументи

Изберете клетка, копирайте или въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и я плъзнете надолу, за да получите други резултати в списъка.

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

Забележка: Във формулата B5 е клетката, съдържаща текстовия низ, от който искате да извлечете текста; „ “ тук представлява интервал, а числото 2 представлява второто появяване на интервал. Можете да ги промените според вашите нужди.

2.5.2 Извличане на текст след n-тото появяване на разделител

За да извлечете текст след n-тото появяване на разделител, можете да приложите функцията RIGHT с функциите SUBSTITUTE, LEN и FIND.

Обща формула

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

Аргументи

Сега можете да приложите тази формула, за да извлечете текста след второто появяване на интервала от всяка клетка в диапазон B5:B10, както следва.

Изберете клетка, въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и я плъзнете надолу, за да получите други резултати.

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 Извличане на текст преди или след нов ред

Да предположим, че имате списък с поръчки в колона B и искате да извлечете само частта с датата и частта с номера на продукта от всяка клетка. Можете да използвате формулите на Excel по-долу, за да го направите.

2.6.1 Извличане на текст преди първия нов ред с формула

Както можете да видите на екранната снимка по-горе, частта с датата се намира преди първия прекъсващ ред в клетката. Този раздел ще демонстрира функция LEFT заедно с функцията SEARCH, за да ви помогне да извлечете текста преди първия нов ред в клетка.

Обща формула

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

Аргументи

Изберете празна клетка, копирайте или въведете формулата по-долу в нея и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да приложите тази формула към други клетки.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

След това можете да видите текста преди първия нов ред във всяка клетка в диапазон B5:B8 да бъде извлечен, както е показано на екранната снимка по-долу.

Забележка: Във формулата CHAR(10) представлява нов ред в Windows.

2.6.2 Извличане на текст след последния нов ред с формула

В предишната стъпка говорихме как да извлечем текст преди първия нов ред в клетка. И тази част ще ви насочи как да извлечете текст след последния нов ред в клетка с различна формула.

Обща формула

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

Аргументи

Изберете празна клетка, въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да приложите формулата към други клетки.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Тогава продуктът №. част от всяка клетка в списъка се извлича, както е показано на екранната снимка по-горе.

Забележка: Във формулата CHAR(10) представлява нов ред в Windows.

2.7 Извличане на текст преди или след дума

В предишните части научихме как да извличаме текст преди или след знак или разделител. Какво трябва да направите, за да извлечете текст преди или след цяла дума? Този раздел ще представи три метода, които да ви помогнат да изпълните тази задача.

2.7.1 Извличане на текст преди определена дума с формула

Следната формула ви помага да извлечете текст преди определена дума в клетка в Excel.

Обща формула

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

Аргументи

Изберете празна клетка, въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да приложите тази формула към други клетки.

В този пример ще извлечем всички текстове преди думата „Excel“, така че директно въвеждаме думата във формулата и я затваряме в двойни кавички. Или можете да посочите клетка, която съдържа думата „Excel“.

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Забележки:

2.7.2 Извличане на текст след определена дума с формула

За да извлечете текст след определена дума, можете да приложите следната формула, за да го направите.

Обща формула

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

Аргументи

Изберете клетка, въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете клетката с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да приложите тази формула към други клетки.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

След това можете да видите всички текстове след думата „Excel“ във всяка клетка, които са извлечени, както е показано на екранната снимка по-долу.

Забележки:

2.7.3 Извлечете текст преди или след определена дума с удивителен инструмент

Ако смятате, че използването на формула може да причини много неудобства, тук горещо препоръчваме Извличане на текст полезност на Kutools за Excel. Тази функция помага за автоматизиране на задачата за извличане в Excel само с няколко кликвания.

1. кликване Kutools > Текст > Извличане на текст за да активирате тази функция.

2. В Извличане на текст диалогов прозорец, направете следните настройки.

Забележки: Ако искате да създадете динамични резултати, маркирайте Вмъкване като формула кутия. Тогава резултатите ще се актуализират автоматично, когато данните в диапазона се променят.

3. След това an Извличане на текст се появява диалогов прозорец, трябва да изберете клетка, за да изведете резултата, и след това да щракнете върху OK бутон.

След това текстовете преди или след определена дума във всяка клетка в избрания диапазон се извличат незабавно.

Забележка: Тази функция е чувствителна към главни и малки букви.

Кликнете, за да научите повече за тази функция.

---

3. Извличане между знаци/думи

Ако искате да извлечете текст между определени знаци или думи, опитайте следните методи.

3.1 Извличане на текст между два знака

За извличане на текст между два знака, които могат да бъдат еднакви или различни символи. Този раздел предоставя няколко метода и можете да изберете един от тях според вашите нужди.

3.1.1 Извличане на текст между два еднакви знака с формула

Както е показано на екранната снимка по-долу, има списък с текстови низове в колона B и искате да извлечете числовата част между знаците „/“ от всяка клетка в диапазона, следната формула може да ви направи услуга.

Изберете празна клетка, копирайте или въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете клетката с резултати и след това я плъзнете Автоматично попълване на манипулатора надолу, за да получите резултати от други клетки в списъка.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

След това текстът между два еднакви знака „/“ се извлича от всяка клетка в диапазона. Вижте екранна снимка:

Забележка:

3.1.2 Извличане на текст между два различни знака с формула

След като научите как да извличате текст между два еднакви знака в клетка, тук ще демонстрираме формула за извличане на текст между два различни знака. Както е показано на екранната снимка по-долу, за да извлечете само имейл адреса между „<“ и „>“ от всяка клетка в колона B, можете да направите следното.

Обща формула

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

Аргументи

Изберете празна клетка, копирайте или въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да приложите тази формула към други клетки.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Можете да видите, че само текстът между посочените знаци е извлечен, както е показано на екранната снимка по-горе.

3.1.3 Извлечете текст между два знака с невероятен инструмент

Тук силно препоръчва Извличане на низове между определен текст функция на Kutools за Excel за да ви помогне лесно да извлечете текст между два еднакви или различни знака в клетка в Excel.

1. Изберете празна клетка, за да изведете резултата, и след това щракнете Kutools > Помощник за формула > Помощник за формула.

2. В Помощник за формули диалогов прозорец, направете следните настройки.

3. След това се извлича само текстът между “<” и “>” в клетка B5. Междувременно е създадена формула, можете да изберете тази клетка с резултати и след това да плъзнете манипулатора й за автоматично попълване надолу, за да извлечете текстове от други клетки в същия списък.

Кликнете, за да научите повече за тази функция.

3.1.4 Извличане на текст между два знака (включително знаците) по правило

Ако искате да запазите двата знака след извличане, опитайте да приложите правило в функцията за извличане на текст на Kutools за Excel.

1. кликване Kutools > Текст > Извличане на текст.

2. В Извличане на текст диалогов прозорец, направете следните настройки.

3. Друг Извличане на текст се появи диалогов прозорец, моля, изберете клетка, за да изведете резултата, и след това щракнете върху OK бутон.

След това текстът между определени знаци (включително знаците) се извлича групово от всяка клетка в избрания диапазон.

3.2 Извличане на текст между две думи

В допълнение към извличането на текст между два знака, може също да се наложи да извлечете текст между две думи. Например, извлечете всички текстови низове между две думи „KTE“ и „feature“ от всяка клетка в колона B, както е показано на екранната снимка по-долу. Можете да опитате един от следните методи, за да го направите.

3.2.1 Извличане на текст между две думи с формула

Можете да използвате формула, базирана на функцията MID и функцията SEARCH, за да извлечете всички текстови низове между две думи в клетка.

Обща формула

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

Аргументи

Изберете празна клетка, копирайте или въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да приложите тази формула към други клетки.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Забележка: Във формулата числото 3 представлява дължината на знака на думата "KTE"; числото 4 представлява дължината на знака на думата "KTE" плюс 1.

Можете да видите всички текстови низове между посочените две думи са извлечени от всяка клетка в колона B.

3.2.2 Извлечете текст между две думи с невероятен инструмент

За много потребители на Excel формулите може да са трудни за запомняне и работа. Тук, с Извличане на низове между определен текст функция на Kutools за Excel, можете лесно да извлечете текст между две думи само с няколко кликвания.

1. Изберете клетка, за да изведете резултата, и след това щракнете Kutools > Помощник за формула > Помощник за формула.

2. В Помощник за формула диалогов прозорец, трябва да конфигурирате както следва.

3. След това се извличат всички текстови низове между две думи „KTE“ и „feature“ в клетка B5. Междувременно е създадена формула, можете да изберете тази клетка с резултати и след това да плъзнете манипулатора й за автоматично попълване надолу, за да извлечете текстове от други клетки в същия списък.

---

Извличане на числа по позиция

За списък от буквено-цифрови низове може да има три случая:

1. Номерът е в началото на текста;
2. Номерът е в края на текста;
3. Номерът може да бъде навсякъде в текста.

В този раздел ще предоставим различни методи, които могат да се използват за извличане на числата във всеки случай, споменат по-горе.

1 Извличане на число отляво на низ

Тази част ще въведе формула, която да ви помогне да извлечете само числа, които се появяват преди текст в клетка.

Обща формула

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

Аргументи

Забележка:

Изберете празна клетка, въведете формулата по-долу и натиснете Ctrl + Превключване + Въведете or Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да получите номера на други клетки.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

Забележки:

---

2 Извличане на число отдясно на низ

Както е показано на екранната снимка по-долу, за да извлечете само числата, които се появяват след текст в клетка, опитайте следната формула.

Обща формула

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

Аргументи

Забележка:

Изберете празна клетка, въведете формулата по-долу и натиснете Ctrl + Превключване + Въведете or Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да получите номера на други клетки.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

Забележки:

---

3. Извличане на всички числа от всяка позиция в текстов низ

Методите по-горе помагат да се извлече число само отляво или отдясно на текстов низ. Ако искате да извлечете всички числа от всяко място в текстов низ, тук ви предоставяме три метода, за да го направите.

3.1 Извлечете всички числа от всяко място в низ с формула

Можете да приложите следната формула, за да извлечете всички числа от всяко място в текстов низ в Excel.

1. Изберете празна клетка, копирайте или въведете формулата по -долу и натиснете Въведете ключ за получаване на всички числа от клетка B5.

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. Изберете клетката с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да получите всички номера на други клетки.

3.2 Извлечете всички числа от всяко място в низ с VBA

Формулата по-горе е твърде дълга и сложна за много потребители на Excel. Всъщност можете да стартирате VBA скрипт, за да автоматизирате задачата в Excel. Можете да направите както следва.

1. Натисни Друг + F11 за да отворите Microsoft Visual Basic за приложения прозорец.

2. В откриването Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модул. След това копирайте VBA по-долу в прозореца с код на модула.

VBA код: Извлечете всички числа от всяко място в текстов низ

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. Натисни F5 ключ за изпълнение на кода. В откриването Kutools за Excel изберете диапазона от клетки, където искате да извлечете всички числа от всяка клетка, след което щракнете върху OK бутон.

4. След това още един Kutools за Excel изскача диалогов прозорец. В този диалогов прозорец изберете целева клетка и щракнете OK.

След това всички числа се извличат групово от всяка клетка в избрания диапазон.

---

4. Извличане на числа след определен текст

Както е показано на екранната снимка по-долу, за да извлечете произволни числа след конкретния текст „No.“, този раздел предоставя два метода, които да ви помогнат да го направите.

4.1 Извличане на числа след определен текст с формула

Можете да приложите следната формула, за да извлечете числа след определен текст в клетка в Excel.

Обща формула:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

Аргументи

Изберете празна клетка, копирайте или въведете формулата по-долу и натиснете Въведете ключ за получаване на резултата. Изберете тази клетка с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да приложите тази формула към други клетки.

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

Забележки:

4.2 Извличане на числа след определен текст с дефинирана от потребителя функция

Следната дефинирана от потребителя функция също може да помогне за извличане на числа след конкретен текст в клетка. Моля, направете следното.

1. Натисни Друг + F11 за да отворите Microsoft Visual Basic за приложения прозорец.

2. В Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модул, и след това копирайте VBA кода по-долу в прозореца Module code.

VBA код: извличане на числа след определен текст в клетка

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.

4. Изберете клетка, въведете формулата по-долу и натиснете Въведете ключ. Изберете тази клетка с резултати и след това плъзнете манипулатора й за автоматично попълване надолу, за да приложите тази формула към други клетки.

=GetNumberAfterTheChar(B5,"No. ")

Забележки:

---

Още по темата: