CONDITIONAL FORMATING FINDING TEXT CONTAINING SPECIFIC NUMBERS
 

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?

CONDITIONAL FORMATING FINDING TEXT CONTAINING SPECIFIC NUMBERS
 

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

CONDITIONAL FORMATING FINDING TEXT CONTAINING SPECIFIC NUMBERS
 

=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?

CONDITIONAL FORMATING FINDING TEXT CONTAINING SPECIFIC NUMBERS
 

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?

CONDITIONAL FORMATING FINDING TEXT CONTAINING SPECIFIC NUMBERS
 

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?