# Need fix for thousand seperator from decimal to comma retaining last decimal

1. ## Need fix for thousand seperator from decimal to comma retaining last decimal

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.

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

2. ## Re: Need fix for thousand seperator from decimal to comma retaining last decimal

It looks like that code is creating a TEXT string
And the text string is being entered in the cell as Text, not as a number..

Try changing this line
myCell.Value = myStr
to
myCell.Value = Val(myStr)

If that doesn't help, can you post a sample book?

3. ## Re: Need fix for thousand seperator from decimal to comma retaining last decimal

Thanks for responding Jonmo1. I made the change, ran the macro, 1.479.2908 changed to 14792908.
So it's just striping out the decimals completely. I need to retain the last decimal.
I checked and the format on the cell native is General. After running the macro the format stayed general.
I have played with various formats, number, custom etc. nada.
Attached is one of my smaller ones.
Notice, not everything is 4 decimals to the right, some are 2 or three so I can't lose that info.
testthousanddecimal.xlsx

Forgive me if this post is messed up. Still learning.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1