I received an Excel file today from one of my colleagues that contained carriage returns at the end of each line of text, these were displayed as small boxes. They were having trouble splitting out the address using the Text to Columns feature in Excel as it would not recognise the small boxes as any type of delimeter at all.
I had previously come across this before and had identified the character as a carriage return from the original CSV file. The offending character is 13 in ASCII which we can actually search for and replace and/or remove.
It’s relatively easy to do easy to do- see my example below where I have a list of addresses that a seperated by our little boxes.
- Enter the following formula in Column B =SUBSTITUTE(B2,CHAR(13),””)
- Right click the current cells and select copy
- select your first cell
- paste special
- select values
What we have instructed excel to do is replace character 13 in B2 with blank.
The last piece of the simple enough puzzle is to
- Copy your results for column B and
- Paste Special Them as Values in Column A.
There you go little boxes are no more! You can carry on and do all your Text To Columns Magic Wizardry.
Other Magic Excel Tips you might like.
2. Change the standard font in your workbooks
3. Transpose Excel data from rows to columns in a flash!
How do you handle carriage returns in CSV files?
Is your way easier than this- share with everyone else- please leave your feedback.