Excel 2010 Macro to Format Number to Remove the .0 at the End

Senniger1
Senniger1 used Ask the Experts™
on
We have Excel 2010 and Access 2010.

I exported some data from my Access database into an Excel spreadsheet named Example.xls.  In column A I have reference numbers, but some of the numbers are formatted wrong and I need to change them.

I need to remove the .0 from the end of any number.  In my attachment, I have highlighted the numbers which need to be changed with code.  For example the number 2014800136156.0 needs to be changed to 2014800136156.

Can anyone provide me with the code to change these numbers in column A automatically through vba macro.

Thanks in advance.
Example.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

Pls try
    Columns("A:A").Replace What:=".0", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Columns("A:A").NumberFormat = "0"

Open in new window

Regards
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Format the column as Text and then run this macro.

Sub RemoveZero()
Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = ActiveSheet.UsedRange.Rows.Count

For lngRow = 2 To lngLastRow
    If Right(Cells(lngRow, "A"), 2) = ".0" Then
        Cells(lngRow, "A") = Split(Cells(lngRow, "A"), ".")(0)
    End If
Next

End Sub

Open in new window

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Use Rgonzo1971's code. It's better and faster than mine.

Author

Commented:
Perfect!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial