Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Function
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Function
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(B 2,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(B 2,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 "papou" wrote: 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");PR OPER(LEFT(A1;2))&".";LEFT(A1;1)&".");IF(OR(LEFT(A1 ;2)="Ch";LEFT(A1;2)="Sh");PROPER(LEFT(A1;2))&"."&P ROPER(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" a écrit dans le message de news: ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to find expired information | New Users to Excel | |||
Function to find only exact matches in spreadsheets? | Excel Worksheet Functions | |||
Simple find with right function | Excel Worksheet Functions | |||
Function to find closest to 0 (including neg #'s) in cloumn | Excel Discussion (Misc queries) | |||
Can VLookup function find and list multiple records? | Excel Worksheet Functions |