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:
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).
- Select the column A2:A13 and choose Text To Columns Command; the Convert Text to Columns Wizard appears (see graphic 2)
- Choose Delimited instead of Fixed Width in 1/3 step
- 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.
Excel detects commas and split your CSV file into columns (see graphic 3).
- Assign the format to each column in 3/3 step.
- Choose General Format for [Gauge Readings] data.
- Choose Text Format for [Months] data. See below.
The spaces of numbers in CSV files are easily deleted by choosing General data format in the 3/3 step.
The leading and trailing spaces in texts data weren’t deleted; see result in graphic 4.
How to delete them? It is very easy; you have two options:
- Use Find and Replace Command. Use this option when you have one word texts.
- 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)…
- Open the Find and Replace dialog (CTRL+H).
- Type one space in the “Find what:” box, see below.
- Press Replace All button and done.
Use Save As and choose CSV (Comma delimited) format and distribute your cleaned CSV file (see graphic 7).
2. A Non Orthodox Way to Clean Leading Spaces in Texts and Numbers
Proceed this way…
- Copy the column B and paste it in an empty Ms Word document
- Select all data with CTRL + A
- Apply bullets or numbering (any style)
- Suppress bullets or numbering; the spaces are gone
- Copy all the data CTRL + A and CTRL + C
- Paste it back in column B
This function deletes all spaces from cells except the space between words; for example:
- ” January ” becomes “January”
- ” Joseph Stiglitz ” becomes “Joseph Stiglitz”
Copy and paste TRIM function down and done; after that, replace the data that contains spaces in column B (copy and paste values).
4. Delete Leading and Trailing Spaces in Numbers
When your CSV file contains only numbers delete them the following way:
- Open the CSV file in Excel
- Apply General Format in 3/3 step of the Convert Text to Columns Wizard to each number column (as explained above in case 1)
- 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…
- Choose Fixed Width option in the 1/1 step of Convert Text to Columns Wizard
- Split CSV files at the correct places