Tracker
HOME    TRACKER    Excel

Add X Decimal Places To Numbers But Not Zeros

I have a column of numbers with one decimal point and some blank entries too. I need to convert all the numbers (including the zeros, but not the blanks) to two decimal points. Any idea on how this can be done? Examples: 0.1 needs to become 0.10, 0 needs to stay 0, a Blank cell needs to remain Blank.

Related Forum Messages:
Changing The Decimal Places Without Rounding The Numbers
I was trying to decrease the decimal places of the data figures that I'm currently working on my report, however, it keeps rounding-up the decimal numbers. I wish to keep the original numbers and just decrease the decimal places.

For example:
The original figure is = 7260.12903225806
Upon decreasing the decimal figures to just 2, the result became = 7260.13

Is it possible for me to just have this result = 7260.12 instead of 7260.13?

I've tried using the TRUNC formula butit does not work if the 2nd decimal value is 0.

For example:
The original figure is = 227161.905808985
Upon applying the formula, the result became = 227161.9 instead of 227161.90

Forcing Decimal Places (wanting NUMBERS Not TEXT)
I am wondering if there is any way to "force" a number to have three decimal places instead of two and still have that number valued as a number for use in formulas later (like AVERAGE, etc.).

What I have is a combination of HLOOKUPs, IF statements, and Conditional Formatting.

I have a Master Grades sheet and individual Class sheets. I use an HLOOKUP, matching a Student Number in both sheets, and looking in the Class sheet for the P/F indicator. If the indicator is a F, the HLOOKUP is to return the Final Grade from the Class sheet and input that into the Master Grades sheet, forced to three decimal places (whereas the numbers in the Class sheets are at two decimal places) If the indicator is P, the HLOOKUP simply returns the Final Grade from the Class sheet and inputs it into the Master Grades sheet with two decimal places.

I have the failure grades forced to three decimal places so that the Conditional Formatting in the Master Grades sheet highlights the failed grades for teachers and anyone else can see right away that a student failed, but the number still allows the Master Grades sheet to calculate the overall AVERAGE for the student as well as the AVERAGE for that class.

If any of this is not quite clear, please ask and I can further expand on what I am doing.

