Formula To Delete Whole Numbers But Leave Decimals?

Apr 22, 2014

I've got a spreadsheet that's basically a large list of numbers, both whole and decimal. For example, let's say this is in cells A1-A5:

4
0.65
1.34
3
8.2

Is there a formula to get rid of all of the whole numbers but leave the decimals? (What I mean by that is I don't need 4 or 3 as they're whole, but I need the decimals to be left alone).

I know it's probably a really awkward question but I have over 2,000 lines to go through, it will take a long time to do manually.

Perhaps if it's not possible to a formula to delete entries, maybe just make all whole numbers say something like "NO", so that I can sort the column in A-Z order and delete all of the 'NO's quickly by highlighting them all together.

I need to delete all characters BUT numbers (including decimals) in my sheet. Does anyone know how I can do this? I can do it either by using the find/replace menu, or use vba.

I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)

Code:

Col A Col B 1Billing Accuracy 2Billing Time 3Credit Check Accuracy 4Credit Check Time 2.1Bill preparation

[Code]....

This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows (8.1.2.1 through 8.1.2.3) are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.

I have created a worksheet to figure out (in this case) how many sets of Buckyballs i need in order to get both a square number and a cube number. There are 216 balls per set. My worksheet so far has:

Sets Number of balls Square Cube (Number of sets) (Sets*216) (Number of balls ^0.5) (Number of balls ^(1/3))

The problem is that I am looking for the number of sets required to produce an integer in both Square and Cube columns. I have figured out that I can circle all values that are not integers. I am looking for a way to delete all rows with a decimal value or at least all cells with a decimal value and I can delete the rows from there.

I get a report several times a week consisting of several columns and some hundred rows. There must be no comma signs in column E but the reports that I get will sometimes have commas in Col E anyway.

I have a macro/vba code in another workbook that I start by a keyboard combination. This macro will adapt the look of the report, but it can't take care of the comma issue. However, I have managed to remove the comma and replace it with nothing but that is not sufficient. I want to delete the comma and all figures to the right of the comma sign. There can between 1 and 4 decimal numbers.

I need to integrate some kind of vba code that will check every cell in column E and if it finds a comma in any cell, the comma must be deleted and all the numbers to the right of the comma too.

Hope you can help me with this issue because I have been googling around for two days now... It drives me crazy.

I have posted this question here too but it has not yet been solved:

I have a weird problem with Excel. It recognises all numbers as numbers but excluding the number 1. It is only recognised as text as well as a decimals, for example 3.4. So every time I try and add these values up it completely ignores 1 and decimals.

I want to convert the number in a cell, G7, from a whole number into a decimal and then divide that decimal into a whole number in cell E7 and give me the quotient in cell E8.

I received a complaint from one of my colleague that he is facing sudden problem with Ms-Excel(2002) The problem is When he feeds number 2 in to a cell it automatically converts in to 0.02. I tried using "Decrease decimal" and "format cells" option and even through uninstalling office and reinstalling a different version but the problem still continues.

I have received an excel file from an external source. Every time I change a number in a cell it reverts to a 2 decimal number. eg. I type in 8710 and it converts the value to 87.10. I have looked at the number formats, cleared the numbers format but I keep getting the same result. I have also e-mailed the file to someone else and everything is ok on their computer. Is there a property in the excel program that I need to change?

I 'm not sure why my custom function "minimize" is not working... I tried to do this with one of excels built in functions and would prefer a solution that way, but had to go the VBA route in the mean time. x and theta are paired together and I'm trying to reduce a select number of values x by their corresponding ratio.s It's corresponding ratio is determined by what degree value theta corresponds to. I wanted to do an if statement, but got confused...

=if((B2>=$J2)*(B2<$J3)) , A2*K2 , if((B2>=$J3)*(B2<$J4) , A2*$K3 , if((B3>=$J4)*(B3<$J5) , A3*$K4, .... etc. etc. etc........................

I'm trying to clean up a very large list of last names. Only one individual cell, but that cell includes numbers, decimal points, and spaces inbetween the numbers. All I want left in the cell is the last name. I have just under 100,000 to do! How would I go about this? Using Excel 2007.....

I use a worksheet full of formulas to know what divisors a number have, but i need to see what numbers have no decimals in about 500-1000 cells. Is there a formula whitch computer can use to see if it shows a number or not (if a number have or dont have decimals)?

I have used the snippet of code below many times to delete rows of filtered data. For example, if I want to delete all rows containing "Y" in column F, this code would filter to that criteria, offset it to save the column headings, resize it to avoid deleting the row below the range, then delete the rows. This works beautifully, until I ran into an instance where there were NO "Y"'s in column F. Then it deleted the column headings.

How would I modify the code below to leave the headings in row 1, but delete any other filtered data? (Also, if there's a better way to delete filtered rows but leave headings, I would be interested.)

I need a cell to restrict the input: -Only numbers are allowed. -No date posible. -Any amount of decimals (they must all be shown in the cell).

I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".

I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.

I would like to take the below strings and remove any numbers and just leave letters. Or extract the payee from the string. If at all possible, I'd prefer this in a formula.

Card Purchase WAL Wal-Mart Sup 320004 0532 WAL-SAMS CENTRAL LA Card Purchase WAL-MART #0532 053201 308 N AIRLINE HWY CENTRAL LA Card Purchase LEBLANC'S FOOD S 874301 209 S. AIRLINE HWY CENTRAL LA[code]....

Basically, (the first line above) I'd like to remove the words "Card Purchase" and any numbers...or preferably just leave "Wal-Mart Sup" instead of the full string. I'd like the product of the first line to match the product of the 2nd line.

I have data listed in column A. The data appears in text, blanks, and phone numbers....all in different rows.

I need to capture only the phone number and 3 rows above it no matter what the text says or how many rows inbetween each phone number. Then proceed to copy and paste it to a new sheet into one column.

This needs to continue until i reach the end of column a. Eg. row 1: the fox row 2: the pig row 3: animals row 4: water row 5: land row 6: (780) 111-2222

i need to copy rows 3-6, paste to new column and then continue down column A looking for the next chunk of data to copy and paste.

So it would turn out to look like: row 1: animals row 2: water row 3: land row 4: (780) 111-2222

I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things

1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.

2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.

Let's say I have a set of values (A1:A10) where each contains a number, with a varied amount of decimal places (some may have 0, some may have 10)...how can I make a formula in cell B1 that averages A1:A10, ignoring the decimal places (rounding to the nearest whole number)

If I did the Average (=AVG(A1:A10)), I would get 94.2498. But I don't want this, I want the formula to take into account the numbers rounded to the nearest whole number, meaning, I want to take the average of..

93 94 92 95 97 98 99 100 89 86

Which would give me a value of 94.3.. In this example, there isn't much of a difference, but I was simplifying the numbers for time sake

I am trying to convert Degrees Minutes & Decimals of a minute (12 34.567') to Degrees and decimals of a degree (12.57611). I have the formula to convert latitude, that is two digits, but it doesn't want to work with longitudes, three digits. (see attachment). This is the formula that I am using:

I am looking for a formula that either accepts the number in a cell if the numbers on two other cells are "approved". In other words, I have on cell A1 my value to be tested. On cells A2 and A3 I have two numbers. On cell A4 I'm looking for a formula that copies the number on A1 if A2 is bigger then 0.25 and A3 is smaller then 0.35. In not, there's no copy or a N/A appears.