Excel: Decimal Placement Appearance

John Ellis
John Ellis used Ask the Experts™
on
Hello:

I need for the numbers in each column of the attached Excel spreadsheet to "be" just as they appear.  

For example, in cell A2510, I need for the number to be just as it appears.  It needs to be 15440.92243.  It should not be 15440.9224333333, as Excel is showing it as at the top of the application.

What do I need to do, in order to make this happen?

Thank you!

John

Experts-Exchange-Decimal-Format.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You would need to do something like this for each cell.

range("A2510") = range("A2510").Text
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Here's a macro that will do that.
Sub AlterValue()
Dim cel As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each cel In ActiveSheet.UsedRange.Cells
    cel = cel.Text
Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Open in new window

Author

Commented:
I get an error with the use of that formula.  Can you please show me how to place it in Excel?

I'd prefer not to use a macro.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Also. I just now tried to make the cells to be text.  The results were not what I wanted.  Doing so added extra decimal placement.

John
Actually, a colleague of mine gave me the answer that I needed.

The answer is to use the "ROUND" function, as follows:

=ROUND(15440.9224333333, 5)

John
Rob HensonFinance Analyst

Commented:
There is an option in Excel that will force calculations to use the precision as displayed.

So use the normal formatting tools to get the number of decimal places required and then use Excel options to set this option:
Precision setting

Author

Commented:
My colleague gave me the correct response.

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