"clear Contents" Function For A Cell? (solved) 
"clear Contents" Function For A Cell? (solved)  Excel 

I'm trying to set up a basic formula to clear out unwanted cells. Basically, if the cell is not equal a number, I'd like it to be cleared of any information. I would rather not use a space, because I have text that is overlapping between cells and would like it to be legible.
Here's the basic formula:
=if(A1>0, A1, ???)
Any help would be great. Thanks!
Similar Topics
I'm hoping someone can help me.
I need to be able to clear the contents of cells in column C, D, E, H if the value of column Q equals HELLO for arguments sake.
Clearing the contents of an entire row is not a problem, however when it comes to resizing rows I seem to come unstuck.
Related thread: http://www.mrexcel.com/forum/showthread.php?t=476993
Any help would be appreciated.
(The reason I need to do this is for importing into Access, the database treats empty cells as NULL which is what I want. Blank (but not empty) cells screw the import process up.)
=IF(a1=0,"ERROR","")
The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrowdown because it seems to think that these empty cells have contents.
Is there a way to designate, in such a formula as above, to return a TRULY empty cell?
Thanks
I'm using the formula below but keep getting #VALUE results
B1: =LEFT(A1,FIND(",",A1,1))
I know it's not that hard but can't figure it out.
thanks for the help
max
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" > 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
my text entries are rather lengthy. The past two weeks these lengthy entries
are showing up as pound signs (#########) when I click off the cell. I know
the text will fit in the cell, and the problem isn't solved by making the
cell bigger or using a little bit less text. I have the cells formatted as
"text" and "wrap to fit". I have printed the pages and the printed version
also has pound signs. I just want my text to show up!
I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:
I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.
I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?
If somebody could help me out that would be great.
Thanks,
Randy
The question:
How can you prevent a cell's contents from overflowing into the next cell?
Of course, you can make the column wider or turn on text wrapping, but you might not want to. Each of those solutions can mess up the layout of your sheet. You may just want to truncate the value.
Some people have suggested putting a space in the next cell. This is unnecessary, a pain in the but, and will mess up any ISBLANK type formulas, among other things.
The solution:
Select the cells in question and turn on text wrapping (Format>Cells>Alignment>Wrap Text).
Now select the row(s) in question and manually set the row height, by right clicking the row number and selecting "Row Height". Check the height of an adjacent row for a good value.
Your cells will now not spill over either horizontally or vertically. They will simply truncate anything that doesn't fit.
Be careful now, because parts of your data may be hidden. This can cause its own set of problems if one or two digits are neatly hidden away. Think ahead if other people might be using this sheet, and not be expecting to have some data hidden.
Tested in Excel 2002
Examples
Webb Christopher
Greer Nancy
I need to find cells with text that have more than one space between the first and last names; and cells with text that have only one space between the first and last names.
I am fairly new to excel. I can find the space after the first word but cannot tell it to find ones that say have a space then another string of text.
Thank you for your help!
On the attached timesheet there are columns IN, OUT, IN,OUT
The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a 12 hour answer.
looking at cell 'C11 ' in the attached worksheet, I currently have the formula
=IF(C13=0,","SUM(C9C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show 12:00
Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.
The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success
Thanks
=IF(C13=0,","SUM(C9C8))OR,IF(C9,ISBLANK,"",SUM(C9C8)
Are there any ways around this so that it updates upon dragging the formula?
I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!
In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:
=Sheet1!A3 (or whichever cell it is)
That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).
If anyone could point me in the right direction, I'd be very very grateful. Thanks.
I have the following code to put data from a VBA userform into Excel
Dim Sh As Worksheet
Dim Rng As Range
Set Sh = ActiveSheet
Set Rng = Sh.Range("A65536").End(xlUp).Offset(1, 0)
With Rng
.Cells(1, 1) = Surname.Text
.Cells(1, 2) = forename.Text
.Cells(1, 3) = datein.Text
.Cells(1, 4) = origin.Text
.Cells(1, 5) = Addressee.Value
.Cells(1, 6) = usual.Value
.Cells(1, 7) = dateto.Text
.Cells(1, 8) = permission.Value
.Cells(1, 9) = dateseen.Text
.Cells(1, 10) = requestview.Value
.Cells(1, 11) = Invoice.Value
.Cells(1, 12) = notes.Text
.Cells(1, 13) = datecompleted.Text
.Cells(1, 14) = holdsend.Value
.Cells(1, 15) = fee.Text
.Cells(1, 16) = notes2.Text
.Cells(1, 17) = dateseen.Text
.Cells(1, 18) = invoicesent.Text
.Cells(1, 19) = Paid.Text
.Cells(1, 20) = Complete.Value
End With
What I want to do next is click on the surname on the speadsheet and call up the userform with the fields complete for that person. I would also like to be able to edit/update the form and update the information on the speadsheet accordingly.
Any help would be greatly appreciated!
Many thanks!
sometimes, it takes 45 seconds to calc the whole sheet, other times, it takes 5 minutes. how do i clear the cache, or what do i have to do so that it calcs 45 seconds each time.
i don't want the application to think, it would be better if those 15,000 cells can be returned in a fractionofasecond. any ideas/tips? thanks.
So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.
Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?
Here is my issue:
I have a list of SKUs in Excel and I need to add one same word to 500 cells that have preexisting text in the cells. See example below:
1) List of SKU's in cells:
34
35
39
55
2) text that needs to be added IN FRONT of every number:
DF
So the result would be:
DF34
DF35
DF39
etc.
How do I do that in a formula and not manually? I found another similar thread and I understand how to do it from the instructions there but the same text goes on the back and the result is: 34DF but I need it to be like: DF34.
I am using Office 2007 for mac.
Thank you for the help,
Chris
Try this...
There are some valid datas in the cells A2 (Eg. 100), B2(Eg. 200), C2 and D2.
Data validation is used in cell K2 to limit it to a set of values.
Can we implement this formula?
IF(K2="Approved")
{
A2=A3; B2=B3; C2=C3; D2=D3; K2=K3;
}
ELSE
{
no change to any values.
}
Thanks in advance...
E.g.
Manchester United 3  0 Arsenal
Prediction: 4  1
Points: 1
I've found a formula online that works for most scores; the correct score (e.g. 3  0), a correct win (e.g. 4  1), and in the case of a draw (e.g Man Utd 1  1 Arsenal, and the prediction 2 2), correct draw (but not the correct score). The points are as follows: Correct draw (Prediction = result): 4 Correct win (Prediction = result): 3 Correct win, incorrect score (Winner correct, score not): 1 Correct draw, incorrect sco 1
The formula I found online for all but the correct draw is: =IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3>B3,C3>D3),1,IF(AND(A3<B3,C3<D3),1,0)))))
Could someone help me work out how I can write a formula to add before the above that will check the contents of four cells (the home score, away score, home prediction, away prediction), and if the number in all 4 cells are the same, allocate 4 points? I've tried using averages, but some combinations would void this. I've also used the SUM function, but the same happens, as with MULTIPLYING.
Something like =IF(A3:D3) are equal, 4,0
Below are a few examples of what I mean:
(Result) (Prediction) (Points)
A B C D E
3  3 3  3 4
2  1 3  1 1
1  0 1  0 3
3  3 2  2 1
Apologies in advance if I haven't explained this clearly enough.
Thanks
Will
The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!
Code:
Dim myRange As Range Dim cell As Range Set myRange = Range("V6:V50000") For Each cell In myRange If cell.Value < 2 Then cell.Font.ColorIndex = 5 If cell.Value < 1 Then cell.Font.ColorIndex = 3 Next