Finding text within a cell

[kasperblue]

Hello,

I've got the following formula:

=IF(LEFT(G2,1)="7","Mobile","")

Which basically tells me if the number in G2 starts with a 7, it must be a mobile number.

BUT I need to insert another criteria which is, if cell I2 (description column) includes the words "personal" or "PNS" then its a personal number.

I cant seem to get the formula to work with a FIND insert, could anyone help please?

Many many thanks

[Søren Larsen]

Is it something like this you are lokking for?

Please Login or Register  to view this content.

EDIT: =SEARCH() is not case sensitive.

[Ace_XL]

=IF(LEFT(G2,1)="7","Mobile","")&IF(ISERROR(FIND("personal",I2)),IF(ISERROR(FIND("PNS",I2)),""," Personal")," Personal")

[kasperblue]

Hello,
Thanks for your responses but I cant quite get this to work - the formula doesnt error, but just returns a blank. I've got an example of my spreadsheet below

Column G Column H Column J Column K
7777777777 Personal Number
7777777778


In the first row i'd want column K to repond as "Personal" as its a number that starts with 7 and has the word "personal" or "pns" in the description(column J). In the second row i'd want column K to repond as "Mobile" as its a number that starts with 7 and doesnt have the work "personal" or "pns" in the description.

Does this make sense?

[Ace_XL]

In that case..

=IF(ISERROR(FIND("Personal",H2)),IF(ISERROR(FIND("PNS",H2)),IF(LEFT(G2,1)="7","Mobile",""),"Personal"),"Personal")

[martindwilson]

this was wrong

[kasperblue]

Yes, that works, thank you so much.

The only issue i'm contending with now is case sensitive. The formula works for cells with"Personal" but not with "personal" - is there an easy fix?

[Ace_XL]

Maybe..

=IF(ISERROR(FIND("Personal",PROPER(H2))),IF(ISERROR(FIND("PNS",H2)),IF(LEFT(G2,1)="7","Mobile",""),"Personal "),"Personal")

or even better

=IF(ISERROR(FIND("PERSONAL",UPPER(H2))),IF(ISERROR(FIND("PNS",UPPER(H2))),IF(LEFT(G2,1)="7","Mobile",""),"Personal "),"Personal")

[martindwilson]

=IF(LEFT(G1)="7",IF(ISNUMBER(LOOKUP(2,1/SEARCH({"personal","pns"},H1))),"personal","mobile"),"")
btw search is not case sensitive you can use that instead of find