Excel tutorial – extract text or number by specific position

In many cases, you may only need to extract the useful content from a sentence or a text string in a cell, such as extracting the province from the address, extracting the email address from a sentence, extracting the courier bill number from a conversation, and so on. This tutorial narrows down the extraction to a specific position in a cell and collects different methods to help extract text or numbers from a cell by specific position in Excel.

Extract text by position

This section gathers common positions where a text can be extracted from a cell and provides corresponding methods to handle them step-by-step. You can browse for more details.

1. Extract the number of characters from left or right

To extract the number of characters from the left or right side of a string, you can try one of the below methods.

1.1 Extract the first or last N characters with formulas

Supposing you have a text string list in column B as shown in the screenshot below, to extract the first 2 characters and the last 2 characters from each string, you can apply the following formulas.

Extract the first N characters from a text string

The LEFT function can help to easily extract the first N characters from a text string in Excel.

Generic formula

=LEFT(text_string,[num_chars])

Arguments

Now you can apply this formula to extract the first 2 characters from cells in column B.

1. Select a blank cell, copy or enter the formula below and press the "Enter" key to get the first result. Select the result cell and drag its AutoFill Handle down to apply the formula to other cells.

=LEFT(B5,2)

Now you have extracted the first 2 characters in each cell of the given range.

Extract the last N characters from a text string

Here we apply the RIGHT function to extract the last N characters from a text string in Excel.

Generic formula

=RIGHT(text_string,[num_chars])

Arguments

Select a blank cell, copy or enter the formula below and press the "Enter" key to get the result. Then select this result cell and drag its AutoFill Handle down to get other results.

=RIGHT(B5,2)

1.2 Extract the first or last N characters with an amazing tool

Although the formulas above are simple, to extract the first or last n characters from a long list of text strings, you still need to drag the AutoFill Handle from top to bottom, which may be a little time-consuming. Here, we recommend "Kutools for Excel"’s" Extract Text" utility to help extract the first or last N characters from a list of text string in bulk.

1. Select the text string list you want to extract text from in advance and click "Kutools" > "Text" > "Extract Text".

2. In the popping up "Extract Text" dialog box, you need to configure as follows.

Note: To make the result dynamic when the text string changes, you can check the" Insert as a formula" box.

3. In the next popping up "Extract Text" dialog box, choose a cell to output the extracted characters and then click "OK".

Then the first N or last N characters are extracted from selected cells in bulk.

Click to know more about this feature.

2. Extract text before or after a certain character/word

To extract text before or after a certain character or word, the different scenarios in this section will meet your needs.

2.1 Extract text before or after the first delimiter (a character)

As shown in the screenshot below, to extract text before or after the first delimiter from each cell in the range B4:B10, you can apply one of the below methods.

2.1.1 Extract text before the first delimiter with formula

Apply a formula based on the LEFT and FIND functions can help to extract text before the first delimiter from a cell. You can follow the steps below to get it done.

Generic formula

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

Arguments

Select a blank cell, copy or enter the formula below into it and press the "Enter" key to get the first result. Select the first result cell and drag its AutoFill Handle down to get the texts before the first delimiter of other cells.

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

2.1.2 Extract text after the first delimiter with formula

The below formula helps to extract text after the first delimiter from a cell in Excel.

Generic formula

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

Arguments

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

2.1.3 Extract text before or after the first delimiter with an amazing tool

Here, we highly recommend the "Extract Text" utility of "Kutools for Excel". With this feature, you can easily extract texts before or after the first delimiter from a range of cells in bulk.

1. Select the range of cells where you want to extract the text, and then click "Kutools" > "Text" > "Extract Text".

2. In the "Extract Text" dialog box, you need to configure as follows.

Note: To make the result dynamic when the text string changes, you can check the Insert as a formula box.

3. Then another "Extract Text" dialog box pops up, choose a cell to output the results and click "OK."

Then texts before or after the first delimiter are extracted from selected cells at once.

To know more about this feature, please visit: Quickly Extract Certain Text From Cells In Excel.

