- HubPages»
- Technology»
- Computers & Software»
- Computer Software
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 thisCSV 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...
- Copy the column and paste it in an empty Ms Word document
- Apply bullets or numbering (any style)
- Suppress bullets or numbering; the leading spaces disappeared
- Bring the data back to Excel
Resources
- Delete CSV Spaces in a Flash
Learn 5 Ways to Clean Undesired Leading and Trailing CSV Spaces, No Need to Be a Coder - CSV Comma Separated Value File Format - How To - Creativyst - Explored,Designed,Delivered.(sm)
Comma Separated Values (CSV) File format - Creativyst, Inc. Docs - How to: parse and convert CSV files to XML. by John Repici - Office Space: Using the Text to Columns Feature in Microsoft Excel
Twice-weekly column that offers tips and tricks for scripting Microsoft Office applications.