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

Data Analysis

  • Understanding Anova in Excel
  • Get column name from index in Excel Table
  • Excel Frequency Function Example
  • Use Data Form to input, edit and delete records in Excel
  • Conditional Formatting Color Scales Examples in Excel

References

  • Get nth match with INDEX / MATCH in Excel
  • How to retrieve first match between two ranges in Excel
  • Approximate match with multiple criteria in Excel
  • How to get last column number in range in Excel
  • Count unique text values with criteria

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation must contain specific text
  • Excel Data validation don’t exceed total
  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells

Tag: CHAR function

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

by

What is CHAR function in Excel? CHAR function is one of TEXT functions in Microsoft Excel that returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. Operating environment Character set Macintosh Macintosh character set Windows ANSI The ANSI …

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 use double quotes inside a formula in Excel

by

If you need to include double quotes inside a formula, you can use additional double quotes as “escape characters”. By escaping a character, you are asking Excel to to treat the ” character as literal text. As always, you’ll also need to include double quotes wherever you would normally in a formula. For example, if …

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 find nth occurrence of character in Excel

by

To find the nth occurrence of a character in a text string, you can use a formula based on the FIND and SUBSTITUTE functions. Formula =FIND(CHAR(160),SUBSTITUTE (text,”@”,CHAR(160),N)) Explanation In the example shown, the formula in D5 is: =FIND(CHAR(160),SUBSTITUTE (B5,”@”,CHAR(160),C5)) How this formula works In this example we are looking for the nth occurrence of the …

Continue Reading

Remove unwanted characters in Excel

by

To remove specific unwanted characters in Excel, you can use a formula based on the SUBSTITUTE function. Formula =SUBSTITUTE(B4,CHAR(code),””) Explanation In the example shown, the formula in C4 is: =SUBSTITUTE(B4,CHAR(202),””) Which removes a series of 4 invisible characters at the start of each cell in column B. How this formula works The SUBSTITUTE function can …

Continue Reading

Remove line breaks in a cell in Excel

by

To remove line breaks from a cell, or from text inside a formula, you can use a formula based on the SUBSTITUTE and CHAR functions. Formula =SUBSTITUTE(A1,CHAR(10),”, “) Explanation In the example shown, the formula in C5 is: =SUBSTITUTE(B5,CHAR(10),”, “) which replaces line breaks in B5 with commas. How this formula works First, you should …

Continue Reading

How to add a line break with a formula while joining text strings in Excel

by

To add a line break with a formula, you can use the concatenation operator (&) along with the CHAR function. Note: make sure you have text wrap enabled on cells that contain line breaks. Formula =”text”&CHAR(10)&”text” Explanation In the example shown the formula in E4 is: =B4&CHAR(10)&C4&CHAR(10)&D4 How this formula works This formula “glues together” …

Continue Reading

Add line break based on OS in Excel

by

To add a line break taking into account the current OS (Mac or Windows), you can use the INFO function to test the system and then return the correct break character — CHAR(10) for Windows, CHAR(13) for Mac. Note: make sure you have text wrap enabled on cells that contain line breaks. Formula =IF(INFO(“system”)=”mac”,CHAR(13),CHAR(10)) Explanation The character …

Continue Reading

How to count line breaks in cell in Excel worksheet

by

This tutorial shows how to count line breaks in cell in Excel worksheet using example below. To count total lines in a cell, you can use a formula based on the LEN, SUBSTITUTE, and CHAR functions. Formula =LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),””))+1 Explanation In the example, we are using this formula: =LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),””))+1 How the formula works First, the LEN function counts …

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 OR Function
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • How to use Excel TRUE Function

Date Time

  • Get days before a date in Excel
  • Pad week numbers with zeros in Excel
  • Check If Two Dates are same month in Excel
  • Convert decimal seconds to Excel time
  • Convert time to time zone in Excel

Grouping

  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • If cell contains one of many things in Excel
  • Map inputs to arbitrary values in Excel
  • How to randomly assign data to groups in Excel

General

  • Convert column number to letter in Excel
  • How to calculate percent of goal in Excel
  • How to Delete Cells, Row and Rows in Excel
  • Check if multiple cells have same value in Excel
  • Excel Operators
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning