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
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.
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.