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" |
|---|---|
| Result | abc1230123456789 |
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 0 | 7 |
| Position of 1 | 4 |
| Position of 2 | 5 |
| Position of 3 | 6 |
| Position of 4 | 11 |
| Position of 5 | 12 |
| Position of 6 | 13 |
| Position of 7 | 14 |
| Position of 8 | 15 |
| Position of 9 | 16 |
Step 3: Find the minimum location of each number
| Formula | =MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1 & "0123456789")) |
|---|---|
| Result | 4 |
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) |
|---|---|
| Result | abc |