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