If statement

[Secret Squirrel]

I'm trying to write an IF statement using a text value but it's not returning
what I need. Is there something special I need to do with the format of the
field I'm using in my statement?

Formula: =IF(E2="Closed","",U2)

All I'm saying is that if the value in E2 equals "Closed" then put nothing
but if it's not true then copy the value from U2. The value in U2 is a dollar
amount. It's returning the false value for this when the actual value in E2
is Closed. Am I missing something?

 

[Gary''s Student]

Check that E2 is EXACTLY what's in the formula and that there is no extra
blank after it like "Closed "

 

[Dave Peterson]

I'd guess that there's more stuff in E2 than just Closed. Any leading/trailing
white space?

if you type:
=len(e2)
do you get 6?

And don't forget to check for a simple spelling error.

 

[Secret Squirrel]

Yep it's exactly the way it needs to be. No extra spaces or anything like
that. The format for E2 is set to Text. What else can I check?

 

[Secret Squirrel]

No I get 20. Why is that? The spelling is correct and there are no leading or
trailing spaces.

 

[Gary''s Student]

Check the formula bar as well as the cell. There is extra stuff hidden in
there somewhere.

 

[Secret Squirrel]

Found it. When I clicked in the formula bar there were extra spaces trailing
the word. How do I get rid of those for all my rows? I have over 1000 rows.
I'm importing this data from my MRP system so maybe it's bringing them in
during the import?

 

[driller]

use the fx button to have your formula entered properly - maybe

 

[David Biddulph]

Try the TRIM() function.
--
David Biddulph

Found it. When I clicked in the formula bar there were extra spaces
trailing
the word. How do I get rid of those for all my rows? I have over 1000
rows.
I'm importing this data from my MRP system so maybe it's bringing them in
during the import?

 

[Gary''s Student]

If they are truly spaces (and not some other non-printing character), then
you can use

Edit > Find and then Replace to remove either all of them or some of them.

 

[Dave Peterson]

if the data is in one column, you can select that column and do
data|text to columns and then click finish.

(or do this for each column to be fixed).

Or you could modify your formula:

=IF(E2="Closed","",U2)
becomes
=IF(trim(E2)="Closed","",U2)
or
=IF(trim(E2)="Closed","",trim(U2))

Personally, I'd try to clean up the data and not try to modify the
formulas--it's just too easy to add a formula and forget that the data needs to
be cleaned up (again and again and again...)

 

[Secret Squirrel]

They are in fact spaces. I did remove them for some of the cells but how do I
know how many spaces there are on the other cells? For example if the text
"Closed" is in that cell then there are 14 spaces after it. It the word
"Open" is in there then there are 16 spaces. Is there any way just to remove
them all without figuring out how many there are after every word?

 

[Secret Squirrel]

What is the TRIM function?

Try the TRIM() function.

 

[David Biddulph]

What does Excel's help tell you when you ask about TRIM?
--
David Biddulph

What is the TRIM function?

 

[Secret Squirrel]

No need to be a jerk about it.

What does Excel's help tell you when you ask about TRIM?

 

[driller]

Hi David Bid, in english you are correct, the If function easily trace the
data content himself when the formula writer is cautious enough before
reproducing basic IF formulas produce for thousands of line.....Nice problem
squirrel....hope u can give more....

 

[Dave Peterson]

If there are no other spaces in the cell (like: "in work "), you could do
this:

Select the range
edit|replace
what: (spacebar)
with: (leave blank)
Make sure that the "match entire cell contents" box under the Options button is
not checked.
Then click
replace all

If you do have "In Work " in a cell, it will become "InWork". So be careful.

 

[Secret Squirrel]

Thanks! I'm trying to come up with more problems for you guys! I just wish I
could learn as much as you.

 

[Gary''s Student]

If you feel comfortable with VBA, this small macro will find all the spaces
in the worksheet and remove them:

Sub SpaceAttacker()
Dim r As Range, rr As Range
Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
For Each r In rr
r.Value = Application.Substitute(r.Value, " ", "")
Next
End Sub

It changes only constants, not formulas

 

[Secret Squirrel]

Yes I am comfortable with VBA. I will give this a shot. Thanks for all your
help!

SS