| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to remove the spaces in currency format?
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove the spaces in currency format?
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove the spaces in currency format?
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove the spaces in currency format?
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove the spaces in currency format?
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove the spaces in currency format?
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 "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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove the spaces in currency format?
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to remove the spaces in currency format?
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 "Rick Rothstein" a écrit dans le message de news: ... 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) "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to add new currency Symbol in Format/Cell/Currency | Excel Discussion (Misc queries) | |||
| How to remove the spaces in currency format? | Excel Programming | |||
| How to remove the spaces in currency format? | Excel Programming | |||
| how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
| Conversion from currency value to currency text format | Excel Programming | |||
Linear Mode
