|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work
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 |
#2
|
|||
|
|||
You could try
SUBSTITUTE(A1;CHAR(160),"") If that doesn't work I think you will have to upload a file with actual data. |
#3
|
||||
|
||||
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 |
#4
|
|||
|
|||
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
|
#5
|
|||
|
|||
=TRIM(CLEAN(SUBSTITUTE(LEFT(TRIM(A7),LEN(TRIM(A7))-OR(RIGHT(TRIM(A7))={"?","!","."})),CHAR(160)," ")))
This function was the winner. Holy **** that was cool. Last edited by Pecoflyer; 06-17-2016 at 06:10 AM. Reason: Deleted unappropriate word |
#6
|
|||
|
|||
|
Tags |
extra spaces, trim |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Outlook Calendar goes back to start screen each time I put a new entry in | Exhale | Outlook | 0 | 04-24-2016 02:01 AM |
Adding a carriage return at end of each line, and then a blank space, after text entry complete | CEMurphy4 | Word VBA | 1 | 12-02-2015 11:53 PM |
Copy data from spreadsheets to a two, with the sum criterion | marreco | Excel Programming | 1 | 05-15-2012 04:15 PM |
Help getting lost work back!!! | jcb08j | Word | 0 | 03-06-2012 08:11 AM |
trimming excess image data in Word document | gib65 | Word | 2 | 12-13-2011 07:50 AM |