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 …
What is REPT function in Excel? REPT function is one of TEXT functions in Microsoft Excel that repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string. Syntax of REPT function REPT(text, number_times) The REPT function syntax has the following arguments: Text Required. The text …
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 …
To create a simple in-cell histogram, you can use a formula based on the REPT function. This can be handy when you have straightforward data, and want to avoid the complexity of a separate chart. Formula =REPT(barchar,value/100) Explanation In the example shown, the formula is: =REPT(CHAR(110),C11/100) How this formula works The REPT function simply repeats …
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 …
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 …
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 …
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 …
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 …
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 …
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 …