Excel 2007. XPsp3
I have a couple of columns of numbers where the csv file I have shows the thousand seperator as a decimal. Example 1,176.8781 shows as 1.176.8781.
Or 3.112.152.0000 should be 3,112,152.0000. Some of my columns are 4 numbers to right of decimal, some are only 2 numbers to right of decimal.
I have 10 columns and 8000+ rows which this scattered throughout affecting anything over 1,000.
I found a macro on an older post I thought would work but it just changed 1.176.8781 to 1176878. Nothing I do has seemed to change this.
I tried variety of format, number, custom, etc. ###,###,###.####. Nothing works! Please help.
Thank you
Here is the macro I tried.
Option Explicit
Sub testme01()
Dim myCell As Range
Dim myRng As Range
Dim mySplit As Variant
Dim myStr As String
Dim iCtr As Long
Set myRng = Selection
For Each myCell In myRng.Cells
myStr = myCell.Text
If InStr(1, myStr, ".", vbTextCompare) = 0 Then
'do nothing
Else
mySplit = Split97(myStr, ".")
mySplit(UBound(mySplit)) _
= Left(mySplit(UBound(mySplit)) & String(3, "0"), 3)
myStr = ""
For iCtr = LBound(mySplit) To UBound(mySplit)
myStr = myStr & mySplit(iCtr)
Next iCtr
End If
myCell.NumberFormat = "General"
myCell.Value = myStr
Next myCell
End Sub
Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function
Bookmarks