Hi, I have a table which has been imported from a web query, which has a
column of ID numbers contained in it. Some of these (not all) are not
being picked up as numbers by Excel, and the reason appears to be the
inclusion of a leading nonbreaking space character. There is a way to
clean these up by using the trim and substitute functions in a separate
column, but as I will be refreshing this query reasonably frequently and
the size of the range will change each time, I would like to come up
with a VBA solution to automate removing the leading nonbreaking space.
Any assistance would be welcomed.

Thanks in advance

See this:

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

I also do a lot of stuff from the web and have to deal with all the junk
from html. This macro is a real time saver. I put a button on one of my
toolbars and attached that macro to the button.

Biff

"Geoff Ness" wrote in message
...
Hi, I have a table which has been imported from a web query, which has a
column of ID numbers contained in it. Some of these (not all) are not
being picked up as numbers by Excel, and the reason appears to be the
inclusion of a leading nonbreaking space character. There is a way to
clean these up by using the trim and substitute functions in a separate
column, but as I will be refreshing this query reasonably frequently and
the size of the range will change each time, I would like to come up with
a VBA solution to automate removing the leading nonbreaking space. Any
assistance would be welcomed.

Thanks in advance

Try this

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


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Geoff Ness" wrote in message
...
Hi, I have a table which has been imported from a web query, which has a
column of ID numbers contained in it. Some of these (not all) are not
being picked up as numbers by Excel, and the reason appears to be the
inclusion of a leading nonbreaking space character. There is a way to
clean these up by using the trim and substitute functions in a separate
column, but as I will be refreshing this query reasonably frequently and
the size of the range will change each time, I would like to come up with
a VBA solution to automate removing the leading nonbreaking space. Any
assistance would be welcomed.

Thanks in advance

Thanks Biff! :D

This was exactly what I needed. Great site too.

Cheers
Geoff

T. Valko wrote:
See this:

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

I also do a lot of stuff from the web and have to deal with all the junk
from html. This macro is a real time saver. I put a button on one of my
toolbars and attached that macro to the button.

Biff

"Geoff Ness" wrote in message
...
Hi, I have a table which has been imported from a web query, which has a
column of ID numbers contained in it. Some of these (not all) are not
being picked up as numbers by Excel, and the reason appears to be the
inclusion of a leading nonbreaking space character. There is a way to
clean these up by using the trim and substitute functions in a separate
column, but as I will be refreshing this query reasonably frequently and
the size of the range will change each time, I would like to come up with
a VBA solution to automate removing the leading nonbreaking space. Any
assistance would be welcomed.

Thanks in advance

I think this is proof that great minds think alike ;)

Thanks Peo

Peo Sjoblom wrote:
Try this

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

You're welcome. Thanks for the feedback! And thanks to David M. for posting
that macro!

Biff

"Geoff Ness" wrote in message
...
Thanks Biff! :D

This was exactly what I needed. Great site too.

Cheers
Geoff

T. Valko wrote:
See this:

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

I also do a lot of stuff from the web and have to deal with all the junk
from html. This macro is a real time saver. I put a button on one of my
toolbars and attached that macro to the button.

Biff

"Geoff Ness" wrote in message
...
Hi, I have a table which has been imported from a web query, which has a
column of ID numbers contained in it. Some of these (not all) are not
being picked up as numbers by Excel, and the reason appears to be the
inclusion of a leading nonbreaking space character. There is a way to
clean these up by using the trim and substitute functions in a separate
column, but as I will be refreshing this query reasonably frequently and
the size of the range will change each time, I would like to come up
with a VBA solution to automate removing the leading nonbreaking space.
Any assistance would be welcomed.

Thanks in advance