Import leaves "dirty" cells

CORESTOCK

New Member
Joined
Mar 12, 2002
Messages
1
Importing from Access to Excel leaves blank cells. Only after clearing contents of the individual blank cells will the formulas work...some Excel KB articles refer to dirty cells...what can I do to globally clean the dirty cells.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi
If the cells are "dirty" because they contain spaces, you can remove them by going to
Edit, Replace, in the find-what box enter * then press your spacebar once. The replace-with-what box should be completely blank. You must ensure the box "find entire cells only" is selected. Click Replace All
Hope this helps
regards
Derek
 
Upvote 0
Try running this macro to clean & trim the
data cells

Sub Clean_Trim()
Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CleanTrimRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))
Next


End Sub


Ivan
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top