• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Decimal Place Problem

IanG

New Member
I need a formula that can change all my numbers to 2 decimal places. My data includes numbers in millimetres and inches and varies between 0 decimal places to 3 decimal places.

Also some cell values are a range that is represented like this:

0.5 ~ 0.645mm

Not sure if the ~ symbol affects this or not.

Cheers,
Jayden
 
Throw some of your sample data into a file and upload it. You'll get a quicker better answer.

Show your initial data and what you want it to look like
 
The ~ symbol will mean that the cell is a Text Value and not a Number

For single cells you can use
=Text(A1,"#,###.00")

But for those cells with the ~
=Text(A1,"#,###.00") & " ~ " & Text(A2,"#,###.00") & "mm."
 
You can also use a Custom Number format
Select all the cells that are mm
Press Ctrl+1
Select Number Tab
Select Custom
In the Type: Dialog #,###.00 "mm."
Ok

You can abviously also use
#,###.00 "in."
#,###.00 "ft."
etc

once you have applied one of these, you can select other cells and the Format will already be in the list on the Custom Tab

These cells are now Numbers even though they have text in them

eg: If A1 has 10 with a Number format of #,###.00 "mm."
it will display 10 mm.

In B1 if you go =A1 + 10 it will display 20
 
Thanks for the tip. Here is an example of the data im working with.

Throw some of your sample data into a file and upload it. You'll get a quicker better answer.

Show your initial data and what you want it to look like
 

Attachments

  • data.xlsx
    82 KB · Views: 2
I tried using your formulas but they did not give me what I wanted. I've attached the sheet with your formulas in use so you can see what happened.
 

Attachments

  • data.xlsx
    161.2 KB · Views: 3
Back
Top