Variable

Wet_Blanket

Registered User.
Local time
Today, 13:34
Joined
Dec 3, 2008
Messages
113
Access has rotten my brain. Whenever I need to write a formula in Excel, it is a challenge to remember.

All I want to do is create an IF THEN statement that recognizes words within a colum. The idea is that when "Pension" appears in a column, it would do something. But "Pension" only appears when it is part of a name, ie. ABC Company Pension.

What I have is:

=IF(E38="Pension",E38," ")

This of course doesn't work. I've tried many different things, but I can't remember the variable key for Excel.
 
Access has rotten my brain. Whenever I need to write a formula in Excel, it is a challenge to remember.

All I want to do is create an IF THEN statement that recognizes words within a colum. The idea is that when "Pension" appears in a column, it would do something. But "Pension" only appears when it is part of a name, ie. ABC Company Pension.

What I have is:

=IF(E38="Pension",E38," ")

This of course doesn't work. I've tried many different things, but I can't remember the variable key for Excel.

Your IF() Statement would be perfect if Column E38 contained EXACTLY the word Pension. Since you seem to want to include values that include the word Pension as well as other words, then your IF() Statement will not be enough, and you will need to include a SEARCH statement. Try doing this and then get back to us.
IF( Condition to Test, Value If True, Value If False )

SEARCH( Text to Find, Text to Search, Starting Position )
 
Can I put the search function into the IF statement? If yes, how?
 
Can I put the search function into the IF statement? If yes, how?

Try making the SEARCH() Function the Condition to Test part of the IF() Function. It will return 0 if the String is not Found, and the Starting Position of the String when it is found.
 
=IF(SEARCH("Pension",(E38),1),E38,"")

Worked like a charm.

Riddle me this. What if there are a series of words I am looking for like:
Pension
Company
401k
?

THanks for your help btw.
 
=IF(SEARCH("Pension",(E38),1),E38,"")

Worked like a charm.

Riddle me this. What if there are a series of words I am looking for like:
Pension
Company
401k
?

THanks for your help btw.

Perhaps joining more than one IF() Statements using OR() Statements will provide what you are looking for?
OR( Logical Test #1, Logical Test #2, Logical Test #3, ... )
 
Perhaps joining more than one IF() Statements using OR() Statements will provide what you are looking for?

I tried the OR and got an error. I was thinking of doing another IF, but I can see that I will have to do a DLOOKUP (or VLOOKUP or HLOOKUP).
 
I tried the OR and got an error. I was thinking of doing another IF, but I can see that I will have to do a DLOOKUP (or VLOOKUP or HLOOKUP).

The format for the OR() Statement that I provided should work for you.
 
Try making the SEARCH() Function the Condition to Test part of the IF() Function. It will return 0 if the String is not Found, and the Starting Position of the String when it is found.

I think that it returns a #VALUE error if the search string is not found

Brian
 
I think that it returns a #VALUE error if the search string is not found

Brian

In that case, then the OP could use the ISERROR() Command to check for the error and return 0.
ISERROR( Value or Condition to Test ) { 1 = Error; 0 = No Error }
 
If you were only looking for one string I would have used
=IF(ISERROR(SEARCH("pension",e38)),no pension action,pension action)

however as you are looking for more I would write a simple function using Instr to look for the data, that does return 0 if the string is not found.

How many strings of data are there likely to be?

Brian
 
Getting an error on this:

=IF(iserror(search(“Pension” ,(E38),1)),E38,"")
 
If you were only looking for one string I would have used
=IF(ISERROR(SEARCH("pension",e38)),no pension action,pension action)

however as you are looking for more I would write a simple function using Instr to look for the data, that does return 0 if the string is not found.

How many strings of data are there likely to be?

Brian

Its a whole mess. Let me explain.

I need to edit a colum of names, to drop off part of the name. But in some instances, I don't need to do anything. Examples:

Original Intended Result
John and Joan Smith Smith
Harold Buggy Trust Buggy Trust
ABC Company Pension ABC Company Pension
Jack Smith IRA Smith IRA

I was hoping to do IF THEN statements to go through and clean it up, but as you can tell, it is a mess.

I would do something better, and not involving Excel, but this is for an end user that isn't advanced - so I just want to keep it to copying and pasting a formula into an excel spreadsheet (or very little input from them).
 
Getting an error on this:

=IF(iserror(search(“Pension” ,(E38),1)),E38,"")

Two questions here:
  1. The word Pension seems to be surrounded by the wrong type of Quotes. This could be a Type-O, but it might create problems if it is not.
  2. The ISERROR Test will return the calue of E38 when there is an error, and nothing if there is not. I think this needs to bne reversed.
 
In the code you had the True False the wrong way
=IF(iserror(search(“Pension” ,E38)),"",e38)

Brian
 
Its a whole mess. Let me explain.

I need to edit a colum of names, to drop off part of the name. But in some instances, I don't need to do anything. Examples:

Original Intended Result
John and Joan Smith Smith
Harold Buggy Trust Buggy Trust
ABC Company Pension ABC Company Pension
Jack Smith IRA Smith IRA

I was hoping to do IF THEN statements to go through and clean it up, but as you can tell, it is a mess.

I would do something better, and not involving Excel, but this is for an end user that isn't advanced - so I just want to keep it to copying and pasting a formula into an excel spreadsheet (or very little input from them).

Maybe you could write a VBA Function that does what you want that the user could use the same way that they use any other Excel Function.
 
I see Access Rookie had replied, you need to read up on Search and Iserror.
The iserror returns TRUE if an error occurs in the formula the IF is testing for that.

Brian
 

Users who are viewing this thread

Back
Top Bottom