Removing spaces in cell

[Tom]

Hello,

When importing external data into excel, I end up with 2 blank spaces
before the data. Need to remove the 2 blanks spaces for all the cells
in that column. Changed the format and tried decreasing indent,
neither worked.

Is there a formula or VBA code that would work?

Thanks,
Tom

 

[Roger Govier]

Hi Tom

If you are sure it is always 2 spaces, then in another column enter
=MID(A1,3,255)
and copy down
When finished copy your new column and Paste Special>Values back over
the original data to "fix the data".
The helper column can then be deleted.

Alternatively, if the data doesn't have other spaces within the source
cells you could use
=SUBSTITUTE(A1," ","")
and repeat as above.

 

[Guest]

use the TRIM() function

 

[T. Valko]

Here's a link to a nice little macro that's perfect for this:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Biff

 

[Tom]

Got it working with the macro. Tried the =MID(A1,3,255) but didn't
work for me as needed.

Thanks to all,
Tom