Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work
Register FAQ

Reply
 
Thread Tools Display Modes
  #1  
Old 06-16-2016, 01:33 PM
jhato160 jhato160 is offline Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Windows 10 Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Office 2016
Novice
Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work 		 
Join Date: Jun 2016
Posts: 13
jhato160 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-16-2016, 08:38 PM
xor xor is offline Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Windows 10 Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
You could try

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

If that doesn't work I think you will have to upload a file with actual data.
Reply With Quote
  #3  
Old 06-17-2016, 02:00 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Windows 7 64bit Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Office 2010 64bit
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,919
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default
Interesting link on "cleaning "dirty" data" : http://www.rondebruin.nl/win/s9/win017.htm
__________________
Using O365 v2503 - 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
Reply With Quote
  #4  
Old 06-17-2016, 03:37 AM
jhato160 jhato160 is offline Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Windows 10 Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Office 2016
Novice
Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work 		 
Join Date: Jun 2016
Posts: 13
jhato160 is on a distinguished road
Default
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
File Type: xlsx Book1.xlsx (8.0 KB, 9 views)
Reply With Quote
  #5  
Old 06-17-2016, 03:51 AM
jhato160 jhato160 is offline Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Windows 10 Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Office 2016
Novice
Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work 		 
Join Date: Jun 2016
Posts: 13
jhato160 is on a distinguished road
Default
=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
Reply With Quote
  #6  
Old 06-17-2016, 04:06 AM
xor xor is offline Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Windows 10 Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
Se the attached file and the following link.

https://support.office.com/en-us/art...c-fe5303222c9d
Attached Files
File Type: xlsx CHAR160.xlsx (8.7 KB, 10 views)
Reply With Quote
Reply

Tags
extra spaces, trim

« Previous Thread | Next Thread »


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
Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work 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
Data Entry - Getting spreadsheets back with excess space but the TRIM function doesn't work trimming excess image data in Word document gib65 Word 2 12-13-2011 07:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:13 AM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft