How to Locate the First Number in a Text String

To locate the first number in a text string, please use MIN function combined with FIND function.

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

Explanations:

- Cell&"0123456789": Text string in the cell combined with numbers from 0 to 9;

– FIND({0,1,2,3,4,5,6,7,8,9},Cell&"0123456789"): To find the location of each number from 0 to 9 in the text string;

- MIN(FIND({0,1,2,3,4,5,6,7,8,9},Cell&"0123456789")): To select the minimum location number for 0 to 9.

Example:

- Text string: abc124;

- abc124&"0123456789" turns to abc1240123456789;

- The minimum location number for all numbers in the text string "abc1240123456789" is 4.

Leave a Reply