Determine the position of the first of any of the ten primary digits in an Excel text entry
Hello,
Does Excel (2013) have a command which will search for the position of the first number or digit in a string of text?
The command =SEARCH() will return the position of the first specified character or sequence of characters in a given cell. However, suppose you want to determine the position of the first of any of the ten primary digits (ie 0, 1, 2, 3...9) in a cell. Is there a way to do that?
For example, such a command would return the following results:
5 — for a cell containing "mT z7DL2x Y"
8 — for a cell containing "g DqQj 3vd2AEp"
2 — for a cell containing "y4tKgu"
6 — for a cell containing "tq k8w3a4"
This article has your answer. Note specifically the section that says this (the previous sections are important, too):
Determine the position of the number in the alphanumeric string
Now we determine the position of the number by locating a TRUE value in the result of the decomposed string mentioned in the paragraph above. We employ the MATCH function here. The revised formula now becomes:
=MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0)
Important: You must enter this formula as an array by pressing CTRL+SHIFT+ENTER. If the string is abc123, this formula will yield 4 — that being the position of the first numeric character in the alphanumeric string.
There's more in the article after that, but this is where you can stop. :) Regards, Joe
This article has your answer. Note specifically the section that says this (the previous sections are important, too):
There's more in the article after that, but this is where you can stop. :) Regards, Joe