Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
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 can i remove spaces? | Excel Discussion (Misc queries) | |||
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 | |||
Remove spaces | Excel Programming |