Hi,
I import data into excel sheet from a data source. The problem is that there are lots of white spaces that gets imported along with the values of the cell. Is there a function/VBA code which will remove all the white spaces from all the cells....
Thanks..
What's your definition of "white space"?
What's the source of the data? (CSV, Text File, Web Page, etc)
Can you describe what you're getting in the cells?
(...spaces? carriage returns? many spaces padded onto the end of each cell? something else?)
I am getting it from a Database...Originally Posted by Ron Coderre
I should have been more clear.. I am getting spaces padded into the end of each cell.
How do I remove them?
Thanks for your reply..
That field in the database is probably "fixed length"....meaning that if the characters don't fill up the field to the proper length, it adds spaces to pad the length.
Perhaps a "helper cell":
If your imported data is in A2:A100
Then (assuming we use Col_B for the "helper column"...
B2: =TRIM(A2)
Copy B2 down as far as you need
THEN....
Select the TRIM formulas
<edit><copy>
Select A2
<edit><paste special>.....Check: Values....Click [OK]
Clean up by clearing the TRIM formulas
Does that help?
Hi Ron,
Thanks for your message. That will help me for sure, but I have these excel files which have 50-60 columns. So is there any way (VBA code) or somethinh which will remove all the extra spaces from those cells...
Thanks
If the only VBA code in the workbook would be to clean up those rows, then maybe this is a workable alternative:
<edit><replace>
Find what: .........That's 2 spaces
Replace with: .......Leave this blank
Click [Replace All]
The end result:
Some cells will have only 1 trailing space, the others will have none.
Acceptable?
I am afraid not. I don't want any extra (trailing spaces) in the cells because I am using a Vlookup function on those cells. Even if there one space it will not match with its lookup value. So basically I need a function which I can execute on the entire excel worksheet which will remove those spaces from each and every cell....
Thanks for your prompt response and help. Really appreciate it...
Here you go:
[Alt]+[F11]............a shortcut to view the Visual Basic Editor (VBE)
Right-click on your workbook name
Select: Insert General Module
On that module....
Paste the below code:
Then......Select the data range on your worksheet
Please Login or Register to view this content.
[Alt]+[F8].......a shortcut for <tools><macro><macros>
Select CleanDataPull.....Click [Run]
Can you work with that?
(Post back if you have more questions)
Awesome, it worked like a charm.....Many thanks....You are great!!!Here you go:
[Alt]+[F11]............a shortcut to view the Visual Basic Editor (VBE)
Right-click on your workbook name
Select: Insert General Module
On that module....
Paste the below code:
Then......Select the data range on your worksheetPlease Login or Register to view this content.
[Alt]+[F8].......a shortcut for <tools><macro><macros>
Select CleanDataPull.....Click [Run]
Can you work with that?
(Post back if you have more questions)
Wow that was awesome. I received a fixed length file from someone today that I needed to do several vlookups on and those nasty extra spaces were giving me STRESS. Now they are gone. Thanks.
I have also thried the soolution. In my file I have arond 300000 lines. So the program is not responding for 40 min.
Hi mohua,
welcome to forum
try add Application.ScreenUpdating = False before FOR loop. It can reduce the time
-If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
-Always upload a workbook before start your question
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
+++ If my answer(s) helped you, please add me reputation by click on * +++
Thanks. I am trying this now
if you have lots of formula inside the cell,
try add this before FOR Loop
Application.Calculation = xlCalculationManual
After FOR Loop
Application.Calculation = xlCalculationAutomatic
It is taking same long time
mohua,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I don't have any calculation in my excel
Hi arlu1201,
My question is same i.e. "Remove White Spaces from all cells". Thats why I am posting in this link. I am just registered to this forum, so not aware of this. PLease let me know for the same question do I need to open another new post???
Your situation might be different from the original poster's situation and hence you may require a solution matching your issue.
Its better you open a new thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks