Please see attached video. I am outsourcing data entry and need to "scrub" my data to have a clean excel file to use for organizing data and uploading to google earth. A lot of the data I am getting leaves excess space in the cells causing it to wrap or expand the cell. Please advise if there is a way to handle this aside from going in and deleting spaces from each cell. I have many thousands of rows of date. TRIM doesn't work.



I have also thought about text to columns as a solution to clean everything up.

I appreciate the input.

http://www.screencast.com/t/OE5rzQXYk

You could try

SUBSTITUTE(A1;CHAR(160),"")

If that doesn't work I think you will have to upload a file with actual data.

Interesting link on "cleaning "dirty" data" : http://www.rondebruin.nl/win/s9/win017.htm

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

On the "Substitiute" function, can you explain so I know what cells I am adding? What does the 160 represent? I attached a spreadsheet witha couple of cells with that problem. Thanks

Attached Files

Book1.xlsx (8.0 KB, 7 views)

=TRIM(CLEAN(SUBSTITUTE(LEFT(TRIM(A7),LEN(TRIM(A7))-OR(RIGHT(TRIM(A7))={"?","!","."})),CHAR(160)," ")))

This function was the winner. Holy **** that was cool.

Se the attached file and the following link.

https://support.office.com/en-us/art...c-fe5303222c9d

Attached Files

CHAR160.xlsx (8.7 KB, 8 views)