Forum Discussion
Show special hidden characters in excel
Hi, how do I make excel show hidden characters, such as spaces, line breaks or paragraph breaks, like in Word? Thanks
You can't - Excel doesn't have an option for displaying non-printing characters.
- Thanks, that was a fast and definite answer. so how do you find and replace special characters in excel? It would be nice to have that option.
- You may not have an option for displaying non-printable characters but if you have spaces or line breaks causing problems you can copy and paste them into a cell and point a cell with the formula =CODE(), to it and Excel will return the code for that line break/space/etc so you know what to search for and replace. Just replacing spaces can be much more difficult than it seems due to hidden/non-printable characters. The Trim function removes leading and trailing spaces and replaces multiple spaces between words with a single space as long as your space is the correct type as it doesn't work on non-printable characters or special ASCII characters. I have just literally spent days trying to purge data acquired from several different sources of hidden non-breaking spaces. NBSP.
TRIM will not remove non-breaking spaces, but SUBSTITITE will, if you can use the =CODE() function to find the code for the space that won't go away. I found this video very good but it isn't Microsoft so who knows if they will allow it. https://www.xelplus.com/excel-trim-all-spaces/
=CODE() will return a number and then you can use SUBSTITUTE() to look for incidences of characters with that code and replace with a normal space or nothing at all. Wrapping the SUBSTITUTE function with TRIM() will remove any extra spaces left after replacing bad spaces with good spaces.
=TRIM(SUBSTITUTE(A2,(CHAR(160)),"")) finds the non-breaking spaces and replace with a space and removes any leading or trailing or extra spaces between words.
I used a "|" so I could see where the non-breaking spaces were and so I could remove trailing | but keep the | as a delimiter to split the string where the non-breaking space had been. =IF(RIGHT(E2,1)="|",(LEFT(E2,LEN(E2)-1)),E2)
I'm definitely going to contact the database manager for the data I downloaded and suggest they remove the non-breaking spaces because it has caused me a major headache and I do not think I am the only one. I need these strings to match strings from other databases and the non-breaking space causes matches to fail. If I wasn't using another application I would never have known these were there and trusted the non-matches and missed data. Bad. To find a space, simply enter a space in the 'Find what' box in the Find dialog (Ctrl+F) or Replace dialog (Ctrl+H).
To find a line break, click in the 'Find what' box and press Ctrl+J.
Excel does not have paragraph breaks.
