Formatting Issues

Display Hidden Spaces and Line Breaks in Excel Cells

Anum MushtaqAnum Mushtaq Nov 18, 2026 870 views

Question details

The user wants to identify hidden spaces, tabs, line breaks, and nonbreaking spaces in Excel cells.

Display Hidden Spaces and Line Breaks in Excel Cells
Product
Microsoft Excel
Device & OS
not provided
Scenario
Inspecting cell contents for nonprinting characters that affect text layout or matching.
Observed behavior
The desired result is a visible representation or count of the nonprinting characters in a cell.
Before you start

Identify the cell you want to inspect and choose an empty cell for a formula. The examples below use A1; replace it with your cell reference.

Solution 1Recommended

Show hidden characters with an Excel formula

Use an adjacent cell to display readable labels for common hidden characters without changing the original text.

1
Select an empty cell

Click an empty cell next to the text you want to inspect, such as B1.

2
Enter the inspection formula

Enter =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","[space]"),CHAR(9),"[tab]"),CHAR(10),"[line break]"),CHAR(160),"[NBSP]") and press Enter.

3
Review the labels

Read the result in B1. Each label identifies the corresponding character in A1; [NBSP] indicates a nonbreaking space.

Show hidden characters with an Excel formula
Scope of the formula: The formula labels the four specified characters in a separate cell. It does not enable a worksheet-wide display of nonprinting characters or identify every possible hidden character.
Inspect cell text in WPS Spreadsheets

Reveal hidden cell characters with WPS Spreadsheets

Open your workbook in WPS Spreadsheets and use a formula to label spaces, tabs, line breaks, and nonbreaking spaces in a separate cell.

  1. 1. Open the workbook: In WPS Spreadsheets, select Open and choose your Excel workbook.
  2. 2. Select an empty cell: Click an empty cell, such as B1, next to the cell you want to inspect.
  3. 3. Enter the formula: Enter =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","[space]"),CHAR(9),"[tab]"),CHAR(10),"[line break]"),CHAR(160),"[NBSP]") and press Enter.
  4. 4. Inspect the result: Read the labels in the formula cell and adjust the A1 reference if your source text is in another cell.
Labels common hidden characters without changing the source cellCompatible with Microsoft Excel XLS and XLSX filesUses familiar spreadsheet formulas and cell referencesAvailable in the free WPS Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel show all nonprinting characters like Word?

Excel does not have a Word-style command that displays all nonprinting characters directly in worksheet cells. Use a formula in an empty cell to label or count specific characters.

How do I count line breaks in a cell?

Enter =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")) in an empty cell, replacing A1 with the cell you want to inspect.

How can I tell whether a cell contains a nonbreaking space?

Enter =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(160),"")) in an empty cell. A result greater than zero indicates that the cell contains at least one nonbreaking space.

Will showing formatting marks in Word change my Excel cell?

No. Copying text into Word and selecting Home > Show/Hide ¶ does not change the original Excel cell. However, the pasted text may not preserve every character or formatting detail exactly.