Thread: part of string
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
  
Posts: 5,651
Default part of string
On 14 Nov 2005 23:13:53 -0800, wrote:

it's just that First Word Matches are considered duplicates. Could you
tell me, is the formula finding words left of the space between dundee
united.

Yes. That's what I mean by "First Word". It's the first sequence of
characters in the string that is terminated by a <space.

Also can this be entered as a conditional format to highlight
the cells.

Not directly. I don't think you can use array formulas in the Conditional
Format dialog box.

However, here are two, simpler, NON-Array formulas that can be used in both
instances.

To count the number of "First Word" matches:

=COUNTIF(Teams,IF(ISERROR(FIND(" ",A3)),A3,LEFT(A3,FIND(" ",A3)-1))&"*")

and copy/drag down.

To use in Condtional Formatting:

=COUNTIF(Teams,IF(ISERROR(FIND(" ",$A3)),$A3,LEFT($A3,FIND(" ",$A3)-1))&"*")1


--ron
Reply With QuoteReply With Quote