Mr T


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.




Re: Problem with converting numbers stored as text in excel.

MyLady


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






Re: Problem with converting numbers stored as text in excel.

magicalclick

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.






Re: Problem with converting numbers stored as text in excel.

Mr T

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("radata").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.





Re: Problem with converting numbers stored as text in excel.

Mr T

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.



Re: Problem with converting numbers stored as text in excel.

magicalclick

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.





Re: Problem with converting numbers stored as text in excel.

MyLady

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("radata")
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