Delete Leading and Trailing CSV Spaces Quickly

Leading and trailing CSV Spaces are typical unwanted elements in lists; where do they come from?

  • Data copied from a Web site or documents: Word, Pdf, etc
  • Data from a third corporate BI Software report
  • Data compiled by an absent author
  • Data from OCR operations
  • And more…

Fix them manually if they are a few but they usually come in hundreds and thousands.

You don’t need to be a coder to solve this problem; let's delete those CSV Spaces…

Turn this

CSV file with Spaces

Into this...

Clean CSV File

1. Delete CSV Leading and Trailing Spaces from Numbers and Texts

Copy the values from csv file and paste them into an empty sheet; then proceed this way:

1.    Select the column that contains the data and choose “Text To Columns” Command

2.    Choose Delimited in 1/3 step

3.    Choose Comma as the delimiter in 2/3 step

Excel detects commas and split your CSV file into columns; do the following in step 3/3…

1.    Assign the format to each column.

2.    Choose General Format for Number data.

3.    Choose Text Format for Text data.

The spaces in numbers are gone but the leading and trailing spaces in texts need other approach; let’s see…

1. Use Find and Replace Command

Use this command when you have one-word texts or the spaces are regular. Here’s how to do it…

1.    Open the Find and Replace dialog box (CTRL+H).

2.    Type one space in the “Find what:” box.

3.    Press Replace All button.

2. Use TRIM Function

Use this option when you have multi-word texts or the spaces are irregular. See below…

2. Delete All Spaces from Cell Content with TRIM Function

This function deletes all leading, trailing and inter-words spaces from cell content.

The most useful part of this function is that it doesn’t delete the space between words; for example:

  • "    February  " becomes "February"
  • " Joseph      Stiglitz      " turns to "Joseph Stiglitz"


The result is shown below…

3. Clean Leading Spaces in Texts and Numbers with MS Word

Proceed this way...

  1. Copy the column and paste it in an empty Ms Word document
  2. Apply bullets or numbering (any style)
  3. Suppress bullets or numbering; the leading spaces disappeared
  4. Bring the data back to Excel

More by this Author


Comments 2 comments

Oliver 6 years ago

I used Trim() function to clean the leading spaces in my data file. It worked for the first 2307 lines. Strangely it did not work on the rest of the lines. I tried different ways opening a new sheet and using OpenOffice but could not get it work. Thanks to the advise of using MS bullet function to delete the leading space. It works like a magic! It is easy and quick. Thanks for sharing the secret :-)


Johnf117 2 years ago

Informative Site Hello guys here are some links that contains information which you might find useful yourselves. It is Worth Checking out. facegeacdcag

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working