Position of 1st Occurrence of a Number in a cell string
Position of 1st Number in String; Extract String to the Left of 1st Number / Starting from 1st Number.
Cell C2 formula: Returns the Position of First Number in Cell A2 String.
———————————————————————————————–
Cell C2 formula: Returns the position of 1st occurrence of a Number in cell A2 string.
The cell C2 formula Returns zero if there is no number in the cell A2 string.
Enter formula in cell C2 and copy down.
Cell C2 formula:
=MIN(INDEX( IFERROR( FIND({1, 2,3,4, 5,6,7, 8,9,0}, A2), “”),))
Explanation – Cell C2 formula (Non-Array Formula):
1) FIND({1, 2,3, 4,5, 6,7, 8,9,0}, A2): using the FIND function with the find_text argument of numbers 0 to 9 looks for each of these numbers in cell A2 string, and returns an array of numbers and value errors – {4,5, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!} – the numbers represent the position of occurrence of the numbers {1,2,3, 4,5,6,7 ,8,9,0} in the cell A2 string. The 1st number 4 indicates that number 1 occurs at the 4th position in cell A2 string, the 2nd number 5 indicates that the number 2 occurs at the 5th position.
2) IFERROR( FIND({1,2, 3,4, 5,6,7, 8,9,0}, A2), “”): returns an array of numbers and blanks – {4,5, “”,””, “”,””,””, “”,””, “”}- using the IFERROR function returns blanks for #VALUE! errors. INDEX function is used after this to keep the formula as non-array. Using MIN on this returns the smallest number which is the 1st position of occurrence of a number in the cell A2 string.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
Return Position of First number in a cell String.
Cell B2 formula: Returns the position of 1st occurrence of a Number in cell A2 string. This does the same as the cell C2 formula explained above. If cell A2 string has no number, then the formula returns zero. Normal Non-Array formula.
Cell D2 formula: Returns the position of 1st occurrence of a Number in cell A2 string. This does the same as the cell C2 formula explained above. If cell A2 string has no number, then the formula returns string length +1. Normal Non-Array formula.
Extract Sub-String starting from 1st Number in a Cell string till the end.
Cell F2 formula: Cell F2 returns sub-string starting from 1st occurrence of a number in cell A2 string till the end. Enter formula in cell F2 & copy down. Normal Non-Array formula.
Extract Sub-String to the Left of 1st Number in a Cell String.
Cell H2 formula: Cell H2 returns the sub-string preceding (ie. to the Left of) 1st occurrence of a number in cell A2 string. Enter formula in cell H2 and copy down. Normal Non-Array formula.
Cell I2 formula: Cell I2 returns the sub-string preceding (ie. to the Left of) 1st occurrence of a number in cell A2 string. Enter formula in cell I2 and copy down. Normal Non-Array formula.