Find Function

[Guy Lydig]

Is there a limit to the number of times the Find function can be used in one
formula? (I know you can nest only up to 7 If functions. Does Find have such
a limitation?)

Okay gurus, I've been breaking my head and I've almost got this licked,
(except for the aforementioned problem with Find...) but I'm wondering if
there isn't a much easier way to do this:

Column B (First Name) has some cells that have:
nothing at all (empty cells)
one initial with a period
two initials with periods and a space between
one name
two names
one name and one initial

Problem:
In column C, I would like to extract just the initials--with one little
catch: if either the first or second name begins with either Ch or Sh, the
initial should be both those letters. Here's what I want:

Column B Column C

S. C. S. C.
Charles Ch.
Sharlene Sh.
Ben B.
Ben Charles B. Ch.
Sharlene Charley Sh. Ch.
Ben James B. J.
Charlie Ben Ch. B.
D. D.

My function is replete with If, And, Or, Find, Isblank, Iserror, Left, and
Mid: a total of close to 400 characters...AND--it still doesn't work because
it is complaining about a Find function.

Anyone have any simpler ideas?

TIA

Guy

 

[papou]

Hello
Here is a try not too far from what you're after :
=IF(A1="";"";IF(ISERROR(FIND("
";A1;1));IF(OR(LEFT(A1;2)="Ch";LEFT(A1;2)="Sh");PROPER(LEFT(A1;2))&".";LEFT(A1;1)&".");IF(OR(LEFT(A1;2)="Ch";LEFT(A1;2)="Sh");PROPER(LEFT(A1;2))&"."&PROPER(MID(A1;FIND("
";A1;1)+1;2))&".";LEFT(A1;1)&"."&IF(OR(MID(A1;FIND("
";A1;1)+1;2)="Ch";MID(A1;FIND(" ";A1;1)+1;2)="Sh");PROPER(MID(A1;FIND("
";A1;1)+1;2))&".";MID(A1;FIND(" ";A1;1)+1;1)&"."))))
The only remaining problem it seems, resides in data like "Charlie Ben"
where my function will return "Ch. Be."

HTH
Cordially
Pascal

 

[Guy Lydig]

Thanks for taking the time to write this out. This is the same idea as mine
except that I didn't bother with Proper, since the names were typed properly.

My problem remains, however.
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 stuck on Find. Hence my question about the
limitation on the number of Finds. Is there a limit? Now it sticks on MID...
read on....

Here's the formula I used:
=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 now it's hanging on MID after the last OR.

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

Quick question on your formula: didn't you mean commas instead of the
semi-colons?

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

TIA

Guy