When you copy and paste data from external sources—like a PDF document, a poorly formatted email, or a proprietary company database—into Google Sheets, the data often contains “invisible” characters. The most frustrating of these is the hidden carriage return (a line break).
You might have a cell containing the text “John Smith,” but no matter what you do, you cannot get a VLOOKUP formula to match it with the “John Smith” in another column. The cells look absolutely identical to the human eye, but the imported cell actually contains “John Smith” followed by a microscopic, invisible line break. Because standard “Find and Replace” tools cannot search for a line break you can’t see, you must use the CODE and CHAR functions to identify and destroy it mathematically.
Step 1: Identify the Invisible Character using CODE
Every single character on a computer keyboard (including invisible formatting commands like spaces, tabs, and line breaks) has a specific numerical ID, known as its ASCII code. The CODE function reveals the hidden number of the first character in a string.
If you suspect cell A1 has a hidden character at the end of the text, you can isolate it.
- Click an empty cell.
- Type this formula to extract the very last character of cell A1 and reveal its true identity:
=CODE(RIGHT(A1, 1))
If this formula spits out the number 10, you have found your culprit. In the ASCII system, the number 10 represents a “Line Feed” (a hard return).
Step 2: Destroy the Invisible Character using CHAR and SUBSTITUTE
Now that you know the exact mathematical ID of the invisible enemy (10), you can use the CHAR function (which converts a number back into its physical character) combined with the SUBSTITUTE function to surgically rip it out of your entire dataset.
Assume your dirty data is in Column A.
- Click on cell B1 (or any empty adjacent column).
- Type the following formula:
=SUBSTITUTE(A1, CHAR(10), "") - Press Enter.
- Drag the formula down to apply it to all your rows.
How the Formula Works
The SUBSTITUTE function scans the text in A1. Whenever it encounters CHAR(10) (the literal invisible line break you identified earlier), it completely deletes it by replacing it with "" (absolute nothingness, not even a space).
The resulting text in Column B is now mathematically pure. Your VLOOKUP formulas, pivot tables, and conditional formatting rules will finally work perfectly, because the hidden formatting ghosts imported from the PDF have been permanently eradicated.