If you have been using the Trim function for a while to delete the extra spaces between your text, you may have encountered that sometimes the TRIM Just Doesn’t Work! Here is what you do in that case
The Trim Function
If you don’t know about the TRIM here is what it can do. Imagine that you have some names with unwanted spaces in between (and in the start) that you want to get rid of. All you gotta do is to TRIM it baby!!
When the TRIM Function Fails
The Trim function fails to do its job at times when the blanks appear to be spaces but THEY ARE NOT! They are actually non-breaking line feeds
How to detect non breaking line feeds : If the TRIM function fails to remove the spaces most likely you have non breaking feeds. The character code of these feeds is 160 where as space is coded as 32
Use the =SUBSTITUTE function to replace the non breaking line feeds (code 160) with nothing (“”). Here is the formula =SUBSTITUTE(Cell address,CHAR(160),””)
Download the File from Down Below & examine yourself. Also many thanks and credits to DonkeyOte from where I learnt this interesting solution
More How tos in Excel
- How to unhide all sheets at once
- How to turn off the GETPIVOTDATA function in Pivot Tables
- How to get numbers in Words (Indian Currency)
- Quick Alternative for Merging Cells
- How to protect your sheet from unwanted access
