User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Position of Last Occurrence of a Numeric 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 or Last Numeric character in an AlphaNumeric String.

 

 

Cell E2 formula: Returns the Position of Last Numeric in Cell A2 String.

 

 

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

 

 

Position of Last Numeric in a String

           

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

 

 

Cell E2 formula: Returns the Position of 1st Numeric character in the cell A2 Alphanumeric String.  

 

The cell E2 formula Returns zero if there is no number in the cell A2 string.

 

Enter formula in cell E2 and copy down.

 

 

Cell E2 formula: 

  

=IFERROR(MATCH(1E+100, INDEX(--MID(A2, ROW( INDIRECT("1:"& LEN(A2))), 1), )), 0)  

 

   

Explanation - Cell E2 formula (Non-Array Formula):

 

1) ROW( INDIRECT("1:"& LEN(A2))): returns an array of consecutive numbers starting from 1 till 13, where 13 is the length of cell A2 string - {1;2;3; 4;5;6; 7;8;9; 10;11; 12;13}. This is the start_num argument of the MID function used in 2) below.

 

2) MID(A2, ROW( INDIRECT("1:"& LEN(A2))), 1): using the MID function for cell A2 text string with the start_num argument of 1) above and the num_chars argument of 1 returns  an array comprising of each character of cell A2 string  - {"2";"4";"."; "5";"A";"b"; "c";" ";"2"; "8";"/";"1"; "5"}. The MID function uses the num_chars argument as 1 and multiple start_num of 1,2,3,4,..., this extracts a single character starting from each of the 13 positions starting from the 1st position, then single character from the 2nd position, then single character from the 3rd position, and so on and thereby returns each character in the cell A2 string.  Preceding this with double negation ("--") will return a numerical value for each number / numerical and an error value for text characters or blank spaces - {2;4;#VALUE!; 5;#VALUE!; #VALUE!; #VALUE!; #VALUE!;2;8; #VALUE!;1;5}. INDEX function is used to keep the formula as non-array. This is the lookup_array argument of the MATCH function used in 3) below.

 

3) MATCH(1E+100, INDEX(--MID(A2, ROW( INDIRECT("1:"& LEN(A2))), 1),)): using the MATCH function for lookup_value of "1E+100", with the lookup_array argument of 2) above returns the position of the Last number in the lookup_array which is the position of the Last numerical in cell A2 string (ie. returns 13). Using lookup_value of "1E+100" (which is a verly large number in excel) in a MATCH function, where the lookup_array comprises of numbers and error values, and omitting the match_type, will return the position of the last number in the array, ie. 13. If the lookup_value is larger than the largest value in the lookup_array, then the position of the last value is returned, wherein the search starts from the bottom or right, and in this search blanks and errors are ignored. IFERROR is used to return zero where there are all error values in the lookup_array argument, ie. there is no number in cell A2 string, wherein MATCH will return an error value.

  

 

 

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 uses the FIND and MIN excel functions. If cell A2 string has no number, then the formula returns zero. Normal Non-Array formula.

 

Cell C2 formula: Returns the position of 1st occurrence of a Number in cell A2 string. This uses the ROW, INDIRECT & LEN excel functions, alongwith the MID and MATCH functions. If cell A2 string has no number, then the formula returns zero. Normal Non-Array formula.

  

 

 

Return Position of Last Numeric character in the cell A2 Alphanumeric String.  

 

Cell F2 formula: Returns the position of Last occurrence of a Numeric in cell A2 string. This does the same as the cell E2 formula explained above. This uses the ROW, INDIRECT & LEN excel functions, with the MID, ISNUMBER and MATCH functions. If cell A2 string has no number, then the formula returns string length +1. Normal Non-Array formula.

 

Cell G2 formula: Returns the position of Last occurrence of a Numeric in cell A2 string. This does the same as the cell E2 formula explained above. This uses the ROW, INDIRECT & LEN excel functions, with the FIND and AGGREGATE functions. If cell A2 string has no number, then the formula returns string length +1. Normal Non-Array formula.

 

Cell H2 formula: Returns the position of Last occurrence of a Numeric in cell A2 string. This does the same as the cell E2 formula explained above. This uses the ROW, INDIRECT & LEN excel functions, with the FIND and MAX functions. If cell A2 string has no number, then the formula returns string length +1. Array formula (Ctrl+Shift+Enter).

 

 

  

    

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