Removing decimal places (not hiding them)

[ifu06416]

I have formatted column A's data to only show 2 decimal places.

However i also want to add in a written message from column B to the end of the formatted number eg

=A1&" "&"over"

My problem is that if A1 if formatted to 2 dec places (0.75) once the message is added the formatting erraces and i get the full number (0.7678547 over)

Is there any way i can removed all but thhe last 2 dec places so the answer reads 0.75 over

John.

 

[VoG]

Try

=TEXT(A1,"0.00")&" "&"over

 

[Rick Rothstein]

Use the TEXT function...

=TEXT(A1,"0.00"" over""")

or, as little shorter...

=TEXT(A1,"0.00 ov\er")

 

[BMR]

You can insert a column next to it, then do =Round(A1,2) or however many digits and copy that down through the whole column.

Copy those new numbers and Paste Values into your previous column with too many decimal places.

Now you have a rounded version of those with less decimal places.


If you don't want it to round and all of the cells have the same amount of digits, you can use Text to Columns and set a limited width, so it will split one cell into 2 at a certain digit. Then you can delete the second column it creates and you are left with your original numbers but with only however many digits you choose.

 

[ifu06416]

Thank you all,

VoG gets the prize though

 

[Rick Rothstein]

VoG gets the prize though

Just to point out that, as long as you chose to go the concatenation route, we can eliminate one concatenation from VoG's formula. Instead of this...

=TEXT(A1,"0.00")&" "&"over"

combine all the text into one text constant and use this...

=TEXT(A1,"0.00")&" over"<!-- / message --><!-- sig --><!-- / message --><!-- sig -->