ExcelBanter - How to remove the spaces in currency format?

That is what the Selection.Value = Selection.Value line was supposed to
do... and it worked as expected on my test cases (I formatted one cell as
Text, used a leading apostrophe on another and formatted a third as
General... all three converted to real numbers when done). You did select
the column (or, at minimum, all the cells) with the numbers in it before
running the macro, right? Try this variation out and see if it works for
you...

Sub RemoveAllSpaces()
Dim C As Range
Dim Char As String
For Each C In Intersect(Selection, ActiveSheet.UsedRange)
C.NumberFormat = "General"
If C.Value Like "*[ " & Chr(128) & Chr(160) & "]*" Then
C.Value = Replace(C.Value, " ", "")
C.Value = Replace(C.Value, Chr(128), "")
C.Value = Replace(C.Value, Chr(160), "")
End If
C.Value = C.Value
Next
End Sub

And, if not, try it this way instead...

Sub RemoveAllSpaces()
Dim C As Range
Dim Char As String
For Each C In Intersect(Selection, ActiveSheet.UsedRange)
C.NumberFormat = "General"
If C.Value Like "*[ " & Chr(128) & Chr(160) & "]*" Then
C.Value = Replace(C.Value, " ", "")
C.Value = Replace(C.Value, Chr(128), "")
C.Value = Replace(C.Value, Chr(160), "")
End If
If Len(C.Value) Then C.Value = 1 * C.Value
Next
End Sub

Did either of these work for you?

--
Rick (MVP - Excel)

"Robert" wrote in message
...
Rick,
Sorry: I answered a first time from the PC of a colleague... but I'm still
me :-)
I said:
That doesn't work : numbers are still displayed as text. Is it possible to
simulate the multiplication by 1 in each cell?
Thanks again

Robert

"Rick Rothstein" a écrit dans le
message de news: ...
See if this modified macro does what you need (it removes "spaces" if
they are there and then forces the entry to be a real number)...

Sub RemoveAllSpace()
Dim C As Range
Dim Char As String
For Each C In Intersect(Selection, ActiveSheet.UsedRange)
If C.Value Like "*[ " & Chr(128) & Chr(160) & "]*" Then
C.Value = Replace(C.Value, " ", "")
C.Value = Replace(C.Value, Chr(128), "")
C.Value = Replace(C.Value, Chr(160), "")
End If
Next
Selection.NumberFormat = "General"
Selection.Value = Selection.Value
End Sub

As before, select the column of numbers first, then run the macro.

--
Rick (MVP - Excel)

"Robert" wrote in message
...
Thank a lot Rick : That worked!
All the true and false spaces are gone!
But I need a last improvement : All these values which got rid of the
parasitical spaces are stored as text : they are marked with an error
indicator (green trinagle in the upper left corner). An option is
"Convert to number" but it's boring to do that manually. Could the macro
do this last step?
Thanks again!

Robert

"Rick Rothstein" a écrit dans le
message de news: ...
The 160 is what I was assuming it might be... I don't understand why
the 128 was returned by my code. Here is my macro modified to handle
both of those codes plus a normal space, so it should work no matter
what is in your cells. As before, select the entire column with your
"spaced out" numbers and then run this macro...

Sub RemoveAllSpace()
Dim C As Range
Dim Char As String
For Each C In Intersect(Selection, ActiveSheet.UsedRange)
If C.Value Like "*[ " & Chr(128) & Chr(160) & "]*" Then
C.Value = Replace(C.Value, " ", "")
C.Value = Replace(C.Value, Chr(128), "")
C.Value = Replace(C.Value, Chr(160), "")
End If
Next
End Sub

--
Rick (MVP - Excel)

"Robert" wrote in message
...
Rick,

I copied this 'strange space' in a cell and used the =CODE() formula :
it returns the value : 160

Robert

"Rick Rothstein" a écrit dans le
message de news: ...
Select any one cell with one of these "spaced out" numbers in it and
run this macro...

Sub IDtheApparentBlank()
Dim X As Long
For X = 1 To Len(ActiveCell.Value)
If Mid(ActiveCell.Value, X, 1) Like "[!0-9 -+]" Then
MsgBox Asc(Mid(ActiveCell.Value, X, 1))
Exit For
End If
Next
End Sub

What number was displayed in the MessageBox?

--
Rick (MVP - Excel)

"Robert" wrote in message
...
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