2

I received a spreadsheet for work and it has cells that are full of line breaks and excessive whitespace. Unfortunately, I need to export the spreadsheet to CSV format and these two are breaking the format.

Do you have any suggestions as to how I can strip out the garbage and export a valid CSV file?

Improve this question
  • 2
    Did you try Find & Replace? Search for: _ _+ (that's two spaces followed by a plus, meaning "two or more spaces") Replace with: _ (a single space) Options/Regular expressions: YES (checked) Click "Replace All". Do the same thing with \n to replace the line breaks. – Lyrl May 5 '15 at 0:14
  • Can you flesh that out and post it as an answer? – fixer1234 May 5 '15 at 3:35
1

If all the whitespace is regular space characters this should be simple with Find & Replace. Open the Find & Replace window from the Edit menu or by pressing Ctrl+F. In the "Search for" field enter two spaces followed by a plus sign, meaning "two or more spaces": 

_ _ +   (underscore used here to represent a space)

In the "Replace with" field enter a single space. Press the "More Options" button to show additional options at the bottom of the window. Check the option "Regular expresions". Press the button "Replace all".

That should take care of all the whitespace. If it doesn't you may have unusual whitespace characters. You may be able to copy/paste such characters into the "Search for" field to remove them by the same method. (Leave the "Replace with" box blank if you just want to remove characters and not replace them with a space or anything else.)

To replace line breaks with a space, enter \n in the "Search for" field, a single space in the "Replace with" field, again make sure "Regular expressions" is checked, and hit the "Replace all" button.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.