Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Subtotal function in Excel
  • How to create running total in an Excel Table
  • Get column index in Excel Table
  • How To Compare Two Lists in Excel
  • How to count table columns in Excel

References

  • How to reference named range different sheet in Excel
  • How to get address of first cell in range in Excel
  • Find closest match in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Approximate match with multiple criteria in Excel

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation must not contain
  • Excel Data validation allow weekday only
  • Excel Data validation exists in list
  • Excel Data validation must begin with

Tag: REPT function

How to display conditional message with REPT function in Excel

by

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 …

Continue Reading

REPT function: Description, Usage, Syntax, Examples and Explanation

by

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 …

Continue Reading

How to get last row in text data in Excel

by

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 …

Continue Reading

How to create simple in-cell histogram in Excel

by

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 …

Continue Reading

How to extract multiple lines from a cell in Excel

by

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 …

Continue Reading

How to extract nth word from text string in excel

by

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 …

Continue Reading

Extract word that begins with specific character in Excel

by

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 …

Continue Reading

How to get last line in cell in Excel

by

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 …

Continue Reading

How to get last word in a cell in Excel

by

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 …

Continue Reading

How to pad text to match equal length in Excel

by

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 …

Continue Reading

How to split text with delimiter in Excel

by

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 …

Continue Reading

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel AND Function
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • Not Equal To ‘<>‘ operator in Excel

Date Time

  • How to get number of days, weeks, months or years between two dates in Excel
  • Sum race time splits in Excel
  • Generate series of dates by weekends in Excel
  • Get month from date in Excel
  • Roll back weekday to Friday base on a particular date in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel
  • Running count group by n size in Excel
  • Map inputs to arbitrary values in Excel

General

  • How to calculate decrease by percentage in Excel
  • Lock Cells in a Worksheet Excel
  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • How to calculate percent sold in Excel
  • How to set or clear a print area in Excel Worksheet
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning