User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Position the Position of 1st Occurrence of a Number in a cell string

 

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

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.

 

 

-----------------------------------------------------------------------------------------------

 

 

Position of 1st Number in String

           

"Open Image in New Tab" for a full and clear view. 

 

 

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.

  

 

 

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.

 

 

  

    

Only one of the multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.