2.2 Extract text before or after the last delimiter (a character)

In the steps above we have learned the methods about extracting text before or after the first delimiter from a cell. As shown in the screenshot below, this section will show you two formulas to extract text before or after the last delimiter from a cell. You can follow the steps below to get it done.

2.2.1 Extract text before the last delimiter with formula

To extract text before the last delimiter from a cell, you can use the SEARCH, LEN and SUBSTITUTE functions inside of the LEFT function.

Generic formula

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

Arguments

Select a cell, enter the formula below and press the "Enter" key to get the result. Select this result cell and drag its AutoFill Handle down to extract the texts from other text strings in the same column.

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

2.2.2 Extract text after the last delimiter with formula

After extracting text before the last delimiter from a cell, you can apply the formula below to extract the text after the last delimiter as you need.

Generic formula

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

Arguments

Select a cell, enter the formula below and press the "Enter" key to get the result. Select this result cell and drag its AutoFill Handle down to extract the texts from other text strings in the same column.

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

2.3 Extract text after the nth character

Look at the example below, there is a list of text string in the range B4:B10, to extract the text after the third character from each cell, you can apply a formula based on the MID function and the LEN function.

Generic formula

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

Arguments

Select a blank cell, copy or enter the formula below into it and press the "Enter" key to get the result. Select this result cell and drag its AutoFill Handle down to get other results.

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

2.4 Extract the nth word from a text string

Supposing you have a list of text string as shown in the screenshot below, and want to extract only the nth word from the text string, this section provides three methods for you to get it done.

2.4.1 Extract the nth word with formula

You can combine the TRIM, MID, SUBSTITUTE, REPT and LEN functions to extract the nth word from a text string in a cell.

Generic formula

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

Arguments

In this case, the range B5:B10 contains the text strings, D5:D10 contains the numbers representing the nth word, let’s apply this formula to extract the nth word from text string.

Select a blank cell, copy or enter the formula below into it and press the "Enter" key to get the first result. Select this result cell and drag its AutoFill Handle down to get the nth word of other cells.

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

Note: You can directly type the nth number in the formula as follows.

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

2.4.2 Extract the nth word with User-defined function

Apart from the above formula, you can also apply a user-defined function to extract the nth word from a cell in Excel.

1. Press the "Alt" + "F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. In the "Microsoft Visual Basic for Applications" window, click "Insert" > "Module," and then copy the VBA below into the Code window.

VBA code: Extract the nth word from a text string in a cell

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. Press the "Alt" + "Q" keys to close the "Microsoft Visual Basic for Applications" window.

4. Go back to the worksheet containing the text strings which you want to extract the nth word from. Select a blank cell, copy or enter the formula below into it and press the "Enter" key to get the nth word.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Note: in the formula, D5 is the cell that contains a number representing the nth word. Alternatively, you can directly replace the cell reference with a number.

5. Select the result cell and drag its AutoFill Handle down to extract the nth word from the text strings of other cells.

2.4.3 Extract the nth word with an amazing tool

If you don’t want to manually apply a formula or a user-defined function provided above, here, we recommend the "Extract the nth word in cell" utility of Kutools for Excel. With this feature, you can easily extract the nth word from a text string in a cell with a few clicks only.

1. Select a cell to place the result, and click "Kutools" > "Formula Helper" > "Text" > "Extract" "the nth word in cell". See screenshot:

2. In the "Formulas Helper" dialog box, you need to configure as follows.

3. Then the nth (second) word is extracted from the text string in the cell B5, and you can see a formula is created at the same time. Select this result cell and drag its AutoFill Handle down to get the nth word from other text strings.

Click to know more about this feature.

2.5 Extract text before or after the nth occurrence of a delimiter

Supposing you have a list of text strings as shown in the screenshot below. To extract the text before or after the second occurrence of a space, this section provides two formulas to help you get it done.

2.5.1 Extract text before the nth occurrence of a delimiter

