How to Extract Text in the TextNumber String

To extract the text portion from the textnumber string, you need to use Left Function, or check how to extract Numbers from TextNumber String.

Formula:

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

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

Example:

To extract the text from the text string "abc123".

The result returns "abc".

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 Text from the String

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

Leave a Reply