CONDITIONAL FORMATING FINDING TEXT CONTAINING SPECIFIC NUMBERS

[Guest]

I have a spreadsheet of dialled telephone number in various formats eg
"01234123456", or "00441234123456", and am trying to highlight their personal
calls using conditional formatting.

I am looking for a formula that says highlight this cell if this cell
CONTAINS "123456" ie that last six digits of the number. Some people do
however use a * at the end of the number dialled to indicate a personal call
but they are not consistent! e.g 01234123456* or 00441234123456*

Any ideas?

 

[Dav]

If your data is in cell a1 and you use the following formula as th
conditional format making sure "formula is" is selected rather tha
value try

=OR(RIGHT(A3,6)="123456",RIGHT(A3,1)="*") and format to whatever colou
you like

If you require actual phonenumbers and 123456 is just an example, yo
will have to give us more information

Regards

Da

 

[Bob Phillips]

=ISNUMBER(FIND("123456",A1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks Bob, very helpful, but what if I have a number of numbers to find for
each person eg "123456", "654321" and "456789" - Do I have to put each of
these in as a separate formula?

 

[Bob Phillips]

As long as you don't have too many, you could try this approach where the
name is in A1, number in B1

=ISNUMBER(FIND(IF(A1="Sydney","123456",IF(A1="Joe","565656")),B1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)