Problem with converting numbers stored as text in excel.
Hi,
I'm trying to make a macro that converts numbers that are stored in textformat for example "1.987,00 kr" to normal numbers so I can process them further. To accomplish this I am trying to replace the . with nothing.
When doing this manually in excel it all works fine but when I let the macro do it for me I a get really wierd outcome. when doing it manually the above example turns out to "1987" as it should be but when the macro does it it turns out as still in text format looking like "1 987 kr".
Another problem that occurs is that those cells with information less then "1.000,00" but with some decimals like "45,50 kr" turns out like "455" when the macro is run. Not good at all as it changes the information.
I should probably mention that the information I'm trying to process is exported from an external system and stored as unicode text, nothing I can change but have to work around with some solution like this after I opened the file in excel.
This is what i use:
Columns("H:I").Select
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=True
Columns("H:I").Select
Selection.NumberFormat = "#,##0_ ;-#,##0 "
Thanks in advance for any help in this matter.
[1456 byte] By [
MrT] at [2008-1-5]
Hey,
Delete this:
Columns("H:I").Select
Selection.Replace What:="", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=True
Use this (note: small changes is done):
Columns("H:I").Select
Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
Best Regards
Cathrine
Hello, just curious what kind of notation you are using. I have never seen people use . and , in reverse. Usually 0.5 means less than one, but you seems to use 0,5 as less than one. I think you need to swap the notation since I don't think Excel understands 1.056,45 and I don't understand that "string" either.
Hey and thanks Cathrine,
I got a bit further before i saw your reply and am close to accomplish what I set out to do.
This is the code so far:
Sub almost()
' note: not satisfied with this solution as it etakes long time to loop through it all.
Dim rng As Range, cel As Range
Set rng = Worksheets("r?data").Range("H2:I2000")
For Each cel In rng.Cells
cel.value = Replace(cel.value, ".", "")
Next cel
'note: removes all the "kr" parts from the cells.
Dim Rw As Long
Rw = Range("H65536").End(xlUp).Row
Range("I2").EntireColumn.Insert
Range("I2:I" & Rw).Formula = "=TEXT(VALUE(SUBSTITUTE(RC[-1],""kr"","""")),""@"")"
Range("I2:I" & Rw).Copy
Range("H2").PasteSpecial (xlPasteValues)
Range("I2").EntireColumn.Delete
Rw = Range("I65536").End(xlUp).Row
Range("J2").EntireColumn.Insert
Range("J2:J" & Rw).Formula = "=TEXT(VALUE(SUBSTITUTE(RC[-1],""kr"","""")),""@"")"
Range("J2:J" & Rw).Copy
Range("I2").PasteSpecial (xlPasteValues)
Range("J2").EntireColumn.Delete
Columns("H:I").Select
With Selection
.NumberFormat = "0"
.value = .value
End With
Columns("H:I").Select
Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
End Sub
After running the macro as it is now I only have a small problem left. Cells with numbers containing decimals eg. 435,5 stays in text format. all other cells now show as correct numbers.
Any ideas on how to solve this? Also any ideas to improve the first bit of code would be really nice.
many thanks.
MrT at 2007-10-3 >
magicalclick: this is exactly my problem. The data I'm using is exported form a swedish system and in sweden as in many european countries this notation is standard as opposite to the states. as you see in my new code above, the first thing I do is to remove all the "." then excel manages to accept the rest with "," as decimal breaker as that's in my local settings.
MrT at 2007-10-3 >
Aw, that's sux to hear. Even the most fundamental notations are inconsistent thru out the world. I would thought this kind of standard notation should already be unified as a planet, but just some countries.
Anyway, I recommand you to code it without the use of formulas since you are converting them into value in the end. IMO, formula is tricky to use, and mostly used for runtime calculation that is always updated without involking a macro.
like you can do.
For i = 1 to lastRow
cell(i, 3).value = replace(cell(i, 3).value, ".", "")
next
I think this is easier to undersdand and maintain by using traditional coding style, just a personal preference.
Hey,
Copy and try...
Sub Convert_Text_to_NumberFormat()
Dim wSheet As Worksheet
Dim wsAreaH As Range, wsAreaI As Range
Dim vaDataH As Variant, vaDataI As Variant
Dim i As Integer, j As Integer
Set wSheet = Worksheets("r?data")
Set wsAreaH = wSheet.Range(Range("H2"), Range("H65536").End(xlUp))
Set wsAreaI = wSheet.Range(Range("I2"), Range("I65536").End(xlUp))
vaDataH = wsAreaH.Value
vaDataI = wsAreaI.Value
For i = 1 To UBound(vaDataH)
For j = 1 To UBound(vaDataI)
vaDataI(i, 1) = Replace(vaDataI(i, 1), ".", "")
vaDataI(i, 1) = vaDataI(i, 1) * 1
vaDataH(j, 1) = Replace(vaDataH(j, 1), ".", "")
vaDataH(j, 1) = vaDataH(j, 1) * 1
Next j
Next i
wsAreaH.Value = vaDataH
wsAreaH.NumberFormat = "#,##0.00_);(#,##0.00)"
wsAreaI.Value = vaDataI
wsAreaI.NumberFormat = "#,##0.00_);(#,##0.00)"
End Sub
Cath
