User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Position of 1st Occurrence of a Text Character in cell string

 

 

Position in AlphaNumeric String of 1st Text Character, Last Text Character, 1st Letter (A-Z), Last Digit Preceding 1st Text Character. 

 

Cell B2 formula: Returns the Position of 1st Text (Non-Numerical) Character in cell A2 String. 

 

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

Position of 1st Text Character in String

    

Cell B2 formula: Returns the position of 1st occurrence of a Text Character (ie. 1st Non-Numerical) in cell A2 string.  

 

The cell B2 formula Returns zero if there is no text character in the cell A2 string.

 

Enter formula in cell B2 and copy down. 

 

Cell B2 formula: 

  

=IFERROR(MATCH( TRUE, INDEX( ISERR(--MID(A2, ROW( INDIRECT("1:"& LEN(A2))), 1)),), 0),0)  

    

Explanation - Cell B2 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 beginning 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}. Using the ISERR function - ISERR(--MID(A2, ROW( INDIRECT("1:"& LEN(A2))), 1)) - returns and array of TRUE / FALSE values where TRUE is for each error value and FALSE for each numerical - {FALSE;FALSE; TRUE;FALSE; TRUE;TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}. 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(TRUE, INDEX(ISERR(--MID(A2, ROW(INDIRECT("1:"& LEN(A2))), 1)), ),0): using the MATCH function for lookup_value of TRUE, with the lookup_array argument of 2) above where TRUE is for each error value and FALSE for each numerical, returns the position of the 1st TRUE (ie. returns 2) which indicates the position of the 1st text character in cell A2 string. IFERROR is used to return zero where there are all FALSE values in the lookup_array argument, ie. there is no text character in cell A2 string, wherein MATCH will return an error value.

  

 

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

 

Related Formulas - illustrated in the Ebook of Excel Formulas.

 

 

Other formulas:

  

Cell C2 formula: Returns Position of Last Text (Non-Numerical) Character in cell A2 String. If cell A2 string has no text character, then the formula returns zero. Uses the ROW, INDIRECT & LEN excel functions with MID, INDEX, ISERR and MATCH functions. Normal Non-Array formula.

 

Cell E2 formula: Returns the position of 1st Letter (A-Z, case-insensitive) in cell A2 String. If cell A2 string has no letter, then the formula returns zero. Uses the ROW, CHAR, SEARCH and AGGREGATE excel functions. Normal Non-Array formula.  

 

Cell G2 / H2 formulas: Return Position of Last Digit / Numerical Preceding 1st Text Character in cell A2 String . Enter formula in cell G2 / H2 & copy down. The cell G2 formula uses the ROW, INDIRECT & LEN excel functions with LEFT and AGGREGATE functions. The cell H2 formula uses the ROW, INDIRECT & LEN excel functions with LEFT and LOOKUP functions. Both are Normal Non-Array formulas.