Formula Required Which Deletes Certain Text From Cell If Certain Terms Appear In Row

[Greenal]

Hello,

I've had a few formulas to work out and i've managed a couple but i'm stuck on this one...

Aim:

To delete certain text contained within cell H2. H2 is populated, before the formula is run with [male|female|]

Action Required From Formula

delete 'this term' from cell H2 if 'these terms' are present in row 2 but don't delete if 'these terms' are also present in row 2

Formula Spoken Out Loud

[- delete 'male|' from cell H2 IF 'pink,heart,girl,for your girlfriend,for your mum' is present in A2 to E2
- but if 'girlfriend or boyfriend,heart beating' appear in A2 to E2 do not delete & then
- delete 'female|' from cell H2 IF 'blue,beer,boy,for your boyfriend,for your dad' is present in A2 to E2
- but if 'girlfriend or boyfriend,blue bird' appear in A2 to E2 do not delete ]

Thank you in advanced for any help - it will be much appreciated

--------------------------------------
Update: Altered the direction - aim is to add text rather than deleting:

I'm wanting to look at A21:D21 to search for "term","terms" and if present enter "this"

=IF(ISNUMBER(SEARCH("unisex",A21)),"Males & Females",IF(ISNUMBER(SEARCH({"for your boyfriend","for your husband","for your son","for you niece"},A21)),"Males",IF(ISNUMBER(SEARCH({"for your girlfriend","for your wife","for your daughter","for you nephew"},A21)),"Female","Males & Females")))

It is working but only for text in A21. But can't get it to work for A21:D21...
-----------------------------------------

[Ron Coderre]

There are some ambiguities in your criteria.
For instance:
Delete "male" if "for your girlfriend"
...but...
Do NOT delete "male" if contains "girlfriend"

Is there a hierarchy of tests that you want to follow until there is a hit?

[Greenal]

Hi Ron,

Thank you for taking the time to look.

Do NOT delete "male" if contains "girlfriend"

It meant to read more as a phrase (each comma separating the 'term' to query)

Do NOT delete "male" if contains "girlfriend or boyfriend"
i.e. if this sentence was in the row [these tickets are ideal to give to your girlfriend or boyfriend]

There isn't a hierarchy as such more just a number of words and phrases to run the query through.

[Greenal]

I've altered the way i'm going to work it - to hopefully simplify it.

I'm wanting to look at A21:D21 to search for "term","terms" and if present enter "this"

=IF(ISNUMBER(SEARCH("unisex",A21)),"Males & Females",IF(ISNUMBER(SEARCH({"for your boyfriend","for your husband","for your son","for you niece"},A21)),"Males",IF(ISNUMBER(SEARCH({"for your girlfriend","for your wife","for your daughter","for you nephew"},A21)),"Female","Males & Females")))

It is working but only for text in A21. But can't get it to work for A21:D21...

(If i can solve this I can /will edit the thread title and make a note in the first post to reflect the true nature of the thread)

[Ron Coderre]

Try something like this....

With
G1:H9 containing this list

Please Login or Register  to view this content.

This regular formula returns the category that corresponds to the entry in A21:D21 (assuming there will only be one matching item)

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

[Greenal]

Try something like this....

With
G1:H9 containing this list

Please Login or Register  to view this content.

This regular formula returns the category that corresponds to the entry in A21:D21 (assuming there will only be one matching item)

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

Hi Ron,

Yes i think it is

However there is a couple of things, if you could help:
1. if it finds 'for your sonnet' it is turning male due to the 'for your son'.
2. When you say 'assuming there will be only one matching item' is there a way to:
2a) say 'if a term which = male and if a term = female are found return male & female
2b) if a term which is = male & female then this takes priority
2c) (does the 225 represent a character limit per cell?) If so could this limit be removed as text contained within some cells is over 1000 characters...
2d) there may be more than one matching item...could it look for all matches?

[Your help is appreciated...it's been a frustrating afternoon!]

[Greenal]

Hi Ron,

Yes i think it is

However there is a couple of things, if you could help:
1. if it finds 'for your sonnet' it is turning male due to the 'for your son'.
2. When you say 'assuming there will be only one matching item' is there a way to:
2a) say 'if a term which = male and if a term = female are found return male & female
2b) if a term which is = male & female then this takes priority
2c) (does the 225 represent a character limit per cell?) If so could this limit be removed as text contained within some cells is over 1000 characters...
2d) there may be more than one matching item...could it look for all matches?
2e) can a 'ignore these words' function be built in / added to a lookup table?

[Your help is appreciated...it's been a frustrating afternoon!]

a) and b) i have worked around...any help with 1, 2c, 2d and 2e would be great.