We help IT Professionals succeed at work.

convert numbers to text with 2 decimal places

Jagwarman
Jagwarman asked
on
272 Views
Last Modified: 2014-08-08
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

Author

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

Thanks
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
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
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.