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?

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

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

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

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=531472

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Twiggy" wrote in message
...
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?

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" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Twiggy" wrote in message
...
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?

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)

"Twiggy" wrote in message
...
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" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Twiggy" wrote in message
...
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?