Display Hidden Spaces and Line Breaks in Excel Cells
Question details
The user wants to identify hidden spaces, tabs, line breaks, and nonbreaking spaces 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.
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.
Show hidden characters with an Excel formula
Use an adjacent cell to display readable labels for common hidden characters without changing the original text.
Click an empty cell next to the text you want to inspect, such as B1.
Enter =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","[space]"),CHAR(9),"[tab]"),CHAR(10),"[line break]"),CHAR(160),"[NBSP]") and press Enter.
Read the result in B1. Each label identifies the corresponding character in A1; [NBSP] indicates a nonbreaking space.

Count a specific hidden character in Excel
Use a counting formula when you need to know how many line breaks or other specified characters a cell contains.
Inspect copied cell text in Word
Use Word's formatting marks to inspect copied text outside the worksheet.
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. Open the workbook: In WPS Spreadsheets, select Open and choose your Excel workbook.
- 2. Select an empty cell: Click an empty cell, such as B1, next to the cell you want to inspect.
- 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. Inspect the result: Read the labels in the formula cell and adjust the A1 reference if your source text is in another cell.

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.