Here is the code I have right now that is not working correctly (and I know why it's not): ....

Decimal Places For Close Ties Of Numbers Based On Cell Value
I have a pivot table of values displaying agency's performance based on their sales percentage to target. When the race is 'loose', and all companies are 1 or more percentage point away from each other, I format the values without decimal places. If two agencies appear tied, I add a decimal place to seperate them. Occasionally the race is really tight and I have to increase the format to two decimal places to seperate them. how to do this automatically?

Paste Two Decimal Number In Excel Without Extra Decimal Places Appearing
I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...

Removing Decimal Point While Maintaining # Of Decimal Places
I need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.

Consistent Decimal Places
Is it possible to apply some kind of formatting to a range, that will force all numbers entered into that range to have the same number of decimal places as the cell with the maximum number of decimal places?

Fixed Decimal Places
Everytime I enter a number, excel automatically converts it to a decimal number.

I type 1 in a cell (or formula box)
Excel returns the value as 0.1

I think it may have to do with the FIX box highlighted to the bottom of the screen. (To the right side of the NUM lock). (I can't paste a screen dump to show the FIX box position). how to remove the FIX box, and/or change excel so that I can enter in numbers normally.

Round To More Than 30 Decimal Places
I'm trying to work out a formula in excel which requires me to use Pi to over 30 decimal places. Excel will only let me have 30 decimal places of Pi whether I copy and paste it as a number or use its Pi function and even then seems to round up. Is there any way I can get more than 30 decimal places for this calculation? If not in excel then can anyone suggest another programme that may be capable of this? You can post here or contact me at [email=" Deleted by Jack in the UK[/EMAIL]

Concatenate And Decimal Places
I am facing a problem with Excel's Concatenate function. I am trying to make a text string with numbers from a cell. =CONCATENATE( "price paid= ", D23). D23 is a numreric cell, formatted for one decimal place. However, the text produced by above function is showing me two decimal places of the number in D23.

Limiting A Cell To 2 Decimal Places
How do I stop a user from entering more than 2 decimal places?

If I format the cell to "0.00" I can still type in 1234.1234 and it will display 1234.12.
If I select that cell, the value in the formula bar is still 1234.1234.

Formatting Of A Cell To 2 Decimal Places
if I change the formatting of a cell to 2 decimal places, it appears as two decimal places (as it should) for example \$88.88888 will show as \$88.88

However, when I use this data in another application that displays this data it will display as 88.88888 still. I need to actually take the value and truncate it to 88.88 eg 8.8888888 will become 8.88

I have been using trunc by hand and wanted to try and find out if there is a way that I could write some sort of macro to do this for me each time.

Currency Format With 4 Decimal Places
I have a problem with the currency format with 4 decimal places.

Sub go()
Range("C5") = Range("C5") + Range("C4")
End Sub
as you can see it adds the conecnt of "R4" to what is in "R5"

If works fine when the calls are formatted as number with 4 decimal places.
example if R4 is 0.1111 and R5 is 0.2222 after running the macro R5 becomes 0.3333

But if the cells are formatted as currency and 4 decimal places the then excel returns \$0.3300

Set Decimal Places By Cell Value
I am converting values from SAE to Metric and wish to retain the decimal places of the SAE value before converting.

My code below converts nicely but doesnt maintain decimal places.

Using The Accounting Format With No Decimal Places
I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:

Converting Units And Decimal Places.
I have a simple spreadsheet that allows the user to enter a dimension in metric or inches. I want to display the other units in the adjacent cell. In cell A1, the units are "Metric" or "Inch" in a pull down list. In cell A2, the value is entered.

In cell A3 i want to show the value in the other units. So if A1 is Metric, then take A2 and divide by 25.4. And if A1 is Inch, then take A2 and multiply by 25.4.
Also, if A1 is Inch, then display 2 decimal places in A3, and if A1 is Metric, then display 3 decimal places in A3. Is this possible?

Show As Many Or As Little Decimal Places In Any Cell
I am doing some calculations via Excel, and I have found that I get different results using a calculator. I know that by formatting the number, I can show as many or as little decimal places as I like in any cell, but can anyone tell me how many decimal places Excel actually uses when it's carrying out calculations? I have a feeling that the Excel results I'm getting might be more accurate than my calculator ones.

Set Decimal Places In Cell?
What do I need to add to this code to set the decimal places to zero?

Fix Decimal Places On Entry
I am creating a Daily Cash Count worksheet for a business to do their daily closeout paperwork. What I am trying to do is automatically apply decimal formatting to a cell. Examples, if they count 60 cents in nickles and enter 60 in the cell, it automatically converts it to .60. And if they count 7.50 cents in quarters and enter 750 in cell it automatically would convert it to 7.50.

How To Type In 2 Decimal Places In 1/4 Hour Display
I have to change a time sheet that was previously in HH:mm to display in decimals, but to the nearest 1/4 hour ( 0.25 (=15 mins), 0.50 (= 30 mins), 0.75 (=45 mins), and 1.00 (1 hour))

This is not a formula question, but a format/display question as the cells have to be typed in by different people who up until now have beeb used to typing in the HH:mm format.

This means;
8:15hrs would become 8.25
8:30hrs would become 8.5
8:45hrs would become 8.75

The reason behind the change is a head office wallah that needs to have time in decimals and not true minutes.

Is there a way to;
prevent user from entering any decimal other than .00; .25;.50;.75
or change formatting to turn red if any other than above is entered.

Alternatively, is there a way of me (the collator for 50 people) being able to take data from their sheets (which I have to do monthly) which could be a formula...

I have tried to get head office to change, but they say that they collate data for 13 other sites and all the others are OK (thats cos all the others receive their data on PAPER and one person tabluates them)...

Format Number Of Decimal Places To Show
Is there a way to format a cell based upon a condition? If the cell value is <1, I want to show two decimal places. If the cell is >1, I want to show zero decimal places. I tried to use the conditional formatting, but there is no option for this.

VBA Increase The Decimal Places Of A Cell
Is there a function in VBA that lets me increment or decrement the number of decimals of a particular cell? let's say i got a cell value of 7.123456 but excel only displays 7 on the cell. What if i want excel to display only 7.123? How can I specify the number of decimals i want on a particular cell (in this case 3)

Format All Percentage Cells To X Decimal Places
I would like to do the following: * Format numeric cells (cells with a percent sign) to zero decimal places. I have a set of macros that I am combining to clean up a data set and these are two of many (removing blank lines, duplicates, blue/bold, etc.)

Restrict TextBox Entries To X Decimal Places
I have following for change event in text boxes to only allow numerics e.g.

Private Sub txtGBP10_Change()
If (Not IsNumeric(txtGBP10.Value) And (txtGBP10.Value <> "")) Then
txtGBP10.Value = Left(txtGBP10.Value, Len(txtGBP10.Value) - 1)
End If
End Sub
Private Sub txtShare10_Change()
If (Not IsNumeric(txtShare10.Value) And (txtShare10.Value <> "")) Then
txtShare10.Value = Left(txtShare10.Value, Len(txtShare10.Value) - 1)
End If
End Sub

Can I add some code so that the user can only add numeric entries to 2 DP (txtGBP) or 3DP(txtShare)

Format Result Of Date Difference To 2 Decimal Places
I use datediff to calculate the difference between 2 dates in Months.

Result = Datediff("M", Date1,Date2)

I need the result to be formatted to 2 decimal places ie 23.52 (months)

Not neccessary to use datediff if this doesnt work.

Please note I have cross posted this HERE because I couldnt get connected to ozgrid for some reason, I will montior both sites for a solution and post back results. Apologies for the inconvenience.

Concatenate Formula Not Taking Decimal Places/currency Sign
i have lets say a table with rows that contain the following data.

SKU - PRICE - SALEPRICE - DESCRIPTION - SALEDESCRIPTION

What I am trying to do, is to make a concat formula that says:

SALEDESCRIPTION = DESCRIPTION+price was+PRICE+now+SALEPRICE

In essence so that i can simply pull down the formula, and all our product descriptions have 'special offer, was xx (price) is now xx (sale price).

This works fine, except that in the SALEDESCRIPTION, the amounts from the price and saleprice fields are being brought over without decimal places and without currency symbol.

I have tried formatting those two columns as currency or accounting but neither works.

Number Showing Extra Decimal Places In Formula Bar
If you look at the attached file in Column E line 47 you will see that it displays
373.97. However if you look in the formula bar it shows 373.9694. I am trying to make it so that the formula bar shows the same number as in the cell. Any idea on how to do this? I have tried everything I know how to do, which is not much.

Custom Data Validation Formula To Limit Cell Value To Maximum Of 4 Decimal Places
I require a custom data validation formula to limit cell value to maximum of 4 decimal places.

0.0001 ok
0.02 ok
0.3 ok

0.12345 fail
0.123456 fail etc

Show Zeros After Decimal
I have a list of numbers:

21.20151242
28.012224

etc....

I want to round/truncate them as if it were currency and add "/month" to them

so the resulting column would be:

21.20/month
28.01/month

everytime I truncate to two digits and add the text it cuts off the final zero:

21.2/month

how do I do this?

Drop Decimal But Keep Ending Zeros
Here's my problem. I need a formula to take the decimals out of a column but keep ending zeros. I have tried find/replace & several formulas but nothing seems to be working. Here is an example of the numbers in the column and the end result I need.

9.66 966
0.50 050
13.09 1309
309.98 30998

Decimal Format With No Trailing Zeros
I need to never have a trailing zero be displayed but still have decimals displayed (we can use up to 3 digits for the example) Here's a list of test cases

Value______Display
1.234567___1.235
1.234______1.234
1.23_______1.23
1.230______1.23
1.2________1.2
1.200______1.2
1.0________1
1.000______1
1.020______1.02
1.0002_____1
0__________0
0.001______0.001
0.0001_____0

The only time I want to see a 0 is when the answer is 0 (or rounded to 0) or when 0 is required in the middle. This is for patient safety if you are curious. 2.0 when printed out or written by hand can be mistakenly read as "20" in the heat of things, thus 2.0 should be written/printed as "2" (no decimal on the end) not as "2." even, just "2"

Converting Decimal Numbers To Text With Dot Numbers
we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers:

Sub ForceToNumber()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
With wSheet
. Range("IV65536") = vbNullString
.Range("IV65536").Copy
End With
Next wSheet
End Sub

Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.

List Only Whole Numbers From Decimal Numbers
From a list of numbers I would like to delete values that have cents so only those transactions with a .00 amount are displayed

For example

34.95
21.88
21.00
56.00
45.77

Only those valaues ending in .00 will list. I tried filtering but I think there most be a function(s) string that might work or at least filter out the values with cents

Zeros At The Front Of Numbers
i have to copy and paste values from an sap program over to excel spreadsheets, and I usually do about 15 at a time that end up in a column: 15 different cells. The value I am copying are ID numbers that all begin with zero and excel automatically removes the zeros at the front of each number. Is there a formula/process for preventing this.

Zeros At The Begining Of The Numbers
I've made an formula to concartenate some numbers:

In A1 the value is 0
In B1 the value is 0
In C1 the value is 6
In D1 the value is 9
In E1 the value is 5

These numbers will change acording to a formula, but:

In F1 the formula is =A1&B1&C1&D1&E1
but in F1 the result will be: 00695
How can i make the result in F1 to be 695 instead of 00695?

I need a formula that will make a 4 digit number by adding leading zeros. If the number is already 4 digits then don't add leading zeros, do nothing. all numbers will be 1,2,3,4 digits long.

For Example,

if number is 1 then make it 0001
if number is 11 then make it 0011
if number is 111 then make it 0111
if number is 1111 then leave it 1111

Often I need to add data from one spreadsheet to the appropriate places on another spreadsheet. For example:

Sheet A has 10,000 records with these fields: id#, name, address, place of employment.

Sheet B has 5,000 records these fields: id#, GPA, college major, type of degree.

Some of the records in B contain information for the same id#'s as sheet A. I want to add this information together so that a Sheet C will have these fields: id#, name, address, place of employment, GPA, college major, type of degree.

Adding Zeros To The Prefix Or Certain Numbers
Im basicaly pulling a report wherein the details will have a column as number.

It basicaly should be a three digit number. The tool will have the information as below.

VNO005016017008019020021

But when im pulling out the report into excel, the zeros in the prefix goes away and the report looks like the below

VNO516178192021

I just checked the tools previllages and it seems that we cannot do anyting in pulling the data as it is in the tool.

So wht im trying to do is to save the report in a folder where i have placed a validation sheet which will have references of the parent report.

So when i open the report the reference will pull up the data and in here i would like to add a validation which will add "0" in the prefix if VNO is a 2 digit number or "00" if the VNO is a one digit number.

Adding Zeros To The End Of Numbers
I have a large spreadsheet of employee data saved in a shared network folder so that others can access it. Starting about a week ago, anytime I enter a number in any cell, Excel adds two zeros to the end of the number. For instance, a 1 becomes 100.

When other users open the file on their computer and enter a number, everything is normal - no zeros are added. I'm guessing I inadvertantly enabled a function somewhere along the line, but have been unable to figure out how to undo the function.

i have a header row that i want to transfer to a new spreadsheet. here is my exact setup to make it more clear:

Sheet1 - 001 PM Usage\$%
Sheet2 - 022 PM Usage\$%

i have dozens of these rows, and i want to set up a auto rename for the header row on sheet 2 to rename itself like this:

Dim the value from header row on Sheet 1 (001), and rename the header on Sheet 2 (022), keeping the remaining contents intact (this is important because i want to use the same function for other headers which varies).

what i am doing now is dumping the value of the first cell into a variable, but as it stands it doesnt store 001, it stores 1... stripping the zeros, which makes it more complicated.

i thought perhaps i should dim the first 3 characters in the header as a string, and rename the first 3 characters in header 2, which i think would be the most efficient, but i dont even know if that is possible.

I have ID numbers that sometimes starts with one or more zeros and when i try to paste a string containing ID numbers that starts with a zero, excel converts it to a number thereby deleting the leading zero(s). Is there any way to force excel to keep the leading zero when i paste from the clipboard?

Clipboard.SetText strMyString, vbCFText
Set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Activate
xlApp. ActiveWorkbook.ActiveSheet. Range("A2").Select
xlApp.ActiveWorkbook.ActiveSheet.Paste

I have tried setting the format for a column as text using xlSheet.Columns("D").NumberFormat = "text" before pasting to try to force excel to keep the leading zero but the ID# comes out unreadable as "############". I have also tried formatting as "General" and custom formatting the column before pasting but have been unsuccessful thus far. I've seen other programs accomplish pasting numbers with leading zeros but how do you do this?

Add The Neccesary Zeros If Cell Is Not Having 10 Digits
I format the cells with ########## but it does not add the neccesary 0's if its not 10 digits, am I doing something wrong?

Some of the cells are 4 digits, some 7 some 9 etc i need all 10 with trailing 0's

I have a spreadsheet full of site codes which should be the format:
AA1111

or

A1111

Where A = any alpha character & 1 = any numeric character

unfortunately the staff entering the code nearly always miss leading zeros off the numeric part of the site code.

At the moment I manually correct this. Is there a better way to do this?

Counting Decimal Numbers
I am trying to find a way of counting decimal numbers if, say, they begin with 3.

For example, I might have 3.33, 4.1, 3.0, 5.65, 3.8, 3.7, 3.33, etc.

I want to count anything that begins with 3 (3.33, 3.0, etc). Using the data above the answer would be 5 ...5 numbers that begin with a 3.

If this possible?

There doesn't appear to be the facility with Countif. I don't want to truncate the data (the spreadsheet already is quite extensive). I have tried using =SUMPRODUCT((Y\$2:Y\$170 >=3)*(Y\$2:Y\$170<=3.9)) but this will return an answer only when there is a complete set of data within cells within the range.

Spliting Numbers Regarding Decimal
I would have a preliminary data, a number, on sheet1. The final data would be on sheet2.

In the case where there is a whole number as the preliminary data, the data would remain the same as the final output. (eg. 248 --> 248)

In the case where there is a number with a decimal, I need a function where it looks at the original number as two sets of WHOLE numbers. (Eg. 248.30 --> 248 and 30). Where the digits to the left of the decimal remains the same and the digits to the right would be divided by x (in this case, 10). The final output would be 2483 (where 248 is the same and the 30 is divided by 10.

Since I am using an older version of excel, I cannot use quotient. I know that I would be using the vlookup function and perhaps an if function. however, I do not know how to separate the numbers in regards to the decimal.

Delete Decimal Numbers Only?
Is there a way to delete only numbers ending in a decimal from a column of numbers? I have a column of numbers but only the non decimals are relevant to the next equation.

Convertering All Numbers To Decimal
My coworker's excel program is converting all data to decimals. He enters in 2004 as a date and it makes it 2.004. I opened the same file on my computer and it works fine. I have done all fo the formatting to try and make it a number or a general field. The general field is not showing a preview as if there is some error overriding the whole program. He has some other numbers that he has already entered as currency and they work fine. I was able to do a =2004 and it showed as 2004 of course.

Whole Numbers Being Changed To Decimal
when I tried to input numbers into an Excel spreadsheet (Excel 2003) the numbers were entered as decimals. Example: I type in 235 with NO decimal point, Excel enters it as 2.35, even when starting from scratch, using a new sheet or new workbook. I tried using format cell from the menu bar to choose "general" then as "number". Nothing changed. I also tried to set the decimal places at "0" using the number format. All that did was truncate the number to 2 with the .35 not being displayed in the cell. It IS displayed in the formula window as 2.35... but not as I entered it... 235. This happens no matter whether or not it's a brand new blank workbook or worksheet

Add Decimal Part In A Range
How can I add decimal part of values in a range

eg: assume my range has 2.27, 3.1, 3.725, 4.1, 4.35

result should be 789 (27+1+725+1+35)

Express Time As A Decimal And Add
I'm trying to make a shift schedule template, and it's all done except that i cant sum the hours the way I think i should be able to.

I've attached the file for reference.

I've added in some hours for myself as an example. I have 47.5 hours in the week. I've got 3 Rows, In Out and Hours. I've formatted the In and Out the way i want them, but I would like the Hours row formatted as a decimal, so that 7 hours 30 minutes would show up as 7.5.

I'm also having trouble with the totals column at the end. Although D8:J8 total 47.5 hours, it's adding them as 23:30.