Spliting Numbers Regarding Decimal

Aug 8, 2008

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.

View 12 Replies


ADVERTISEMENT

Converting Decimal Numbers To Text With Dot Numbers

May 27, 2006

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
.UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
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.

View 3 Replies View Related

List Only Whole Numbers From Decimal Numbers

May 23, 2009

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

View 4 Replies View Related

Delete Decimal Numbers Only?

Dec 14, 2008

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.

View 3 Replies View Related

Counting Decimal Numbers

Oct 25, 2006

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.

View 10 Replies View Related

Convertering All Numbers To Decimal

Feb 16, 2007

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.

View 3 Replies View Related

Whole Numbers Being Changed To Decimal

Apr 29, 2007

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

View 2 Replies View Related

Subtracting Time (in Numbers) To Decimal

Oct 21, 2009

How can I add/subtract time entered as 1830 to give a decimal value?

Start=1830
Finish=2000
Time between = 1.5

Also, how can I convert time as 1830 (24hr) to 12hr time? ie. 0630.

View 3 Replies View Related

Allow Only In Textbox1 Numbers & Decimal Point?

Jan 31, 2014

I need to allow only in textbox1 Numbers & decimal Point.

View 4 Replies View Related

Decimal Numbers To Feet And Inches

Jan 19, 2004

Is there a way to store a text style that will allow me to convert decimal numbers into feet and inches in the same cell?

I want to be able to type in a number like 3.5 and have it read 3'-6".

View 9 Replies View Related

Parse Decimal Numbers From Text

Nov 6, 2006

A while back I asked how to remove the letters from entries that have both numbers and letters. I as pointed to a UDF called "ExtractNumber".

Here is the code for that UDF: .....

View 9 Replies View Related

Compare Variable To Decimal Numbers

Sep 28, 2007

I have an If loop which looks like: If x < 54.5 And x > 52 Then

I have declared x as "Long", but still when it does the comparison it seems to round x up to the nearest whole number for the second comparison ( x > 52 ).

View 4 Replies View Related

Add X Decimal Places To Numbers But Not Zeros

May 24, 2008

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.

View 9 Replies View Related

Validate TextBox For Decimal Numbers

Jun 6, 2008

I have set up a form which requires one textbox to have a decimal followed by four numbers (ex .5780) and another which requires two numbers, a decimal, then two more numbers (ex 57.80). how this can be accomplished? I am new to Visual Basic coding!

View 6 Replies View Related

Spliting The Data

Apr 3, 2009

i have export some data (contacts) from my mobile in excel in the following format

SURESH"""""""""""'',""""""""""9852653563""""""""""suresh@ril.com""""""""""""""
PRADEEP GOSWAMI""""""""",""""""""02836226358""""""""""""umamarine@yahoo.com

total 1000 contacts in this format now i want to split it them with suitable format
pls guide for it.
I tried for Text to column but the exported data is not in the sequence.

View 11 Replies View Related

Excel 2010 :: Sum And Average Of Decimal Numbers

Apr 10, 2014

Basic Excel-2010 functions.

I have given a table with some decimal values like e.g 0.3658 0.358 0.485 0.7863 0.558 0.858 0.985, I want to find the sum and average of these number.

1) I have tried the autosum or auto Function =AVERAGE(A2:A7) but it yields #DIV/0! error
2) when I use =AVERAGEA(A2:A7), it yield ans=0
3) puttin =ROUND(AVERAGEA(H2:H23);3) also yields 0;

View 4 Replies View Related

Changing The Decimal Places Without Rounding The Numbers

Mar 19, 2009

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

View 4 Replies View Related

Remove The Display Of The Leading Zero In Decimal Numbers

May 15, 2009

Is it possible to remove the display of the leading zero in decimal numbers of less than one in Excel 2007?

View 3 Replies View Related

Code Writes Decimal Numbers Next To Each Other Instead Of Adding

Sep 4, 2009

Code writes decimal numbers next to each other instead of adding. I have the following code on a very simple form:

View 5 Replies View Related

Require 2 Numbers Before A Decimal In Data Validation?

Feb 16, 2012

How can I require 2 numbers before a decimal in data validation? I have a formula to require at least to characters after.

I'd like to require the user to enter at least ##.##

View 9 Replies View Related

Filter Only Decimal Numbers - Result In Same Column

Apr 3, 2012

I have few numbers in Column "A". Few are normal figures & few are decimal numbers, like this:

20
5
5.63
4.5
200
53.263
125.5

