Extract Sub-String After Last Occurrence of a Number / Numerical in a String
Extract All Characters starting After the Last occurrence of a Number (ie. 0 to 9) till the end.
Cell B2 formula: Extracts all Text starting After and to the Right of the Last occurrence of a Number (ie. 0 to 9) in cell A2 string.
———————————————————————————————–
Cell B2 formula: Extracts all Text starting After and to the Right of the Last occurrence of a Number (ie. 0 to 9) in cell A2 string.
Enter formula in cell B2 and copy down.
Cell B2 formula:
=TRIM(MID(A2, MAX(IFERROR( FIND({0, 1,2,3, 4,5,6, 7,8,9}, A2, ROW( INDIRECT(“1:”& LEN(A2)))), 0))+1, LEN(A2)))
Explanation – Cell B2 formula (Array formula, Ctrl+Shift+Enter):
1) ROW( INDIRECT(“1:”& LEN(A2))): returns an array of consecutive numbers starting from 1 to 24 – {1;2; 3;4;5; 6;7; 8;9; 10;11; 12;13; 14; 15;16; 17; 18;19; 20; 21;22; 23;24} – where 24 is the number of characters of cell A2 string. This is the start_num argument of FIND function and indicates that start numbers for each search are 1 to 24 ie. each search starts from every successive character (1 to 24). Alternatively, “ROW($1:$255)” can be used, which returns an array of consecutive numbers starting from 1 to 255 – The value of 255 is used presuming cell A2 string length will be within 255 characters (cell Column width maximum limit is 255 characters in Excel).
2) FIND({0,1,2,3, 4,5,6, 7,8,9}, A2, ROW( INDIRECT(“1:”& LEN(A2)))): this searches for each number (0 to 9) in cell A2 string, with start numbers of 1 to 24 (cell A2 string length is 24). It returns the position of occurrence of each number 0 to 9, and starting from each successive start number of 1 to 24 ensures to search all occurrences of each number (0 to 9). Effectively there are 24 searches with each search starting from the start position of 1, 2, 3, and so on till 24. This will return 24 arrays of 10 values each (for each number 0 to 9), the 1st array which starts search from 1st position is – “1, #VALUE!,2, #VALUE!, 14,15, 3,4, #VALUE!, #VALUE!”, the 2nd array which starts search from 2nd position is – “23,#VALUE!, 2, #VALUE!,14, 15,3,4,#VALUE!, #VALUE!”, and so on. The aim is to search all occurrences of each number (0 to 9), for example in the 1st array the 1 indicates the position of occurrence of number 0 but the subsequent occurrence of number 0 is not returned in the 1st array – the next occurrence of number 0 is returned in the 2nd array which is 23 (Note: the number 0 occurs in both the 1st position and 23rd position of cell A2 string). This enables calculation of LAST position of occurrence of any number (0 to 9) because all occurrences of each number 0 to 9 are returned by having start_num argument as 1 to 24. IFERROR function is used to return zero for error values and then using the MAX function returns the position of LAST occurrence of any number in cell A2 string (which is 23) and with 1 added to this becomes the start_num argument of the MID function used in 3) below.
3) MID(A2, MAX( IFERROR( FIND({0,1,2, 3,4,5, 6,7,8,9}, A2, ROW( INDIRECT(“1:”& LEN(A2)))), 0))+1, LEN(A2)): uses the MID function with cell A2 string as the text argument, and the start_num argument of 24 (23 plus 1) as explained in 2) above which is one position after the last occurrence of any number 0 to 9 in cell A2 string, and using “LEN(A2)” as the num_chars argument (exceeds the number of characters to the right of the last occurrence of any number) of the MID function will return the full string till the end after the position of Last occurrence of any number 0 to 9. Using TRIM removes any leading, trailing and extra inbetween spaces.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
Cell C2 formula: Extracts all Text starting After and to the Right of the Last occurrence of a Number (ie. 0 to 9) in cell A2 string. This formula does the same as cell B2 formula above, except that this uses the AGGREGATE function, may be more efficient, and is a Normal Non-Array formula.