You can use the LEFT function along with the SUBSTITUTE and FIND functions to extract text before the nth occurrence of a delimiter from a cell in Excel.

Generic formula

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

Arguments

Select a cell, copy or enter the formula below and press the "Enter" key to get the result. Select this result cell and drag it AutoFill Handle down to get other results in the list.

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

Note: In the formula, B5 is the cell containing the text string from which you want to extract the text; “ ” here represents a space and the number 2 represents the second occurrence of a space. You can change them according to your needs.

2.5.2 Extract text after the nth occurrence of a delimiter

To extract text after the nth occurrence of a delimiter, you can apply the RIGHT function with the SUBSTITUTE, LEN and FIND functions.

Generic formula

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

Arguments

Now you can apply this formula to extract the text after the second occurrence of the space from each cell in range B5:B10 as follows.

Select a cell, enter the formula below and press the "Enter" key to get the result. Select this result cell and drag it AutoFill Handle down to get other results.

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

2.6 Extract text before or after line break

Suppose you have an order list in column B and you want to extract only the date part and the product No. part from each cell. You can use the below Excel formulas to get it done.

2.6.1 Extract text before the first line break with formula

As you can see in the screenshot above, the date part locates before the first line break within the cell. This section will demonstrate a LEFT function along with the SEARCH function to help you extract the text before the first line break within a cell.

Generic formula

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

Arguments

Select a blank cell, copy or enter the formula below into it and press the "Enter" key to get the result. Select this result cell and then drag its AutoFill Handle down to apply this formula to other cells.

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

Then you can see the text before the first line break in each cell in range B5:B8 is extracted as shown in the screenshot below.

Note: In the formula, CHAR(10) represents a line break on Windows.

2.6.2 Extract text after the last line break with formula

In the previous step, we talked about how to extract text before the first line break within a cell. And this part will guide you how to extract text after the last line break within a cell with a different formula.

Generic formula

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

Arguments

Select a blank cell, enter the formula below and press the "Enter" key to get the result. Select this result cell and then drag its AutoFill Handle down to apply the formula to other cells.

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

Then the product no. part of each cell in the list is extracted as shown in the screenshot above.

Note: In the formula, CHAR(10) represents a line break on Windows.

  2.7 Extract text before or after a word

In the previous parts, we have learned how to extract text before or after a character or delimiter. What should you do to extract text before or after an entire word? This section will introduce three methods to help you accomplish this task.

2.7.1 Extract text before a certain word with formula

The following formula helps you extract text before a certain word within a cell in Excel.

Generic formula

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

Arguments

Select a blank cell, enter the formula below and press the "Enter" key to get the result. Select this result cell and then drag its AutoFill Handle down to apply this formula to other cells.

In this example, we are going to extract all texts before the word “Excel”, so we directly type the word in the formula and enclose it in double quotes. Or you can reference to a cell that contains the word “Excel”.

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

Notes:

2.7.2 Extract text after a certain word with formula

To extract text after a certain word, you can apply the following formula to get it done.

Generic formula

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

Arguments

Select a cell, enter the formula below and press the "Enter" key to get the result. Select the result cell and then drag its AutoFill Handle down to apply this formula to other cells.

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

Then you can see all texts after the word “Excel” in each cell are extracted as shown in the screenshot below.

Notes:

2.7.3 Extract text before or after a certain word with an amazing tool

If you feel that using formula may cause a lot of inconvenience, here, we highly recommend the "Extract Text" utility of "Kutools for Excel". This feature helps to automate the Extraction task in Excel with just a few clicks.

1. Click "Kutools" > "Text" > "Extract Text" to enable this feature.

2. In the "Extract Text" dialog box, do the following settings.

2.1) Make sure that you are in the "Extract by location" tab;

2.2) In the "Range" box, click the button to select the range of cells where you want to extract texts;

2.3) In the "Options" section:

To extract all texts before a word, choose the "before the text" radio button and then enter the word into the text box;

To extract all texts after a word, choose the "after the text" radio button and then enter the word into the text box.

