How to Delete Leading and Trailing CSV Spaces in a Flash

Leading and trailing CSV Spaces are typical intruders in data lists.

Why does this happen?

Corrupted data comes from several sources:

  • Data compiled by a gone author (someone and somewhere)
  • Data copied from a Web site
  • Data copied from other documents: Word, Pdf, etc
  • Data from a third party Software output report
  • Data from OCR output.
  • And more…

A few of these undesired data could be fixed manually but they usually come in hundreds and thousands.

Don’t worry; the solution is easy.

No need to be a coder.

Let’s delete the CSV Spaces from the file shown in graphic 1:

Graphic 1

1. Delete Leading and Trailing Spaces from Numbers and Texts Quickly and Easily

Copy the values from Notepad (CTRL+A) and paste them (CTRL+V) into an empty sheet (graphic 2).

Then…

  1. Select the column A2:A13 and choose Text To Columns Command; the Convert Text to Columns Wizard appears (see graphic 2)
  2. Choose Delimited instead of Fixed Width in 1/3 step
  3. Choose Comma as delimiter in 2/3 step (see graphic 2).

Tip 1: you can open the CSV file directly from Excel; this operation also triggers the same Convert Text to Columns Wizard.

Graphic 2

Excel detects commas and split your CSV file into columns (see graphic 3).

Proceed…

  1. Assign the format to each column in 3/3 step.
  2. Choose General Format for [Gauge Readings] data.
  3. Choose Text Format for [Months] data. See below.

Graphic 3

The spaces of numbers in CSV files are easily deleted by choosing General data format in the 3/3 step.

But…

The leading and trailing spaces in texts data weren’t deleted; see result in graphic 4.

Graphic 4

How to delete them? It is very easy; you have two options:

    1. Use Find and Replace Command. Use this option when you have one word texts.
    1. Use TRIM Function. Use this option when you have multi-word texts (see How to Delete Spaces with TRIM Function).

Let’s review the first procedure (see graphic 5)…

  1. Open the Find and Replace dialog (CTRL+H).
  2. Type one space in the “Find what:” box, see below.
  3. Press Replace All button and done.

Graphic 5

Almost done.

Graphic 6

Use Save As and choose CSV (Comma delimited) format and distribute your cleaned CSV file (see graphic 7).

Graphic 7

2. A Non Orthodox Way to Clean Leading Spaces in Texts and Numbers

Proceed this way…

  1. Copy the column B and paste it in an empty Ms Word document
  2. Select all data with CTRL + A
  3. Apply bullets or numbering (any style)
  4. Suppress bullets or numbering; the spaces are gone
  5. Copy all the data CTRL + A and CTRL + C
  6. Paste it back in column B

3. Delete All Spaces from Cell Content with TRIM Function

This function deletes all spaces from cells except the space between words; for example:

  • ”    January  ” becomes “January”
  • ” Joseph      Stiglitz      ” becomes “Joseph Stiglitz”

Graphic 8

Copy and paste TRIM function down and done; after that, replace the data that contains spaces in column B (copy and paste values).

Graphic 9

4. Delete Leading and Trailing Spaces in Numbers

When your CSV file contains only numbers delete them the following way:

  1. Open the CSV file in Excel
  2. Apply General Format in 3/3 step of the Convert Text to Columns Wizard to each number column (as explained above in case 1)
  3. Save it as CSV

5. Use Fixed Width to Delete Regular Leading and Trailing Spaces

Proceed this way only when the CSV spaces are regular, let’s see…

  1. Choose Fixed Width option in the 1/1 step of Convert Text to Columns Wizard
  2. Split CSV files at the correct places