Re: Too many functions?

You have hit the limit of 7 nested functions (any function, not just IF).
Generally when one need more than 7 IF conditions the solution is to use
look-up function. Read help on VLOOKUP and see if you can find a way to use
it; if not come back for more.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Guy Lydig" <GuyLydig@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A42912AA-5BBB-439C-83C0-7A5BFB3F1F45@xxxxxxxxxxxxxxxx
I have a column of first names that has some blank cells, some with 1 name,
and some with 2 names. I inserted another column and want to have only
initials but if the first or second name begin with "Ch" or "Sh", I want
the
"Ch" or "Sh" as the initial.

My function takes care of:
Blank cell
One name and the first two letters are Ch or Sh
One name and the first two letter are NOT Ch or Sh
Two names and the first and second names both begin with Sh or Ch
Two names and the first begins with Ch or Sh but not the second

Here's where the problem begins:
There are TWO other possibilities: the second name begins with Ch or Sh
but
not the first OR there are two names and neither begins with Ch or Sh.

If I pretend there's only ONE other possibility (and put the last bit in
the
If False part of the If function) all is well except that either I will
have
"Ben Bob" as "B. Bo." OR "Ben Charles" as "B. C." depending on how I write
the last piece.

I should really be able to add one more piece to the formula to fix this
but
every time I do, the formula sticks. Is there a limitation on the number
of
Find, Mid, etc. functions?

Here's the formula I used (which ignores the case of only the 2nd of two
names beginning with "Ch" or "Sh"):
=IF(ISBLANK(B2),"",IF(AND(ISERROR(FIND("
",B2)),OR(LEFT(B2,2)="Sh",LEFT(B2,2)="Ch")),LEFT(B2,2)&".",IF(ISERROR(FIND("
",B2)),LEFT(B2,1)&".",IF(AND(FIND("
",B2),OR(LEFT(B2,2)="Sh",LEFT(B2,2)="Ch"),OR((MID(B2,FIND("
",B2)+1,2)="Ch"),MID(B2,FIND(" ",B2)+1,2)="Sh")),LEFT(B2,2)&".
"&MID(B2,FIND(" ",B2)+1,2)&".",IF(AND(FIND("
",B2),OR(LEFT(B2,2)="Sh",LEFT(B2,2)="Ch")),LEFT(B2,2)&". "&MID(B2,FIND("
",B2)+1,1)&".",LEFT(B2,1)&". "&MID(B2,FIND(" ",B2)+1,1)&".")))))

I tried to amend it to:
=IF(ISBLANK(B2),"",IF(AND(ISERROR(FIND("
",B2)),OR(LEFT(B2,2)="Sh",LEFT(B2,2)="Ch")),LEFT(B2,2)&".",IF(ISERROR(FIND("
",B2)),LEFT(B2,1)&".",IF(AND(FIND("
",B2),OR(LEFT(B2,2)="Sh",LEFT(B2,2)="Ch"),OR((MID(B2,FIND("
",B2)+1,2)="Ch"),MID(B2,FIND(" ",B2)+1,2)="Sh")),LEFT(B2,2)&".
"&MID(B2,FIND(" ",B2)+1,2)&".",IF(AND(FIND("
",B2),OR(LEFT(B2,2)="Sh",LEFT(B2,2)="Ch")),LEFT(B2,2)&". "&MID(B2,FIND("
",B2)+1,1)&".",IF(AND(FIND(" ",B2),OR((MID(B2,FIND("
",B2)+1,2)="Ch"),MID(B2,FIND(" ",B2)+1,2)="Sh")),LEFT(B2,1)&".
"&MID(B2,FIND(" ",B2)+1,2)&".",LEFT(B2,1)&". "&MID(B2,FIND("
",B2)+1,1)&".")))))

but it's hanging on MID after the last OR.

I'm stumped. Any ideas why I can't fix this one glitch?

I'd appreciate any assistance. (I hate to admit defeat and ask but....)
Sorry for the length of this post.

TIA

Guy


.

Relevant Pages

  • Re: My Macbook Saved the Day - Sort of
    ... support rather than hit the beach, and take a few tours of the island. ... I'd fix the problem myself. ... you seem like the kind of guy would rather sit in his hotel room ... Nor have I ever called a vendor to resolve a Windows ...
    (comp.sys.mac.advocacy)
  • Re: simple wildcard regex problem.
    ... This is basic programming, ... Do you only want to get a count of those characters (letters, ... hit n to bail out, ... push @guesswords, $_; ...
    (perl.beginners)
  • Re: Among the Pleasures of Staying Put
    ... Whatever happened to "if it ain't broke, don't fix it"? ... Haven't hit one on the move yet. ... What have you got against the ground squirrels? ...
    (rec.outdoors.rv-travel)
  • Re: MY CATS SCREWED UP MY LAPTOP!!!
    ... I left my laptop open and on, downloading some big emails (still on dial-up) while I went to walk the dog. ... cats got up on there and hit some key combination. ... I've tried a re-boot and that did not fix it. ... Look for the slider that sets resolutions. ...
    (alt.sys.pc-clone.dell)
  • Re: the "exclusive access" prob. -- for disc scan.
    ... Hit Y and at this point, then close the command prompt and restart ... Associate Expert - WindowsXP Expert Zone ... > When I check the "fix problem" box in disc scan, ...
    (microsoft.public.windowsxp.general)