Converting European number formats
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
----
"Scossa" wrote in message
...
On 26 Ago, 14:02, "Sarah H." wrote:
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
|