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

Data Analysis

  • Add Outline to Data in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Managing Conditional Formatting Rules in Excel
  • How To Load Analysis ToolPak in Excel

References

  • Count unique text values with criteria
  • How to get last row in numeric data in Excel
  • Extract all partial matches in Excel
  • How to use Excel FORMULATEXT function
  • Extract data with helper column in Excel

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation date in specific year
  • Excel Data validation date in next 30 days
  • Excel Data validation must begin with
  • Excel Data validation exists in list

Tag: REPT function

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 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

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 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

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

  • SWITCH function example in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel

Date Time

  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Get date from day number in Excel
  • Get days before a date in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • Get fiscal year from date in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Group times into unequal buckets in Excel
  • Running count group by n size in Excel
  • How to randomly assign people to groups in Excel

General

  • Common Errors in Excel
  • How to get original price from percentage discount in Excel
  • Convert column number to letter in Excel
  • Hide and Unhide Columns or Rows in Excel
  • Split Cell Content Using Text to Columns in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning