How to remove the spaces in currency format?

[Robert]

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

 

[Rick Rothstein]

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

 

[Robert]

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

 

[Dave Peterson]

I'd select the range to fix and use:
Edit|replace
what: (spacebar)
with: (leave blank)
replace all

Then format the range the way I like.

 

[Dave Peterson]

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 here:
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]

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

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

 

[Robert]

Dave,

I tried that, but it didn't work : the "space" is not a " " !

Robert

 

[Rick Rothstein]

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?

 

[Dave Peterson]

I saved this from a previous post. You may find it useful:

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##)) '<--What showed up in CellView?

myGoodChars = Array("")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
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]

Rick,

The number displayed in the message box is : 128

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)

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

 

[Robert]

Rick,

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

Robert

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)

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]

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)

Rick,

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

Robert

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)

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" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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)


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

 

[Robert]

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

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)

Rick,

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

Robert

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)


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" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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)


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

 

[Rick Rothstein]

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)

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

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)

Rick,

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

Robert

"Rick Rothstein" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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)


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" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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)


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

 

[Gérard Ducouret]

Rick,
That doesn't work : numbers are still displayed as text. Is it possible to
simulate the multiplication by 1 in each cell ?
Thnaks again

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)

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

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)


Rick,

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

Robert

"Rick Rothstein" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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)


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" <[email protected]> a écrit dans
le message de news: (e-mail address removed)...
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)


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

 

[Robert]

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

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)

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

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)


Rick,

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

Robert

"Rick Rothstein" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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)


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" <[email protected]> a écrit dans
le message de news: (e-mail address removed)...
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)


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

 

[Rick Rothstein]

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)

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

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)

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" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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)


Rick,

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

Robert

"Rick Rothstein" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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)


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" <[email protected]> a écrit dans
le message de news: (e-mail address removed)...
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)


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

 

[Robert]

Rick,

At least the last one (#5) worked perfectly!
The # 4 was not far from the good result, but I had to edit then to validate
each cell (without any change)
Thanks a lot Rick, that will be a great help for me!

Robert

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 RemoveAllSpaces2()
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 RemoveAllSpaces5()
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)

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

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)


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" <[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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)


Rick,

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

Robert

"Rick Rothstein" <[email protected]> a écrit dans
le message de news: (e-mail address removed)...
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)


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" <[email protected]> a écrit dans
le message de news: (e-mail address removed)...
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)


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