REPT function in Excel

Excel 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

How to get last row in text data in Excel

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 »

How to split text with delimiter in Excel

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 »

How to pad text to match equal length in Excel

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 »

How to get last word in a cell in Excel

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 »

How to get last line in cell in Excel

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 »

Extract word that begins with specific character in Excel

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 »

How to extract nth word from text string in excel

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 »

How to extract multiple lines from a cell in Excel

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 »

How to display conditional message with REPT function in Excel

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 »

Sidebar