Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Remove decimals from a number and return in specific format

Posted on 2014-09-23
Medium Priority
114 Views
I need to take the values in a column that contains numbers with and without decimals and return them in the following format:

Cell Value:         Result Needed: (zero filled on the left, four characters total)
1.8                        0180
2                           0200
.25                        0025
0
Question by:BEBaldauf
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 33

Accepted Solution

Rob Henson earned 2000 total points
ID: 40339465
Assuming values in column A, in column B enter formula:

=A1*100

Then format with custom format "0000".

Or if not needed for onward calculations, you can use:

=TEXT(A1*100,"0000")

This latter option has no need for custom format.

Thanks
Rob H
0

LVL 7

Expert Comment

ID: 40339469
make in column B a formula =TRUNC(A1*100;0) and make the view 0000 instead of 0
0

Author Closing Comment

ID: 40339484
Thanks Rob!  The latter option you provided is perfect for the concatenation I'm trying to kluge together!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month10 days, 2 hours left to enroll