Now I want to filter only the decimal numbers, so that after filter the result will show like this:

5.63
4.5
53.263
125.5

But I don't want to use any other column for doing so. I can do it using MOD in other column but I want the result in the same column.

View 7 Replies View Related

How To Convert Big Hexadecimal Numbers To Decimal Format

Mar 19, 2013

I need to be able to convert big hex numbers into decimal numbers. The problem that I have with this is that when you put a hex number that includes letters excel considers it text. And if the number is too big, you can't convert it to decimal format, and when you try to, it comes up with the #NUM! error.

Number: "78652385098375A879FF747C9847A00" without quotes

View 4 Replies View Related

Converting Decimal Numbers To Minutes And Seconds

Jul 27, 2009

I have an excel sheet with a row of numbers example below. These numbers represent the length of time that a telephone call was. The problem is they are decimal numbers (units of 100) but they represent seconds and minutes of a phone call which change up one number when the seconds hit 60 ...

View 9 Replies View Related

Reducing Decimal Points Without Rounding Up The Numbers

Jul 25, 2006

I have columns of geological data in number form which may have about 4 or 5 decimal points. I want to reduce them to 2 decimal points without rounding the numbers up. Is there a simple way to do this?

View 4 Replies View Related

Change Default Decimal Points For Numbers

Feb 14, 2008

I've got a wierd simple problem in Excel 2003. I have a laptop and a desktop machine, and I'm an accountant who uses the fixed decimal feature as a default, with it set to 2 decimal places.

On the desktop machine, if I input "23." into a cell and then hit enter, the value left in the cell is "23.00" However, on the laptop, when I do the same thing, I get "0.23" In essence, it ignores the fact that I entered a decimal point.

If I enter "23.0" in a sheet on the laptop, it puts "23.00" in the cell.

I thought maybe it was some sort of hardware thing, like the KB was messed up, but then I used remote desktop to try to see what would happen if I enter numbers into the other computer using the laptop, and into the laptop using the desktop. The laptop when connected to the desktop performed normally, just like the desktop machine. The desktop, when connected remotely to the laptop behved like the laptop machine. In other words, I believe this test takes Hardware out of the picture.

Which leaves some strange obsure setting in excel someplace tht is causing this behavior, and I can't seem to find it.

Does anyone here know why these two installs treat the data entry so differently? It's driving me nuts.

View 9 Replies View Related

Spliting Data In The Same Cell

Mar 24, 2009

It has been awhile since last I have done this, but how do you split a data in the same cell. For example, I have "Smith, John" in the same cell, and I want to get "Smith" and "John" in two different cells.

View 6 Replies View Related

Spliting Up Text Strings

Jan 16, 2014

What can split up text strings into two cells.

I have this in A1

"X,Y Coord Dn = ",38033.8,42701.7
I have this in A2
"Bearing = ",128.9

I want to split this text into 2 cells.

The Formula I am using for the B1 is:

=IFERROR(LEFT(A1,SEARCH(""",",A1)+1),"")
result = "X,
The Formula I am using for the C1 is:
=IF(A1=B1,"",RIGHT(A1,(LEN(A1)-SEARCH(""",",A1)-1)))
result=38033.8,42701.7
The Formula I am using for the B2 is:
=IFERROR(LEFT(A1,SEARCH(""",",A2)+2),"")
result="Bearing = ",

The formula I am using for the C2 is:

=IF(A1=B1,"",RIGHT(A1,(LEN(A1)-SEARCH(""",",A1)-1))) result=128.9

I am using the same formula down each column; But i can not seem to ge the "X,Y Coord Dn = ", to work properly.

View 9 Replies View Related

Spliting Up Cells Into Two Groups

May 3, 2007

to set up a rule so say certain numbers can be put into two groups in another cell.

i.e. codes 01,02,03,04 are grouped in country 1 and 05,06,07,08 are from country 2, so in the same row in a seperate cell it would identify if the product was 1 or 2?

View 9 Replies View Related

Spliting A Mixed Text

Sep 23, 2008

I have a mixed string (i.e. containig character and numbers.... but the format is same PAR1 or PAMR1 or PR10 it can be 4 characters or five) i want a formula or a macro which splits it in two parts one containing characters and the other one containing nos.

View 9 Replies View Related

Convert Decimal Numbers (Minutes) To Time Format

Mar 8, 2013

How to convert decimal numbers which represent minutes into time format? Like this

Sheet3  GH1MinutesTime212.9729729700:13:00325.9459459500:26:004116.756756801:57:00

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved