Converting European number formats

[Sarah H.]

Hi, all,

I work some with data from Europe and have trouble getting numbers into U.S.
format.

I found a promising looking macro on the web, written by David McRitchie:

http://www.mvps.org/dmcritchie/excel/numconv.htm
-----------------------------
Sub USNumbers()
'David McRitchie 2000-05-10 misc convert text numbers
' under wrong control settings to numbes
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
Dim origValue As String
Dim newValue As String
On Error Resume Next
For Each cell In Selection.Cells.SpecialCells(xlConstants, xlTextValues)
origValue = cell.Value
newValue = ""
For i = 1 To Len(origValue)
If Mid(origValue, i, 1) = "." Then
newValue = newValue & ","
ElseIf Mid(origValue, i, 1) = "," Then
newValue = newValue & "."
Else
newValue = newValue & Mid(origValue, i, 1)
End If
Next i
On Error Resume Next
cell.Value = CDbl(Trim(newValue))
On Error GoTo 0
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
-----------------------------

But when I try to run it in Excel 2010, I get an error about the For-Loop
not being set. Not sure how to proceed. Any help would be appreciated.

-S

 

[James Ravenswood]

Make sure Selection.Cells.SpecialCells(xlConstants, xlTextValues) is
not empty

 

[Jim Cone]

The code only works on text entries in the selection.
If you have selected only numeric values then Excel will balk.
If the amount of data is not extremely large (say under 10,000 cells ) then try the following...
1. Add this line just after Dim newValue as String: "Dim i As Long"
1. Remove (or comment out) the first occurrence of "On Error Resume Next"
2. Change: "For Each cell In Selection.Cells.SpecialCells(xlConstants, xlTextValues)"
To: "For Each cell In Selection.Cells"
The above should allow you to select all numbers, all text or text and numbers.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"Sarah H." <[email protected]>
wrote in message
Hi, all,
I work some with data from Europe and have trouble getting numbers into U.S.
format.
I found a promising looking macro on the web, written by David McRitchie:

http://www.mvps.org/dmcritchie/excel/numconv.htm
-----------------------------
Sub USNumbers()
'David McRitchie 2000-05-10 misc convert text numbers
' under wrong control settings to numbes
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
Dim origValue As String
Dim newValue As String
On Error Resume Next
For Each cell In Selection.Cells.SpecialCells(xlConstants, xlTextValues)
origValue = cell.Value
newValue = ""
For i = 1 To Len(origValue)
If Mid(origValue, i, 1) = "." Then
newValue = newValue & ","
ElseIf Mid(origValue, i, 1) = "," Then
newValue = newValue & "."
Else
newValue = newValue & Mid(origValue, i, 1)
End If
Next i
On Error Resume Next
cell.Value = CDbl(Trim(newValue))
On Error GoTo 0
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Scossa]

Hi, all,

I work some with data from Europe and have trouble getting numbers into U.S.
format.

Try this code (converts european string "1.234,56" into 1234.56 number
value, "52.725" -> 52725):


Sub USNumbers2()
' by Scossa
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim rng As Range
Dim cell As Range
Dim origValue() As String
Dim newValue As String
Dim i As Long

Set rng = Selection.Cells.SpecialCells(xlCellTypeConstants,
xlTextValues)
If Not rng Is Nothing Then
For Each cell In rng
origValue = Split(Replace(cell.Value, ".", ""), ",")
On Error Resume Next 'if no "," in string
newValue = origValue(0)
newValue = newValue & "." & origValue(1)
On Error GoTo 0
cell.Value = CDbl(Trim(newValue))
Next cell
End If
Set rng = Nothing
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Tnks for your feedback.

Bye!
Scossa

 

[Sarah H.]

Great information from all! Thanks very much, guys. I will be on holiday
the next four or five days, but I'll try the solutions and report here when
I'm back.

Sarah H.
--------------

 

[Sarah H.]

Scossa, your version of the Ritchie macro works for me. Thank you. It did
get flummoxed when it encountered some text that was an unconverted European
date, though. Here's what that column contents looked like: "26.08.2010".
(I know how to get that into recognized dates via text-to-columns. But I'm
simply reporting that this text confused the macro.)

Anyway, when I select the right columns it works beautifully for my needs.
Thanks again.

Sarah
----

 

[Sarah H.]

Scossa, your version of the Ritchie macro works for me. Thank you. It
did

Sorry: "McRitchie."

Sarah

 

[Sarah H.]

I've been back from vacation for a few days and finally got back to this.
Thanks again to all.
Jim's statement about the code's only working on text entries made me
realize that when
I imported the CSV I left those columns as "general." I just tried again
selecting "text" for
the columns with the European-format numbers, and I found to my delight that
the original
McRitchie macro now worked. Great answers from everybody! Much
appreciated.

Sarah H.