How to remove the spaces in currency format?
 

Does that space character show up in the formula bar when you select the cell?

Maybe it's due to formatting -- not the real value.

If you use:
=isnumber(a1)
do you see true or false?
(where A1 is a troublesome cell)

Did you get this data from a web site?

If yes, then you could be seeing those non-breaking HTML spaces.

You can clean them up with formulas, but if you have to do it lots of times (or
with lots of data), you may want to use a macro.

David McRitchie has a macro that can help clean this:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)



Robert wrote:

Rick,
Thanks for your quick answer. But that doesn't work:
The InStr(C.Value, " ") function doesn't find the space: obviouly the " "
is not the same that the space in thousand separator in number. The fucntion
always returns 0. :-(
Any idea ?
Thanks again

Robert

"Rick Rothstein" a écrit dans le
message de news: ...
Select the entire column with your "spaced out" numbers and then run this
macro...

Sub RemoveAllSpace()
Dim C As Range
For Each C In Intersect(Selection, ActiveSheet.UsedRange)
If InStr(C.Value, " ") Then C.Value = Replace(C.Value, " ", "")
Next
End Sub

--
Rick (MVP - Excel)


"Robert" wrote in message
...
Hello,

I received a lot of currency data (in euros) but when numbers are greater
than 999, they have a space for the thousands, like ?1 250. So the
numbers greater than 999 are understood by Excel as text. I can remove
manually this space, but the columns are very long ;-(
Is it possible to remove this space with a VBA procedure which will run
all along the selected column ?
Thanks for your help




--

Dave Peterson

How to remove the spaces in currency format?
 

Thanks Dave,
My answers are in the text.
Thanks again

"Dave Peterson" a écrit dans le message de news:
...
Does that space character show up in the formula bar when you select the
cell?

Maybe it's due to formatting -- not the real value.

If you use:
=isnumber(a1)
do you see true or false?
Rob : I get the value FALSE
(where A1 is a troublesome cell)

Did you get this data from a web site?
Rob : I got this data from an accounting software

If yes, then you could be seeing those non-breaking HTML spaces.

You can clean them up with formulas, but if you have to do it lots of
times (or
with lots of data), you may want to use a macro.
Rob : I copied the "strange space" and pasted it in Rick's VBA procedure :
it worked for 75% of the values

David McRitchie has a macro that can help clean this:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)



Robert wrote:

Rick,
Thanks for your quick answer. But that doesn't work:
The InStr(C.Value, " ") function doesn't find the space: obviouly the "
"
is not the same that the space in thousand separator in number. The
fucntion
always returns 0. :-(
Any idea ?
Thanks again

Robert

"Rick Rothstein" a écrit dans le
message de news: ...
Select the entire column with your "spaced out" numbers and then run
this
macro...

Sub RemoveAllSpace()
Dim C As Range
For Each C In Intersect(Selection, ActiveSheet.UsedRange)
If InStr(C.Value, " ") Then C.Value = Replace(C.Value, " ", "")
Next
End Sub

--
Rick (MVP - Excel)


"Robert" wrote in message
...
Hello,

I received a lot of currency data (in euros) but when numbers are
greater
than 999, they have a space for the thousands, like ?1 250. So the
numbers greater than 999 are understood by Excel as text. I can remove
manually this space, but the columns are very long ;-(
Is it possible to remove this space with a VBA procedure which will
run
all along the selected column ?
Thanks for your help




--

Dave Peterson