How to Count Characters in a Cell Including Spaces in Excel – 3 Methods

The dataset showcases movie names.

Method 1 – Using the LEN Function

The LEN function calculates the number of characters in a text string, including spaces, punctuation, and numbers.

• Select a cell to show the count.
• Enter the formula in the cell:
  =LEN(B5)
  Replace B5 with the cell containing characters.
• Press Enter.
  It will return the character count including spaces.
  
• Drag the Fill Handle to apply the formula to the rest of the cells.

This is the output.

Read More: How to Count Specific Characters in a Cell in Excel

Method 2 – Combining Functions to Count Characters in a Range Including Spaces

2.1. Using the LEN Function

• Select a cell to see the count.
• Use the formula in the cell:
  =LEN(B5)+LEN(B6)+LEN(B7)+LEN(B8)+LEN(B9)
  Replace B5, B6, B7, B8, and B9 with the selected cells.
• Press Enter.

This is the output.

Read More: How to Count Characters in Cell without Spaces in Excel

2.2. Combining the SUM and the LEN Function

Use the LEN and the SUM functions.

• Select a cell to see the count.
• Use the formula in the cell:
  =SUM(LEN(B5:B9))
  Replace B5:B9 with the selected cell range.
• Press Enter.

This is the output.

Alternatively, use the SUMPRODUCT function:

• Select a cell to see the count.
• Use the formula in the cell:
  =SUMPRODUCT(LEN(B5:B9))
  Replace B5:B9 with the selected cell range.
• Press Enter.

This is the output.

Method 3 – Counting Characters Excluding Leading & Trailing Spaces

Use the TRIM function and the LEN function.

• Select a cell to show the count.
• Use the formula in the cell:
  =LEN(TRIM(B5))
  Replace B5 with the selected cell.
• Press Enter.
  
• Drag the Fill Handle to apply the formula to the rest of the cells.

This is the output.

Read More: How to Count Space Before Text in Excel

Download Practice Workbook

Frequently Asked Questions

How Do You Count the Occurrences of a Character in a Cell in Excel?

Use the SUBSTITUTE and the LEN function. If the data is in A1 and the character is x, use the formula in any cell: =LEN(A1) – LEN(SUBSTITUTE(A1, “x”, “”))

Press Enter to see the result.

How Do I Count the Number of Spaces in a Cell in Excel?

Enter this formula in any cell and press Enter: =LEN(A1) – LEN(SUBSTITUTE(A1, ” “, “”))

Replace A1 with your data.

What is the Maximum Number of Characters I Can Show in an Excel Cell?

You can show 1024 characters in a cell and it can contain a maximum of 32767 characters. The Formula Bar can show the 32767 characters at the same time.

Related Articles

• Excel VBA: Count Characters in Cell
• How to Count Specific Characters in a Column in Excel
• How to Count Alphabet in Excel Sheet
• How to Count Occurrences of Character in String in Excel

<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel