software

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 quot;Chquot; or quot;Shquot;, I want the
quot;Chquot; or quot;Shquot; 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
quot;Ben Bobquot; as quot;B. Bo.quot; OR quot;Ben Charlesquot; as quot;B. C.quot; 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 quot;Chquot; or quot;Shquot;):
=IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
quot;,B2) 1,1)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot; quot;,B2) 1,1)amp;quot;.quot;)))))

I tried to amend it to:
=IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
quot;,B2) 1,1)amp;quot;.quot;,IF(AND(FIND(quot; quot;,B2),OR((MID(B2,FIND(quot;
quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,1)amp;quot;.
quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot;
quot;,B2) 1,1)amp;quot;.quot;)))))

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

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

quot;Guy Lydigquot; gt; wrote in message
...
gt;I have a column of first names that has some blank cells, some with 1 name,
gt; and some with 2 names. I inserted another column and want to have only
gt; initials but if the first or second name begin with quot;Chquot; or quot;Shquot;, I want
gt; the
gt; quot;Chquot; or quot;Shquot; as the initial.
gt;
gt; My function takes care of:
gt; Blank cell
gt; One name and the first two letters are Ch or Sh
gt; One name and the first two letter are NOT Ch or Sh
gt; Two names and the first and second names both begin with Sh or Ch
gt; Two names and the first begins with Ch or Sh but not the second
gt;
gt; Here's where the problem begins:
gt; There are TWO other possibilities: the second name begins with Ch or Sh
gt; but
gt; not the first OR there are two names and neither begins with Ch or Sh.
gt;
gt; If I pretend there's only ONE other possibility (and put the last bit in
gt; the
gt; If False part of the If function) all is well except that either I will
gt; have
gt; quot;Ben Bobquot; as quot;B. Bo.quot; OR quot;Ben Charlesquot; as quot;B. C.quot; depending on how I write
gt; the last piece.
gt;
gt; I should really be able to add one more piece to the formula to fix this
gt; but
gt; every time I do, the formula sticks. Is there a limitation on the number
gt; of
gt; Find, Mid, etc. functions?
gt;
gt; Here's the formula I used (which ignores the case of only the 2nd of two
gt; names beginning with quot;Chquot; or quot;Shquot;):
gt; =IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
gt; quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
gt; quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt; quot;,B2) 1,1)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot; quot;,B2) 1,1)amp;quot;.quot;)))))
gt;
gt; I tried to amend it to:
gt; =IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
gt; quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
gt; quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt; quot;,B2) 1,1)amp;quot;.quot;,IF(AND(FIND(quot; quot;,B2),OR((MID(B2,FIND(quot;
gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,1)amp;quot;.
gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt; quot;,B2) 1,1)amp;quot;.quot;)))))
gt;
gt; but it's hanging on MID after the last OR.
gt;
gt; I'm stumped. Any ideas why I can't fix this one glitch?
gt;
gt; I'd appreciate any assistance. (I hate to admit defeat and ask but....)
gt; Sorry for the length of this post.
gt;
gt; TIA
gt;
gt; Guy
Dude, what a nightmare! lt;vbggt;

If these are the possible entries: (as per your other post)

........A...............................Result
S. C..................................S. C.
Charles.............................Ch.
Sharlene............................Sh.
(empty).....................................
Ben....................................B.
Ben Charles........................B. Ch.
Sharlene Charley................Sh. Ch.
Ben James..........................B. J.
Charlie Ben........................Ch. B.
D.......................................D.
Charlie Sharlie....................Ch. Sh.

=IF(A11=quot;quot;,quot;quot;,IF(OR(LEFT(A11,2)={quot;chquot;,quot;shquot;}),LEFT( A11,2)amp;quot;.quot;,quot;quot;))amp;IF(LEN(A11),IF(LEFT(A11,2)lt;gt;quot;chquot;,I F(LEFT(A11,2)lt;gt;quot;shquot;,LEFT(A11)amp;quot;.quot;,quot;quot;),quot;quot;),quot;quot;)amp;quot;
quot;amp;IF(ISNUMBER(FIND(quot; quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
quot;,A11)),quot;quot;),2)=quot;chquot;,quot;Ch.quot;,quot;quot;),quot;quot;)amp;IF(ISNUMBER(FIND (quot;
quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
quot;,A11)),quot;quot;),2)=quot;shquot;,quot;Sh.quot;,quot;quot;),quot;quot;)amp;IF(ISNUMBER(FIND (quot;
quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
quot;,A11)),quot;quot;),2)lt;gt;quot;chquot;,IF(ISNUMBER(FIND(quot;
quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
quot;,A11)),quot;quot;),2)lt;gt;quot;shquot;,MID(A11,FIND(quot; quot;,A11) 1,1)amp;quot;.quot;,quot;quot;),quot;quot;),quot;quot;),quot;quot;)

What a thing of beauty, eh?

Watch out for line wrap possibly quot;disguisingquot; quot; quot; as quot;quot;.

Biff

quot;Guy Lydigquot; gt; wrote in message
...
gt;I have a column of first names that has some blank cells, some with 1 name,
gt; and some with 2 names. I inserted another column and want to have only
gt; initials but if the first or second name begin with quot;Chquot; or quot;Shquot;, I want
gt; the
gt; quot;Chquot; or quot;Shquot; as the initial.
gt;
gt; My function takes care of:
gt; Blank cell
gt; One name and the first two letters are Ch or Sh
gt; One name and the first two letter are NOT Ch or Sh
gt; Two names and the first and second names both begin with Sh or Ch
gt; Two names and the first begins with Ch or Sh but not the second
gt;
gt; Here's where the problem begins:
gt; There are TWO other possibilities: the second name begins with Ch or Sh
gt; but
gt; not the first OR there are two names and neither begins with Ch or Sh.
gt;
gt; If I pretend there's only ONE other possibility (and put the last bit in
gt; the
gt; If False part of the If function) all is well except that either I will
gt; have
gt; quot;Ben Bobquot; as quot;B. Bo.quot; OR quot;Ben Charlesquot; as quot;B. C.quot; depending on how I write
gt; the last piece.
gt;
gt; I should really be able to add one more piece to the formula to fix this
gt; but
gt; every time I do, the formula sticks. Is there a limitation on the number
gt; of
gt; Find, Mid, etc. functions?
gt;
gt; Here's the formula I used (which ignores the case of only the 2nd of two
gt; names beginning with quot;Chquot; or quot;Shquot;):
gt; =IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
gt; quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
gt; quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt; quot;,B2) 1,1)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot; quot;,B2) 1,1)amp;quot;.quot;)))))
gt;
gt; I tried to amend it to:
gt; =IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
gt; quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
gt; quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt; quot;,B2) 1,1)amp;quot;.quot;,IF(AND(FIND(quot; quot;,B2),OR((MID(B2,FIND(quot;
gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,1)amp;quot;.
gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt; quot;,B2) 1,1)amp;quot;.quot;)))))
gt;
gt; but it's hanging on MID after the last OR.
gt;
gt; I'm stumped. Any ideas why I can't fix this one glitch?
gt;
gt; I'd appreciate any assistance. (I hate to admit defeat and ask but....)
gt; Sorry for the length of this post.
gt;
gt; TIA
gt;
gt; Guy
One minor glitch:

Cells that do not contain a space:

gt; Charles
gt; Sharlene
gt; (empty)
gt; Ben

Will return a space concatenated to the end of the string:

gt; Charles.............Ch.lt;spgt;
gt; Sharlene............Sh.lt;spgt;
gt; (empty)..............lt;spgt;
gt; Ben....................B.lt;spgt;

Trying to wrap that whole formula inside TRIM causes a general formula error
(probably nesting limit).

Maybe you can find a better way to do this and eliminate that problem. If
not, maybe you can hide the column this formula is in and use another column
with the helper formula of:

=TRIM(A11)

Biff

quot;Biffquot; gt; wrote in message
...
gt; Dude, what a nightmare! lt;vbggt;
gt;
gt; If these are the possible entries: (as per your other post)
gt;
gt; .......A...............................Result
gt; S. C..................................S. C.
gt; Charles.............................Ch.
gt; Sharlene............................Sh.
gt; (empty).....................................
gt; Ben....................................B.
gt; Ben Charles........................B. Ch.
gt; Sharlene Charley................Sh. Ch.
gt; Ben James..........................B. J.
gt; Charlie Ben........................Ch. B.
gt; D.......................................D.
gt; Charlie Sharlie....................Ch. Sh.
gt;
gt; =IF(A11=quot;quot;,quot;quot;,IF(OR(LEFT(A11,2)={quot;chquot;,quot;shquot;}),LEFT( A11,2)amp;quot;.quot;,quot;quot;))amp;IF(LEN(A11),IF(LEFT(A11,2)lt;gt;quot;chquot;,I F(LEFT(A11,2)lt;gt;quot;shquot;,LEFT(A11)amp;quot;.quot;,quot;quot;),quot;quot;),quot;quot;)amp;quot;
gt; quot;amp;IF(ISNUMBER(FIND(quot; quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
gt; quot;,A11)),quot;quot;),2)=quot;chquot;,quot;Ch.quot;,quot;quot;),quot;quot;)amp;IF(ISNUMBER(FIND (quot;
gt; quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
gt; quot;,A11)),quot;quot;),2)=quot;shquot;,quot;Sh.quot;,quot;quot;),quot;quot;)amp;IF(ISNUMBER(FIND (quot;
gt; quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
gt; quot;,A11)),quot;quot;),2)lt;gt;quot;chquot;,IF(ISNUMBER(FIND(quot;
gt; quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
gt; quot;,A11)),quot;quot;),2)lt;gt;quot;shquot;,MID(A11,FIND(quot; quot;,A11) 1,1)amp;quot;.quot;,quot;quot;),quot;quot;),quot;quot;),quot;quot;)
gt;
gt; What a thing of beauty, eh?
gt;
gt; Watch out for line wrap possibly quot;disguisingquot; quot; quot; as quot;quot;.
gt;
gt; Biff
gt;
gt; quot;Guy Lydigquot; gt; wrote in message
gt; ...
gt;gt;I have a column of first names that has some blank cells, some with 1
gt;gt;name,
gt;gt; and some with 2 names. I inserted another column and want to have only
gt;gt; initials but if the first or second name begin with quot;Chquot; or quot;Shquot;, I want
gt;gt; the
gt;gt; quot;Chquot; or quot;Shquot; as the initial.
gt;gt;
gt;gt; My function takes care of:
gt;gt; Blank cell
gt;gt; One name and the first two letters are Ch or Sh
gt;gt; One name and the first two letter are NOT Ch or Sh
gt;gt; Two names and the first and second names both begin with Sh or Ch
gt;gt; Two names and the first begins with Ch or Sh but not the second
gt;gt;
gt;gt; Here's where the problem begins:
gt;gt; There are TWO other possibilities: the second name begins with Ch or Sh
gt;gt; but
gt;gt; not the first OR there are two names and neither begins with Ch or Sh.
gt;gt;
gt;gt; If I pretend there's only ONE other possibility (and put the last bit in
gt;gt; the
gt;gt; If False part of the If function) all is well except that either I will
gt;gt; have
gt;gt; quot;Ben Bobquot; as quot;B. Bo.quot; OR quot;Ben Charlesquot; as quot;B. C.quot; depending on how I
gt;gt; write
gt;gt; the last piece.
gt;gt;
gt;gt; I should really be able to add one more piece to the formula to fix this
gt;gt; but
gt;gt; every time I do, the formula sticks. Is there a limitation on the number
gt;gt; of
gt;gt; Find, Mid, etc. functions?
gt;gt;
gt;gt; Here's the formula I used (which ignores the case of only the 2nd of two
gt;gt; names beginning with quot;Chquot; or quot;Shquot;):
gt;gt; =IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
gt;gt; quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
gt;gt; quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
gt;gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
gt;gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
gt;gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
gt;gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt;gt; quot;,B2) 1,1)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot; quot;,B2) 1,1)amp;quot;.quot;)))))
gt;gt;
gt;gt; I tried to amend it to:
gt;gt; =IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
gt;gt; quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
gt;gt; quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
gt;gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
gt;gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
gt;gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
gt;gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt;gt; quot;,B2) 1,1)amp;quot;.quot;,IF(AND(FIND(quot; quot;,B2),OR((MID(B2,FIND(quot;
gt;gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,1)amp;quot;.
gt;gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt;gt; quot;,B2) 1,1)amp;quot;.quot;)))))
gt;gt;
gt;gt; but it's hanging on MID after the last OR.
gt;gt;
gt;gt; I'm stumped. Any ideas why I can't fix this one glitch?
gt;gt;
gt;gt; I'd appreciate any assistance. (I hate to admit defeat and ask but....)
gt;gt; Sorry for the length of this post.
gt;gt;
gt;gt; TIA
gt;gt;
gt;gt; Guy
gt;
gt;
Ok, this takes care of that concatenated space glitch:

=IF(A11=quot;quot;,quot;quot;,IF(OR(LEFT(A11,2)={quot;chquot;,quot;shquot;}),LEFT( A11,2)amp;quot;.quot;,quot;quot;))amp;IF(LEN(A11),IF(LEFT(A11,2)lt;gt;quot;chquot;,I F(LEFT(A11,2)lt;gt;quot;shquot;,LEFT(A11)amp;quot;.quot;,quot;quot;),quot;quot;),quot;quot;)amp;IF(I SNUMBER(FIND(quot;
quot;,A11)),quot; quot;,quot;quot;)amp;IF(ISNUMBER(FIND(quot;
quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
quot;,A11)),quot;quot;),2)=quot;chquot;,quot;Ch.quot;,quot;quot;),quot;quot;)amp;IF(ISNUMBER(FIND (quot;
quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
quot;,A11)),quot;quot;),2)=quot;shquot;,quot;Sh.quot;,quot;quot;),quot;quot;)amp;IF(ISNUMBER(FIND (quot;
quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
quot;,A11)),quot;quot;),2)lt;gt;quot;chquot;,IF(ISNUMBER(FIND(quot;
quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
quot;,A11)),quot;quot;),2)lt;gt;quot;shquot;,MID(A11,FIND(quot; quot;,A11) 1,1)amp;quot;.quot;,quot;quot;),quot;quot;),quot;quot;),quot;quot;)

Biff

quot;Biffquot; gt; wrote in message
...
gt; One minor glitch:
gt;
gt; Cells that do not contain a space:
gt;
gt;gt; Charles
gt;gt; Sharlene
gt;gt; (empty)
gt;gt; Ben
gt;
gt; Will return a space concatenated to the end of the string:
gt;
gt;gt; Charles.............Ch.lt;spgt;
gt;gt; Sharlene............Sh.lt;spgt;
gt;gt; (empty)..............lt;spgt;
gt;gt; Ben....................B.lt;spgt;
gt;
gt; Trying to wrap that whole formula inside TRIM causes a general formula
gt; error (probably nesting limit).
gt;
gt; Maybe you can find a better way to do this and eliminate that problem. If
gt; not, maybe you can hide the column this formula is in and use another
gt; column with the helper formula of:
gt;
gt; =TRIM(A11)
gt;
gt; Biff
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt;gt; Dude, what a nightmare! lt;vbggt;
gt;gt;
gt;gt; If these are the possible entries: (as per your other post)
gt;gt;
gt;gt; .......A...............................Result
gt;gt; S. C..................................S. C.
gt;gt; Charles.............................Ch.
gt;gt; Sharlene............................Sh.
gt;gt; (empty).....................................
gt;gt; Ben....................................B.
gt;gt; Ben Charles........................B. Ch.
gt;gt; Sharlene Charley................Sh. Ch.
gt;gt; Ben James..........................B. J.
gt;gt; Charlie Ben........................Ch. B.
gt;gt; D.......................................D.
gt;gt; Charlie Sharlie....................Ch. Sh.
gt;gt;
gt;gt; =IF(A11=quot;quot;,quot;quot;,IF(OR(LEFT(A11,2)={quot;chquot;,quot;shquot;}),LEFT( A11,2)amp;quot;.quot;,quot;quot;))amp;IF(LEN(A11),IF(LEFT(A11,2)lt;gt;quot;chquot;,I F(LEFT(A11,2)lt;gt;quot;shquot;,LEFT(A11)amp;quot;.quot;,quot;quot;),quot;quot;),quot;quot;)amp;quot;
gt;gt; quot;amp;IF(ISNUMBER(FIND(quot; quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
gt;gt; quot;,A11)),quot;quot;),2)=quot;chquot;,quot;Ch.quot;,quot;quot;),quot;quot;)amp;IF(ISNUMBER(FIND (quot;
gt;gt; quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
gt;gt; quot;,A11)),quot;quot;),2)=quot;shquot;,quot;Sh.quot;,quot;quot;),quot;quot;)amp;IF(ISNUMBER(FIND (quot;
gt;gt; quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
gt;gt; quot;,A11)),quot;quot;),2)lt;gt;quot;chquot;,IF(ISNUMBER(FIND(quot;
gt;gt; quot;,A11)),IF(LEFT(SUBSTITUTE(A11,LEFT(A11,FIND(quot;
gt;gt; quot;,A11)),quot;quot;),2)lt;gt;quot;shquot;,MID(A11,FIND(quot; quot;,A11) 1,1)amp;quot;.quot;,quot;quot;),quot;quot;),quot;quot;),quot;quot;)
gt;gt;
gt;gt; What a thing of beauty, eh?
gt;gt;
gt;gt; Watch out for line wrap possibly quot;disguisingquot; quot; quot; as quot;quot;.
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Guy Lydigquot; gt; wrote in message
gt;gt; ...
gt;gt;gt;I have a column of first names that has some blank cells, some with 1
gt;gt;gt;name,
gt;gt;gt; and some with 2 names. I inserted another column and want to have only
gt;gt;gt; initials but if the first or second name begin with quot;Chquot; or quot;Shquot;, I want
gt;gt;gt; the
gt;gt;gt; quot;Chquot; or quot;Shquot; as the initial.
gt;gt;gt;
gt;gt;gt; My function takes care of:
gt;gt;gt; Blank cell
gt;gt;gt; One name and the first two letters are Ch or Sh
gt;gt;gt; One name and the first two letter are NOT Ch or Sh
gt;gt;gt; Two names and the first and second names both begin with Sh or Ch
gt;gt;gt; Two names and the first begins with Ch or Sh but not the second
gt;gt;gt;
gt;gt;gt; Here's where the problem begins:
gt;gt;gt; There are TWO other possibilities: the second name begins with Ch or Sh
gt;gt;gt; but
gt;gt;gt; not the first OR there are two names and neither begins with Ch or Sh.
gt;gt;gt;
gt;gt;gt; If I pretend there's only ONE other possibility (and put the last bit in
gt;gt;gt; the
gt;gt;gt; If False part of the If function) all is well except that either I will
gt;gt;gt; have
gt;gt;gt; quot;Ben Bobquot; as quot;B. Bo.quot; OR quot;Ben Charlesquot; as quot;B. C.quot; depending on how I
gt;gt;gt; write
gt;gt;gt; the last piece.
gt;gt;gt;
gt;gt;gt; I should really be able to add one more piece to the formula to fix this
gt;gt;gt; but
gt;gt;gt; every time I do, the formula sticks. Is there a limitation on the number
gt;gt;gt; of
gt;gt;gt; Find, Mid, etc. functions?
gt;gt;gt;
gt;gt;gt; Here's the formula I used (which ignores the case of only the 2nd of two
gt;gt;gt; names beginning with quot;Chquot; or quot;Shquot;):
gt;gt;gt; =IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
gt;gt;gt; quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
gt;gt;gt; quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
gt;gt;gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
gt;gt;gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
gt;gt;gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
gt;gt;gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt;gt;gt; quot;,B2) 1,1)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot; quot;,B2) 1,1)amp;quot;.quot;)))))
gt;gt;gt;
gt;gt;gt; I tried to amend it to:
gt;gt;gt; =IF(ISBLANK(B2),quot;quot;,IF(AND(ISERROR(FIND(quot;
gt;gt;gt; quot;,B2)),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B 2,2)amp;quot;.quot;,IF(ISERROR(FIND(quot;
gt;gt;gt; quot;,B2)),LEFT(B2,1)amp;quot;.quot;,IF(AND(FIND(quot;
gt;gt;gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;),OR((MID( B2,FIND(quot;
gt;gt;gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,2)amp;quot;.
gt;gt;gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,IF(AND(FIND(quot;
gt;gt;gt; quot;,B2),OR(LEFT(B2,2)=quot;Shquot;,LEFT(B2,2)=quot;Chquot;)),LEFT(B2 ,2)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt;gt;gt; quot;,B2) 1,1)amp;quot;.quot;,IF(AND(FIND(quot; quot;,B2),OR((MID(B2,FIND(quot;
gt;gt;gt; quot;,B2) 1,2)=quot;Chquot;),MID(B2,FIND(quot; quot;,B2) 1,2)=quot;Shquot;)),LEFT(B2,1)amp;quot;.
gt;gt;gt; quot;amp;MID(B2,FIND(quot; quot;,B2) 1,2)amp;quot;.quot;,LEFT(B2,1)amp;quot;. quot;amp;MID(B2,FIND(quot;
gt;gt;gt; quot;,B2) 1,1)amp;quot;.quot;)))))
gt;gt;gt;
gt;gt;gt; but it's hanging on MID after the last OR.
gt;gt;gt;
gt;gt;gt; I'm stumped. Any ideas why I can't fix this one glitch?
gt;gt;gt;
gt;gt;gt; I'd appreciate any assistance. (I hate to admit defeat and ask but....)
gt;gt;gt; Sorry for the length of this post.
gt;gt;gt;
gt;gt;gt; TIA
gt;gt;gt;
gt;gt;gt; Guy
gt;gt;
gt;gt;
gt;
gt;