Hello,
I have to come up with a way to count the number of words in a cell (they
are actually names, separated by a space), so that I can find any cells that
have more than 3 names.
Thanks in advance for your replies.
Phil.
Hello,
I have to come up with a way to count the number of words in a cell (they
are actually names, separated by a space), so that I can find any cells that
have more than 3 names.
Thanks in advance for your replies.
Phil.
Perhaps with something like this:
For a name in A1
B1: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Pro
"Phil" wrote:
> Hello,
>
> I have to come up with a way to count the number of words in a cell (they
> are actually names, separated by a space), so that I can find any cells that
> have more than 3 names.
>
> Thanks in advance for your replies.
>
> Phil.
You would need to count the spaces then, so any 3 and more names in a cell
would have at least 2 spaces
You could use a help column and a formula like
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))>=2
copy down, apply autofilter and filter on TRUE will give all those cells
or use conditional formatting formula is and the high light those cells
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
"Phil" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have to come up with a way to count the number of words in a cell (they
> are actually names, separated by a space), so that I can find any cells
> that
> have more than 3 names.
>
> Thanks in advance for your replies.
>
> Phil.
Ron,
Thank you. That worked perfectly.
"Ron Coderre" wrote:
> Perhaps with something like this:
>
> For a name in A1
> B1: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Phil" wrote:
>
> > Hello,
> >
> > I have to come up with a way to count the number of words in a cell (they
> > are actually names, separated by a space), so that I can find any cells that
> > have more than 3 names.
> >
> > Thanks in advance for your replies.
> >
> > Phil.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks