Solved

Remove decimals from a number and return in specific format

Posted on 2014-09-23
3
100 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 32

Accepted Solution

by:
Rob Henson earned 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Route Recommendation 18 31
excel 2016 program to loop through scripts to apply filename 2 18
Alphabetical Order for Letters 2 21
Merging spreadsheets 8 29
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now