Forum Discussion
Finding a number in a text string
I have test strings from 3 to 7 characters, one of which is a number 0-9. The number is not in the same place but may be in the first to third position. How can I have a cell report that number’s position? I will later use that position to parse out the characters Before and after that position. Can this be accomplished without going through each numeral say by using the find() function ten times?
2 Replies
As variant, for such texts
text before the number
=LEFT(A2,MATCH(1,--ISNUMBER(--MID(A2,ROW($1:$3),1)),0)-1)and after the number
=RIGHT($A2,LEN($A2)-LEN(B2)-1)Try this one:
=SUM(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1,1),""))..... where A1 is your text string.
On drawback though. If your text string has more than one numerical digit in it, it will not work 😞