2.4) Click the "OK" button. See screenshot:

Notes: If you want to create dynamic results, check the "Insert as a formula" box. Then the results will automatically update when the data in the range changes.

3. Then an "Extract Text" dialog box pops up, you need to select a cell to output the result and then click the "OK" button.

Then texts before or after a certain word within each cell in the selected range are extracted immediately.

Note: This feature is case-sensitive.

Click to know more about this feature.

3. Extract between characters/words

If you want to extract text between certain characters or words, try the following methods.

3.1 Extract text between two characters

To extract text between two characters, which may be the same or different. This section provides several methods, and you can choose the one that suits your needs.

 3.1.1 Extract text between two same characters with formula

As shown in the screenshot below, there is a list of text strings in column B, and you want to extract the number part between characters “/” from each cell in the range, the following formula can do you a favor.

Select a blank cell, copy or enter the formula below, and press the "Enter" key to get the result. Select the result cell and then drag the AutoFill Handle down to get results for other cells in the list.

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

Then the text between two same characters “/” is extracted from each cell in the range. See screenshot:

Note:

 3.1.2 Extract text between two different characters with formula

After learning how to extract text between two same characters within a cell, here we will demonstrate a formula to extract text between two different characters. As shown in the screenshot below, to extract only the email address between the “<” and “>” from each cell in column B, you can do as follows.

Generic formula

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

Arguments

Select a blank cell, copy or enter the formula below and press the "Enter" key to get the result. Select this result cell and then drag its AutoFill Handle down to apply this formula to other cells.

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

You can see that only the text between the specified characters is extracted as shown in the screenshot above.

3.1.3 Extract text between two characters with an amazing tool

Here, we highly recommend the "Extract strings between specified text" feature of "Kutools for Excel" to help you easily extract text between two same or different characters within a cell in Excel.

1. Select a blank cell to output the result, and then click "Kutools" > "Formula Helper" > "Formula Helper".

2. In the "Formulas Helper" dialog box, do the following settings.

3. Then only the text between “<” and “>” within the cell B5 is extracted. Meanwhile, a formula has been created, you can select this result cell and then drag its AutoFill Handle down to extract texts from other cells in the same list.

Click to know more about this feature.

3.1.4 Extract text between two characters (including the characters) by rule

If you want to keep the two characters after extracting, try applying a rule in the Extract Text feature of Kutools for Excel.

1. Click "Kutools" > "Text" > "Extract Text".

2. In the "Extract Text" dialog box, do the following settings.

2.1) Click the "Extract by rule" tab;

2.2) In the "Range" section, click the button to select the range of cells where you want to extract the text between characters;

2.3) In the "Text" box, enter "<*>"；

Tips: "<" and ">" are the characters you want to extract text between them, * is a wildcard that represents any number of characters. You can enter the condition according to your needs.

2.4) Click the "Add" button to add the condition to the "Rule description" list box;

2.5) Click the "Ok" button. See screenshot:

3. Another "Extract Text" dialog box pops up. Please select a cell to output the result, and then click the "OK" button.

Then, the text between specified characters (including the characters) is extracted from each cell in the selected range in bulk.

3.2 Extract text between two words

In addition to extracting text between two characters, you may also need to extract text between two words. For example, extract all text strings between two words “KTE” and “feature” from each cell in column B as shown in the screenshot below. You can try one of the following methods to get it done.

3.2.1 Extract text between two words with formula

You can use a formula based on the MID function and the SEARCH function to extract all text strings between two words within a cell.

Generic formula

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

Arguments

Select a blank cell, copy or enter the formula below and press the "Enter" key to get the result. Select this result cell and then drag its AutoFill Handle down to apply this formula to other cells.

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

Note: In the formula, the number 3 represents the character length of the word "KTE"; the number 4 represents the character length of the word "KTE" plus 1.

You can see that all text strings between the specified two words are extracted from each cell in column B.

3.2.2 Extract text between two words with an amazing tool

