Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Remove decimals from a number and return in specific format

Posted on 2014-09-23
3
Medium Priority
?
115 Views
Last Modified: 2014-09-23
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
Comment
Question by:BEBaldauf
3 Comments
 
LVL 34

Accepted Solution

by:
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

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

Author Closing Comment

by:BEBaldauf
ID: 40339484
Thanks Rob!  The latter option you provided is perfect for the concatenation I'm trying to kluge together!
appreciate your simple, quick solution!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question