convert numbers to text with 2 decimal places

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
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Thanks
0
Glenn RayExcel VBA DeveloperCommented:
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
0
JagwarmanAuthor Commented:
Thanks Glenn
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Glenn RayExcel VBA DeveloperCommented:
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
0
JagwarmanAuthor 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
0
Glenn RayExcel VBA DeveloperCommented:
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
0
JagwarmanAuthor 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
0
Glenn RayExcel VBA DeveloperCommented:
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
0
JagwarmanAuthor 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
0
Glenn RayExcel VBA DeveloperCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glenn RayExcel VBA DeveloperCommented:
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

0
JagwarmanAuthor 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 :-)
0
Glenn RayExcel VBA DeveloperCommented:
I'm glad I could help.  Sorry it took so long to get a solution to you.

Have a good weekend,
-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.