Excel remove blanks in columns 1

[emik]

Hi,

I have an Excel sheet which I use to import into Access. I'm running into a problem because in one of the fields there are spaces. Most of the values are ok, but a few instead of being "123" they would be "12 3" (no idea why).

Is there a macro I could write to find the column named "Number" and go through it, removing all the blank spaces?

Thanks

 

[PHV]

Have you tried a Find and Replace on the whole column ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[emik]

I don't think that would help because the criteria would essentially be:

Find " " and replace it with "" ?

but that doesn't find any matches.

 

[emik]

I'm sorry, my mistake. I was putting the quotation marks around 2 empty spaces, but if you just put the empty spaces it fixes it.

I would like it to be a little more flexible in that I don't know if it will always be 2 spaces, or 1 space...etc

but thank you for your reply

 

[PHV]

Worked for me (XL2003)

 

[PHV]

Worked for me (XL2003) without the double quotes

 

[AnotherHiggins]

Turn on your macro recorder (Tools > Macro > Record new macro). Go through the steps of Finding and Replacing one space, then two. Stop recording. Observe the code that was generated ([Alt]+[F11] opens the VB Editor). There's your macro to do what you want!

Post back with any questions.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[emik]

Thanks that was what I was looking for!

You guys have been a great help.

 

[firefytr]

Also check out

http://www.asap-utilities.com,

where you could have used ASAP Utilities | Text | Delete leading and trailing spaces. Lots of useful stuff in that addin.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[AnotherHiggins]

Zack,

It wasn't leading and trailing spaces that he was looking to get rid of, but spaces embedded in the middle.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

Sorry for the speed read. You can then use .. ASAP Utilities | Text | Advanced character removal | Spaces.

The advanced character removal is a great tool, I use it constantly.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci