How to Extract Numbers from TextNumber String

To extract numbers from the textnumber string, you need to use Right Function, or check how to extract Text in the TextNumber String.

Formula:

Copy the formula and replace "A1" with the cell name that contains the text you would like to extract.

=RIGHT(A1,LEN(A1)- MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1 & "0123456789"))+1)

Example:

To extract numbers from the textnumber string "abc123".

The result returns "123".

Explanations:

Step 1: To merge the string with numbers from 0 to 9

Formula=A1 & "0123456789"
Resultabc1230123456789

Step 2: To figure out the position of each number in the new string

Position=FIND({0,1,2,3,4,5,6,7,8,9}, A1 & "0123456789")
Position of 07
Position of 14
Position of 25
Position of 36
Position of 411
Position of 512
Position of 613
Position of 714
Position of 815
Position of 916

Step 3: Find the minimum location of each number

Formula=MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1 & "0123456789"))
Result4

Step 4: Extract the numbers from the TextNumber String

Formula=RIGHT(A1,LEN(A1)- MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789"))+1)
Result123

One Response

  1. Reginald Hendricks March 21, 2020

Leave a Reply