For many Excel users, formulas may be hard to remember and handle. Here, with the "Extract strings between specified text " feature of "Kutools for Excel", you can easily extract text between two words with just a few clicks.

1. Select a cell to output the result, and then click "Kutools" > "Formula Helper" > "Formula Helper."

2. In the "Formula Helper" dialog box, you need to configure as follows.

3. Then all text strings between two words “KTE” and “feature” within cell B5 are extracted. Meanwhile, a formula has been created, you can select this result cell and then drag its AutoFill Handle down to extract texts from other cells in the same list.

Extract numbers by position

For a list of alphanumeric strings, there may be three cases:

1. The number is at the beginning of the text;
2. The number is at the end of the text;
3. The number can be anywhere in the text.

In this section, we will provide different methods that can be used to extract the numbers in each case that mentioned above.

1 Extract number from the left of a string

This part will introduce a formula to help you extract only numbers that appear before text in a cell.

Generic formula

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

Arguments

Note:

Select a blank cell, enter the formula below and press "Ctrl" + "Shift" + "Enter" or "Enter" key to get the result. Select this result cell and then drag its AutoFill Handle down to get numbers of other cells.

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

Notes:

2 Extract number from the right of a string

As shown in the screenshot below, to extract only the numbers that appear after text in a cell, try the following formula.

Generic formula

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

Arguments

Note:

Select a blank cell, enter the formula below and press "Ctrl" + "Shift" + "Enter" or "Enter" key to get the result. Select this result cell and then drag its AutoFill Handle down to get numbers of other cells.

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

Notes:

3. Extract all numbers from any position in a text string

The methods above help to extract number only from the left or right of a text string. If you want to extract all numbers from anywhere in a text string, here we provide three methods for you to get it done.

3.1 Extract all numbers from anywhere in a string with formula

You can apply the following formula to extract all numbers from anywhere in a text string in Excel.

1. Select a blank cell, copy or enter the formula below and press the "Enter" key to get all numbers from the cell 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. Select the result cell and then drag its AutoFill Handle down to get all numbers of other cells.

3.2 Extract all numbers from anywhere in a string with VBA

The formula above is too long and complicated for many Excel users. Actually, you can run a VBA script to automate the task in Excel. You can do as follows.

1. Press the "Alt" + "F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. In the opening" Microsoft Visual Basic for Applications" window, click "Insert" > "Module." Then copy the VBA below into the Module code window.

VBA code: Extract all numbers from anywhere in a text string

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. Press the "F5" key to run the code. In the opening "KutoolsforExcel" dialog box, select the range of cells where you want to extract all numbers from each cell, and then click the "OK" button.

4. Then another "KutoolsforExcel" dialog box pops up. In this dialog box, select a destination cell and click "OK."

Then all numbers are extracted from each cell in the selected range in bulk.

4. Extract numbers after specific text

As shown in the screenshot below, to extract any numbers after the specific text “No.”, this section provides two methods to help you get it done.

4.1 Extract numbers after a specific text with formula

You can apply the following formula to extract numbers after a specific text in a cell in Excel.

Generic formula:

=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}))

Arguments

Select a blank cell, copy or enter the formula below and press the "Enter" key to get the result. Select this result cell and then drag its AutoFill Handle down to apply this formula to other cells.

=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}))

Notes:

4.2 Extract numbers after a specific text with user-defined function

The following user-defined function can also help to extract numbers after a specific text in a cell. Please do as follows.

1. Press the "Alt" + "F11" keys to open the" Microsoft Visual Basic for Applications" window.

2. In the "Microsoft Visual Basic for Applications" window, click "Insert" > "Module," and then copy the VBA code below into the Module code window.

VBA code: extract numbers after a specific text in a cell

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. Press the "Alt" + "Q" keys to close the "Microsoft Visual Basic for Applications" window.

4. Select a cell, enter the formula below and press the "Enter" key. Select this result cell and then drag its AutoFill Handle down to apply this formula to other cells.

=GetNumberAfterTheChar(B5,"No. ")

Notes:

Related articles: