convert numbers to text with 2 decimal places

Jagwarman
Jagwarman used Ask the Experts™
on
I need help with converting numbers to text numbers with 2 decimal places.

In my file in cells I see 100.20 but in the formula bar I see 100.2

so if I have 150.5 I need it to be 150.50 if I have 100.2 I need it to be 100.20

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Should have said this needs to be using VBA code.

Thanks
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
In VBA:
    ActiveCell.NumberFormat = "@"
    ActiveCell.Value = Format(Val(ActiveCell.Value), "0.00")

Open in new window


The first line changes the cell format to text; the second line adds any necessary decimals

Regards,
-Glenn

Author

Commented:
Thanks Glenn
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Thank me when you've got it working!  Please click the "Accept this solution" above my previous post to properly close the question.
Thanks,
Glenn

Author

Commented:
good point cos I can't get it to work I need to get it to work over a range F:F and sometime F:G I thought maybe I could refer to your earlier post where you solve the date for text,  and work out from that, but it never is that simple. So I am sorry but I need more of your help
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Kind of figured that.  Here's a more-general code that assumes a range of values is selected in advance:
Sub Convert_To_2Decimal()
    Dim rng As Range
    Dim cl As Object
    
    Set rng = Selection
    
    For Each cl In rng
        cl.NumberFormat = "@"
        cl.Value = Format(Val(cl.Value), "0.00")
    Next cl
End Sub

Open in new window


Select the range you want to convert, then while it's selected, run this macro.

-Glenn

Author

Commented:
Hmm I set the range F:F and row 1 was a header.

It changed the header to 0.00 and every cell to the end of the file in row F:F
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Hmm, well, you should only select the cells that you want to change! :-)

So, I now presume - although you did not originally state it - that you only want this change to occur:
1) if the value contains a number, and
2) if the value is not blank.

Please confirm this and add any additional clarification necessary to provide a workable solution.

-Glenn

Author

Commented:
Sorry Glenn.

Yes I only want the change to occur in in the range F:F
that contain a number and the value is not blank
Many thanks
Excel VBA Developer
Top Expert 2014
Commented:
Okay, try this code instead.
Sub Convert_To_2Decimal()
    Dim rng As Range
    Dim cl As Object
    
    Set rng = Range("F1:F" & Cells.SpecialCells(xlLastCell).Row)
    
    For Each cl In rng
        If WorksheetFunction.IsNumber(cl) Then
            cl.NumberFormat = "@"
            cl.Value = Format(Val(cl.Value), "0.00")
        End If
    Next cl
End Sub

Open in new window


It is very specific:  it ONLY changes the numerical values in column F from the first cell down to the last cell used.  You don't have to select the range; it does it for you.  You really don't want to select the entire column as that takes more time (processing cells with no values well past your data range).

-Glenn
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
You mentioned column G earlier; if you need to change that column also, then modify line 5 like so:
Set rng = Range("G1:G" & Cells.SpecialCells(xlLastCell).Row)

Open in new window

Author

Commented:
wow, thanks Gelnn that is brilliant. I can now have a restful weekend without worrying about this. Thank you so much. Have a great weekend.

If you were local the meal and wine would be on me :-)
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
I'm glad I could help.  Sorry it took so long to get a solution to you.

Have a good weekend,
-Glenn

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