Extract the Number immediately Preceding Specific Text in a String
Extract the "Number" or "Alphanumeric Sub-string", immediately Preceding Occurrence of a specific text ("no") within a String.
Cell C2 formula: Extract the "Number" or "Alphanumeric Sub-string", immediately Preceding Occurrence of the Text "no" in cell A2 string.
-----------------------------------------------------------------------------------------------
Cell C2 formula: Extract the "Number" or "Alphanumeric Sub-string", immediately Preceding Occurrence of the Text "no" in cell A2 string.
Occurrence of "no" (case-insensitive) within a String means either "no" appears as a separate word or as part of a sub-string ex. "another".
Extract the (i) Number; or (ii) Sub-string that includes a Number; which immediately precedes the occurrence of "no". The Number may consist of a single or multiple digits, and may also include any adjoining non-numerical characters.
The "Number" or "Alphanumeric Sub-string" extracted is which ends with the last numerical preceding 1st occurrence of "no" within the string, and starts from the blank space immediately preceding this numerical. Example: (i) for a string "it is a100%no" the sub-string "a100" is extracted; (ii) for a string "it is 010 times no" the sub-string "010" is extracted.
2 Options: Enter (non-array) formula in cell B2 and copy down; Enter an Array formula (Ctrl+Shift+Enter) in cell C2 and copy down.
Cell C2 formula:
=TRIM(RIGHT( SUBSTITUTE(LEFT(A2, MAX(IFERROR( FIND({0,1,2, 3,4,5, 6,7,8,9}, LEFT(A2, SEARCH("no", A2)-1), ROW($1:$255)),0))), " ", REPT(" ", 255)), 255))
Explanation - Cell C2 formula (Array formula - Ctrl+Shift+Enter):
1) LEFT(A2,SEARCH("no", A2)-1): Extracts sub-string preceding the 1st occurrence of "no" within cell A2 string - returns "67yes 45.5%s".
2) ROW($1:$255): returns an array of consecutive numbers starting from 1 to 255 - {1;2; 3;4;5; 6;7;8;9;... ; 254; 255} - where 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). This is the start_num argument of FIND function and indicates that start numbers for each search are 1 to 255 ie. each search starts from every successive character (1 to 255). Alternatively, the INDIRECT function can be used to determine the precise number of characters in cell A2 string so that the number of searches using the FIND function are limited ie. using "ROW(INDIRECT("1:"& LEN(A2)))" returns the array - {1;2; 3; 4;5; 6;7; 8;9; 10; 11;12; 13; 14;15; 16; 17;18} - where 18 is the length of cell A2 string and using the FIND function will do only 18 searches instead of 255. In this case the final formula can be - TRIM( RIGHT( SUBSTITUTE(LEFT(A2, MAX( IFERROR( FIND({0,1,2, 3,4,5, 6,7, 8,9}, LEFT(A2, SEARCH("no", A2)-1), ROW(INDIRECT("1:"& LEN(A2)))), 0)))," ", REPT(" ", 255)), 255)).
3) FIND({0,1, 2,3, 4,5,6, 7,8,9}, LEFT(A2, SEARCH("no", A2)-1), ROW($1:$255)): this searches for each number (0 to 9) in extracted sub-string of 1) above, with start numbers of 1 to 255. It returns the position of 1st occurrence of each number 0 to 9, and starting from each successive start number of 1 to 255 ensures to search all occurrences of each number (0 to 9). Effectively there are 255 searches with each search starting from the start position of 1, 2, 3, and so on till 255. This will return 255 arrays of 10 values each (for each number 0 to 9), the 1st array which starts search from 1st position is - "#VALUE!, #VALUE!, #VALUE!, #VALUE!, 7,8, 1,2, #VALUE!, #VALUE!", the 2nd array which starts search from 2nd position is - "#VALUE!, #VALUE!,#VALUE!, #VALUE!, 7,8, #VALUE!, 2, #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 number 7 (in 5th position) indicates the position of occurrence of number 4 (its 5th in the array {0,1, 2,3, 4, 5,6, 7, 8,9}) ie. the number 4 appears in the 7th position. The next number 8 (in 6th position) indicates the position of occurrence of number 5 (its 6th in the array {0,1,2, 3, 4,5, 6, 7, 8,9}) ie. the number 5 appears in the 8th position, but the subsequent occurrence of number 5 is not returned in the 1st to 8th arrays where starts search from 2nd to 8th position (which return only the position of occurrence of the 1st 5 which is 8). The 2nd occurrence of number 5 is returned in the 9th array, where search starts from 9th position - "#VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, 10, #VALUE!, #VALUE!, #VALUE!, #VALUE!" - number 10 (in 6th position) indicates the position of 2nd occurrence of number 5 (its 6th in the array {0,1,2,3, 4,5,6, 7,8,9}) ie. the 2nd occurrence of number 5 is in the 10th position. Note: the number 5 occurs in both the 8th position and 10th position of cell A2 string, before the 1st appearance of "no". 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 255. Using IFERROR returns zero for error values. This is the array of numbers for the MAX function used in 4) below which returns the Largest (ie. Last) position of occurrence of a number before the1st occurrence of "no" in cell A2 string.
4) MAX(IFERROR( FIND({0,1, 2,3, 4,5,6, 7,8,9}, LEFT(A2, SEARCH("no", A2)-1), ROW($1:$255)), 0)): this uses the MAX function to return the position of LAST occurrence of any number (0 to 9) in the sub-string preceding the 1st occurrence of "no" within cell A2 string (which is 10) and this becomes the num_chars argument of the LEFT function used in 5) below. The MAX function returns the Largest number in the array of numbers which is the position of Last occurrence of any number (0 to 9) in cell A2 string.
5) LEFT(A2, MAX( IFERROR( FIND({0, 1,2,3, 4,5,6, 7,8,9}, LEFT(A2, SEARCH("no", A2)-1), ROW($1:$255)), 0))): using the LEFT function with the num_chars argument of 4) above returns string upto the last occurrence of a numerical preceding the 1st "no" in cell A2 string - ie returns "67yes 45.5".
6) SUBSTITUTE(LEFT(A2, MAX(IFERROR( FIND({0,1, 2,3, 4,5,6, 7,8,9}, LEFT(A2, SEARCH("no", A2)-1), ROW($1:$255)),0)))," ", REPT(" ", 255)): repeats each blank space in the string extracted in 5) above - "67yes 45.5", 255 times. Using the SUBSTITUTE function replaces each occurrence of a blank space with 255 blank spaces. The new string length will be equivalent to 264 characters (5+255+4).
7) RIGHT(SUBSTITUTE( LEFT(A2, MAX(IFERROR( FIND({0,1, 2,3, 4,5,6, 7,8,9}, LEFT(A2, SEARCH("no", A2)-1), ROW($1:$255)),0)))," ", REPT(" ",255)), 255): using the RIGHT function extracts 255 characters from the right of the string returned in 6) above. It means that the last 255 characters from the above string are extracted (ie. 251 blanks followed by 45.5), which will in every case include the sub-string after last space of string returned in 6) above.
8) TRIM(RIGHT( SUBSTITUTE( LEFT(A2, MAX(IFERROR( FIND({0, 1,2,3, 4,5,6, 7,8,9}, LEFT(A2, SEARCH("no", A2)-1), ROW($1:$255)), 0)))," ", REPT(" ", 255)), 255)): this returns the sub-string after the last blank space in the sub-string of 5) above - returns "45.5". This effectively extracts the alphanumeric sub-string which ends with the last numerical preceding 1st occurrence of "no" within the cell A2 string, and starts from the blank space immediately preceding this numerical. Using the TRIM function removes all leading and trailing blank spaces.
-----------------------------------------------------------------------------
Related Formulas - illustrated in the Ebook of Excel Formulas.
Other formulas:
Cell B2 formula: Extracts the "Number" or "Alphanumeric Sub-string", immediately Preceding Occurrence of the Text "no" in cell A2 string. This formula does the same as cell C2 formula above, except that this uses the AGGREGATE function, may be more efficient, and is a Normal Non-Array formula.
