Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Convert text string to valid reference in Excel using Indirect function
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • How to reference named range different sheet in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to use Excel MMULT Function

Data Analysis Examples

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • How To Load Analysis ToolPak in Excel
  • How To Filter Data in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to count table columns in Excel

Data Validation Examples

  • Excel Data validation must contain specific text
  • Excel Data validation with conditional list
  • Data validation must not exist in list
  • Prevent invalid data entering in specific cells
  • Excel Data validation specific characters only

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

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

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

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

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
  • How to extract nth word from text string in excel
  • How to add a line break with a formula while joining text strings in Excel
  • How to Remove Leading Spaces in Excel
  • Join first and last name — Manipulating NAMES in Excel
  • How to compare two text strings in Excel
  • Find Last Day of the Month in Excel
  • How to get Holiday Date from Year in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • Add decimal minutes to time in Excel
  • Convert text timestamp into time in Excel
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annual compound interest schedule in Excel
  • How to calculate compound interest in Excel
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • SLN function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles