Excel OfficeExcel comes with multiple Lookup and Reference functions that let you find matching values. The Lookup and Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets
To get the last relative position (i.e. last row, last column) for text data (with or without empty cells), you can use the MATCH function. See example below: Formula =MATCH(bigtext,range) Explanation In the example shown, the formula in E5 is: =MATCH(REPT(“z”,255),B4:B11) How this formula works This formula uses the MATCH function in approximate match mode to locate… read more »
To split text at an arbitrary delimiter (comma, space, pipe, etc.) you can use a formula based on the TRIM, MID, SUBSTITUTE, REPT, and LEN functions. Formula =TRIM(MID(SUBSTITUTE(A1,delim,REPT(” “,LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1))) Explanation In the example shown, the formula in C5 is: =TRIM(MID(SUBSTITUTE($B5,”|”,REPT(” “,LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5))) Note: references to B5 and C4 are mixed references to allow the formula to be copied… read more »
To pad text to an equal length using another character, you can use a formula based on the REPT and LEN functions. Formula =A1&REPT(“*”,count-LEN(A1)) Explanation In the example shown, a formula is used to append a variable number of asterisks (*) to values in column B so that the final result is always 12 characters in… read more »
To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions. Formula =TRIM(RIGHT(SUBSTITUTE(text,” “,REPT(” “,100)),100)) Explanation In the example shown, the formula in C6 is: =TRIM(RIGHT(SUBSTITUTE(B6,” “,REPT(” “,100)),100)) Which returns the word “time”. How this formula works This formula is an interesting example… read more »
To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions. Formula =TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(” “,200)),200)) Note: 200 is an arbitrary number that represents the longest line you expect to find in a cell. If you have longer lines, increase this number as needed. Explanation In… read more »
To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user name from a cell that contains other text. Formula =TRIM(LEFT(SUBSTITUTE(MID(text,FIND(“@”,txt),LEN(text)),” “,REPT(” “,100)),100)) Note: 100 represents the longest word… read more »
If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 Excel functions: MID, SUBSTITUTE, TRIM, REPT, and LEN. Formula =TRIM(MID(SUBSTITUTE(A1,” “,REPT(” “,LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1))) Explanation How this formula works At the core, this formula takes… read more »
To extract lines from a multi-line cell, you can use a clever (and intimidating) formula that combines 5 Excel functions: SUBSTITUTE, REPT, TRIM, MID, and LEN. Formula =TRIM(MID(SUBSTITUTE(A1,delim,REPT(” “,LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1))) Explanation In the example shown, the formula in D5 is: =TRIM(MID(SUBSTITUTE($C5,CHAR(10),REPT(” “,LEN($C5))), (D$4-1)*LEN($C5)+1, LEN($C5))) How this formula works At the core, this formula looks for… read more »
To display a conditional message, without the IF function, you can use boolean logic and the REPT function. Formula =REPT(“message”,logical test) Explanation In the example shown, the formula in D5 (copied down) is: =REPT(“low”,C5<100) If the value in column C is less than 100, the formula returns “low”. If not, the formula returns an empty string (“”), which… read more »