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

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

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

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

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