I have column of numbers used as item codes, they appear with a small green triangle in each cell. When I select all cells a little blue square pops up with drop down options to convert to number- after doing this the green triangles disappear but then numbers I had listed as eg '043340' appear as 4334. Is there way to make sure zero's are not deleted as some of my results from formulas end up being incorrect.
View 3 RepliesI am working on a check digit programme.
In cell C17 a negative numher will always appear.
In cell C18 I would like to change this negative number to a positive of the same value.
I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another
18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK
I need to store some numbers, all of which are prefixed by four or five zeros. When I format the cells as number, the zeros are erased. So, 0000012345 becomes 12345. The zeros are preserved when I store them as text, but I cannot do the functions that I need to do with the cells formatted as text.
How I can preserve the zeros in number format?
I have a column with "1" and "0". I'd like to have something like, if the value in a cell within the column is "0", then delete the 4 cells previous to the cell that contained "0" within the row.
Instead of deleting, the values can also be replaced with anything e.g., zero or n/a etc.
I am trying to format my lat and longs in order for Fusion Tables to be able to geocode them. I have it down to where I have separated the lat and longs into their own column. I started with this
>Point>-77.056124,35.552686,0.0
my long issues are -78.335,35 i need to get rid of ,35My lat issues are
434.011400
i need to get rid of the 4 in 434
I am using a userform to pull some data from a form that many different workers fill out. I have a button on the userform that automatically saves certain cell values in the form to a different workbook when they hit this button (they hit it every time after they are done filling it out)....The problem is that if they need to go back and make changes to the form they originally created, the workbook that has all the data will now have two entries for the same form number (when they hit the button it also saves the workbook as NCR####) but I only want the most recent one..
So what I need to make it so that if two numbers in column C (C2 and on) of workbook "NCR_Data.xls" match that this macro deletes the one furthest from the top (my code places newest entries at the top, in row 2).
I have a worksheet containing codes in column A. Where the first 2 digits of the code contains "78" for eg 7801, 7825, 7892 etc then the entire row must be deleted.
View 9 Replies View RelatedI am using the following code to import an excel file into an existing sheet. The problem I am having is the way some of the cells are being imported. Some of the cells have spaces before and after the number.
I need to delete the leading and trailing spaces for each cell affected. Is there a code I can incorporate to do this? BTW…the columns effected are columns F & G.
I have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series. I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.
View 5 Replies View RelatedI have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?
View 14 Replies View RelatedIn Excel 2007, when I delete a row/cell, text is associated with deleting row/cell but some lines, polygon ... can not associate with deleting row/cell . So the lines, polygon ... in mistake location.
View 12 Replies View RelatedI am using this code to populate column J with a date when yes is selected in colum I.
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = Columns("I").Column Then
If .Value = "Yes" And IsEmpty(.Offset(, 1)) Then .Offset(, 1) = Date
End If
End With
End Sub
However i also want any value entered in J to be deleted when No is selected.
My original datasheet had two separate columns, First Name & Last Name. I've combined the two columns into one so it appears Last Name, First Name. Some of the names have middle names. Is there anyway to delete the middle name.
Example: Cope, David Simon
Trying to get: Cope, David
I am using Excel on a work computer that someone else may have changed the settings on.
I want to simply delete a cell, without deleting the entire row the cell belongs to. Yet when I highlight the cell, and go to Edit -> Delete, the program asks me if I want to delete the entire row, and only gives me two options: OK or Cancel. So I can't delete just one cell (and have all of the lower cells shift up one).
When I right click, the 'delete' option is not present either. I tried changing the menu properties to add the 'delete' function, but it will not delete a cell without taking out the whole row.
I have a lot of calculations going on so I thought I would name some of the cells where factors come from to make it easier to follow.
I made a typo and named a cell wrong, is there a way to delete it? Right now I have a correct and incorrect name attached to the cell.
hey everyone
i'm using a table like this one :
A B C D
1 Main | value | value | value
2 Extra | value | value | value
3 Main | value | value | value
.
.
n Extra | value | value | value
i want to delete every cell in column "D" only when cell in column "A" contains "Extra" value.
I want to delete columns that contain a 0 value in row 2 of the column.
In my case, I have headers in row 1 and values in rows 2 and below. I eventually want to filter out the columns that contain no values below the headers.
Let me know if you need any more information for clarification.
I was wondering if ther is a way to delete what is in one cell, once something has been enterted in another?
Example A1 has In-progress written in the cell.
Once B1 has Completed.
So once B1 has been entered, the info in cell A1 will be deleted.
Creating macros in excel. I am looking for a way to delete a row at the active cell. So far this code does the job for me:
Code:
Sub slet()
ActiveSheet.Unprotect
ActiveCell.EntireRow.Delete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
End Sub
But I want to make it impossible to use this code on the first 4 rows.
I'm looking to do a search and delete in Excel 2007 and I'm having a great deal of difficulty trying to do this. I've attempted to modify some code I found on the internet and not having very good luck with it. Here is the scenario, I've got a spreadsheet with 5 columns (A to E). In column C, there is a product name with certain identifiers that set it apart. An example of this product name with identifier is "product XXX_type 2_attribute ". I want to search for "type 2" in Column C of each row and then delete the row.
Here is what I have written so far and I'm not having any luck.
Sub RowDel()
Dim cell As Range
For Each cell In Range(Range("c4"), _
Range("c65536").End(xlUp))
If cell = "_GPnl_" Then
Range(cell, _
Cells(Rows.Count, 1)).EntireRow.Delete
Exit For
End If
Next cell
End Sub
I want to delete part of a cell in Excel. For example if a cell had the data "BlueGreen" how would I delete the "Blue" bit just to leave the "Green" bit behind? Surely there is an easier way then physically deleting the first part in each cell? I have about 4000 cells to work on!
View 9 Replies View RelatedI have a pretty good sized Database and all the rows that are indented are not needed. Anyone know a way to delete all the text within a cell that is indented?
View 9 Replies View RelatedWhat I am trying to do is go through a large form and remove everything except for a certain set of cells, I am using this code
For i = 10000 To 1 Step -1
Set rng = Range("A" & i)
If rng.Value <> "Employee" Or rng.value <> " Total" Then
rng.EntireRow.Delete
End If
Next i
What I want it to do is go through the sheet and delete anything that doesnt have Employee or Total in column A, but my problem is that they dont JUST say Employee or Total, its more like Employee: Team A or Employee Group 1. The Total part always says "Total of " and a number, but the number is always different. How do I make it not delete it if it has Employee or Total anywhere in the cell?
SAMPLE FILE "error_finder.xlsx (36.5 KB)" attached...
The VP of our company needs me to create a spreadsheet.
For this s/s I need the following:
Imagine 2 sheets in Excel...
sheet1
sheet2
sheet2 has sequential numbers in column1 starting in row2 with the number 1 until wherever..., so cell A2 has number 1, cell A3 has number 2, etc...
Manually, a user has to fill in certain text next to a number.
This for example would look something like this:
_|A| B
-|-|-----------------------------------|
1| | Error-description
-|-|-----------------------------------|
2|1| Whatever the 1st error would be...|
-|-|-----------------------------------|
3|2| Whatever the 2nd error would be...|
-|-|-----------------------------------|
4|3| Whatever the 3rd error would be...|
-|-|-----------------------------------|
5|4|
-|-|-----------------------------------|
6|5|
-|-|-----------------------------------|
and so on...
All this would be on sheet2
In a cell on sheet1 I now need the number from column A displayed, that has the latest entry in column B.
In the example above this would have to be the number 3 in cell A4, because right next to it (in cell B4) is the last entry "Whatever the third error would be..."
Addition: There are several sheets, each sheet stands for one error listed on sheet1 in column A
sheet1 for example would look like this:
_|____A____|_B_|_C_|_D_|
-|---------|---|---|---|
1|_________|833|933|934|
-|---------|---|---|---|
2| error 1 |___|___|___|
-|---------|---|---|---|
3| error 2 |___|___|___|
-|---------|---|---|---|
4| error 3 |___|___| 3 |
-|---------|---|---|---|
In this example, because "whatever error" (cell B2 to B4 on sheet2) was found on press 934 was the 3rd error (cell A4), the number 3 (cell A4) would have to be populated on sheet1 in cell D4.
I work for a bank and we use an excel spreadsheet that has an in depth payment calculator used for creating monthly payment arrangements or calculating settlements on bank loans. I would like to add the following feature and I'm not quite sure exactly how to do it.
For example, imagine cell A1 will contain the full payoff of a loan. I want to be able to put a settlement offer in B1 and have C1 populate the percentage of the payoff that is. At the same time, I want to be able to delete the settlement offer in B1 and put a percentage in C1 and have it calculate that settlement offer in B1. The calculations/formulas to do these problems is simple, but what I need is to not lose the formulas in B1 and C1 if I were to go back and forth entering settlement offers vs settlement percentages.
I have a worksheet that I use to store several static lists which populate various combo boxes in my application. However, I'd also like to use this worksheet to store a single column of data which can shrink or grow dynamically.
I have no problem with finding the last cell and adding data to that cell, but I'm having trouble with how to go about deleting the value of a single cell.
So as an example, If I've populated Cells A1:A10, and I want to delete the value that's in A6, how do I go about moving cells A7:A10 up one to fill in any empty spaces?
I have a file with 1000+ rows. The values in column A have text that I want to keep and then extraneous text tacked on. The good thing is the extraneous text ALWAYS start with the same characters. How can I systematically delete the extraneous text values at the end?
View 7 Replies View RelatedI have searched around and cannot find exactly what im looking for. I have a table of data and want to delete it all by clicking a button. I only want it to delete the rows which have data in.
Example
A1 = Apple
A2 = Apple1
A3 = Apple2
A4 = Apple3
A5 = *Empty*
So it would delete A1-A4 and not delete A5.
I would like to delete rows in a worksheet based on a value of a cell within that row. The worksheet will not always have the same number of rows. I will be looking at the value of each cell in column "AD", which is a date, and if it is not equal to 00/00/0000 then I want to delete that row.
View 7 Replies View Related