Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
I'm trying to create a mailing list and have a list containing Mr, Ms, Miss,
Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
See if this does it
=MID(A4,FIND(" ",A4),FIND(" ",RIGHT(A4,LEN(A4)-FIND(" ",A4)))) -- Don Guillett Microsoft MVP Excel SalesAid Software "Jim Berglund" wrote in message ... I'm trying to create a mailing list and have a list containing Mr, Ms, Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
Try something like this:
Using this list (in alphabetical order): E1: (blank) E2: Dr E3: Miss E4: Mr E5: Mrs E6: Ms and a name, with or without title, in cell A1 This formula returns the Title: C1: =T(INDEX($E$1:$E$6,MAX(INDEX(COUNTIF(A1,$E$2:$E$6& " *")*{1;2;3;4;5},0))+1)) Examples: A1: Dr Dave A2: Drew Carey A3: Miss Smith A4: Mississippi Mary These title values are returned: C1: Dr C2: (blank) C3: Miss C4: (blank) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Berglund" wrote in message ... I'm trying to create a mailing list and have a list containing Mr, Ms, Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
Thanks, Ron. I tried it but came up with a column of blanks.
What I did was to create the indesx in column M and enter the formula it into Cell L1 of my spreadsheet as =T(INDEX($M$1:$M$6,MAX(INDEX(COUNTIF(C1,$M$2:$M$6& "*")*{1;2;3;4;5},0))+1)) The names are located in column C. Is there something wrong with my porcedure? Jim Berglund "Ron Coderre" wrote in message ... Try something like this: Using this list (in alphabetical order): E1: (blank) E2: Dr E3: Miss E4: Mr E5: Mrs E6: Ms and a name, with or without title, in cell A1 This formula returns the Title: C1: =T(INDEX($E$1:$E$6,MAX(INDEX(COUNTIF(A1,$E$2:$E$6& " *")*{1;2;3;4;5},0))+1)) Examples: A1: Dr Dave A2: Drew Carey A3: Miss Smith A4: Mississippi Mary These title values are returned: C1: Dr C2: (blank) C3: Miss C4: (blank) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Berglund" wrote in message ... I'm trying to create a mailing list and have a list containing Mr, Ms, Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
Isn't that strange...?
I'm using Excel 2007 and when I try it I get #VALUE on every line. Any idea why? Jim Berglund wrote in message oups.com... On Oct 17, 2:32 pm, "Jim Berglund" wrote: I'm trying to create a mailing list and have a list containing Mr, Ms, Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund I filled in the A column with a mix of Mr. Mrs. Miss. Dr, etc in random order and your code popped in the title that I had in A31 which was Miss. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
On Wed, 17 Oct 2007 15:32:43 -0600, "Jim Berglund" wrote: I'm trying to create a mailing list and have a list containing Mr, Ms, Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss" ,"Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31 ,2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund I think your description of what you eventually want to do may not be complete. But here is a UDF that will, depending on the argument, return either an initial Title if it is present (or blank if it is not). It will also return the first word after the title or, if there is no title, return the first word. I could imagine that sometimes the first word after the title might be a last name, rather than a first name. In any event, to use the function, enter =ParseName(cell_ref, Index) The "Index Codes" are listed in the VBA code. If you need to add more salutations to be excluded, add them to sTitle extending the pipe-delimited string you see there. To enter the UDF, <alt-Fll opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. ======================================== Option Explicit Function ParseName(str As String, Index As Long) As String Dim re As Object Dim mc As Object Dim sPat As String Dim sTitle As String 'Index code ' 1 = Salutation ' 3 = First Name 'Pipe-delimited list of possible Titles sTitle = "Mr|Ms|Miss|Mrs|Dr" sPat = "^((" & sTitle & ")\.?(\s+))?(\w+)" Set re = CreateObject("vbscript.regexp") re.ignorecase = True re.Pattern = sPat If re.test(str) = True Then Set mc = re.Execute(str) ParseName = mc(0).submatches(Index) End If End Function ===================================== --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
It's a bit difficult to guess the problem without knowing the names that are
being evaluated. So.....Reverse engineering from your posted formula.... M1: (blank) M2: Dr M3: Miss M4: Mr M5: Mrs M6: Ms Try this variation L1: =T(INDEX($M$1:$M$6,MAX(INDEX(COUNTIF(C2,$M$2:$M$6& {". *"," *"})*{1;2;3;4;5},0))+1)) Note_1: There's a space before the asterisk) Note_2: That formula matches titles with, or without, a period. eg Mr. vs Mr Note_3: BUT it returns the no-period version from the list Examples: If C1: Mr Bigshot_____Then L1 returns: Mr If C1: Mr. Bigshot_____Then L1 returns: Mr If you want to match the cell contents version of the title: L1: =IF(MAX(INDEX(COUNTIF(C1,$M$2:$M$6&{". *"," *"})*{1;2;3;4;5},0)),LEFT(C1,SEARCH(" ",C1)),"") Note_4: There is a space before each asterisk Examples: If C1: mr Bigshot_____Then L1 returns: mr If C1: mr. Bigshot_____Then L1 returns: mr. Does that help? If you still have issues, post some sample names. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Berglund" wrote in message ... Thanks, Ron. I tried it but came up with a column of blanks. What I did was to create the indesx in column M and enter the formula it into Cell L1 of my spreadsheet as =T(INDEX($M$1:$M$6,MAX(INDEX(COUNTIF(C1,$M$2:$M$6& "*")*{1;2;3;4;5},0))+1)) The names are located in column C. Is there something wrong with my porcedure? Jim Berglund "Ron Coderre" wrote in message ... Try something like this: Using this list (in alphabetical order): E1: (blank) E2: Dr E3: Miss E4: Mr E5: Mrs E6: Ms and a name, with or without title, in cell A1 This formula returns the Title: C1: =T(INDEX($E$1:$E$6,MAX(INDEX(COUNTIF(A1,$E$2:$E$6& " *")*{1;2;3;4;5},0))+1)) Examples: A1: Dr Dave A2: Drew Carey A3: Miss Smith A4: Mississippi Mary These title values are returned: C1: Dr C2: (blank) C3: Miss C4: (blank) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Berglund" wrote in message ... I'm trying to create a mailing list and have a list containing Mr, Ms, Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
Jim Berglund wrote:
I'm trying to create a mailing list and have a list containing Mr, Ms, Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(OR(LEFT(A31,2)="Mr",LEFT(A31,2)="Ms",LEF T(A31,2)="Dr"),LEFT(A31,2),""))) Alan Beban |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
Thanks, that works!
Jim "Alan Beban" wrote in message ... Jim Berglund wrote: I'm trying to create a mailing list and have a list containing Mr, Ms, Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(OR(LEFT(A31,2)="Mr",LEFT(A31,2)="Ms",LEF T(A31,2)="Dr"),LEFT(A31,2),""))) Alan Beban |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Contains or Includes
My face is red. I had a blank in front of all the name entries. When I
removed it all these solutions worked. Jim Berglund "Ron Coderre" wrote in message ... It's a bit difficult to guess the problem without knowing the names that are being evaluated. So.....Reverse engineering from your posted formula.... M1: (blank) M2: Dr M3: Miss M4: Mr M5: Mrs M6: Ms Try this variation L1: =T(INDEX($M$1:$M$6,MAX(INDEX(COUNTIF(C2,$M$2:$M$6& {". *"," *"})*{1;2;3;4;5},0))+1)) Note_1: There's a space before the asterisk) Note_2: That formula matches titles with, or without, a period. eg Mr. vs Mr Note_3: BUT it returns the no-period version from the list Examples: If C1: Mr Bigshot_____Then L1 returns: Mr If C1: Mr. Bigshot_____Then L1 returns: Mr If you want to match the cell contents version of the title: L1: =IF(MAX(INDEX(COUNTIF(C1,$M$2:$M$6&{". *"," *"})*{1;2;3;4;5},0)),LEFT(C1,SEARCH(" ",C1)),"") Note_4: There is a space before each asterisk Examples: If C1: mr Bigshot_____Then L1 returns: mr If C1: mr. Bigshot_____Then L1 returns: mr. Does that help? If you still have issues, post some sample names. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Berglund" wrote in message ... Thanks, Ron. I tried it but came up with a column of blanks. What I did was to create the indesx in column M and enter the formula it into Cell L1 of my spreadsheet as =T(INDEX($M$1:$M$6,MAX(INDEX(COUNTIF(C1,$M$2:$M$6& "*")*{1;2;3;4;5},0))+1)) The names are located in column C. Is there something wrong with my porcedure? Jim Berglund "Ron Coderre" wrote in message ... Try something like this: Using this list (in alphabetical order): E1: (blank) E2: Dr E3: Miss E4: Mr E5: Mrs E6: Ms and a name, with or without title, in cell A1 This formula returns the Title: C1: =T(INDEX($E$1:$E$6,MAX(INDEX(COUNTIF(A1,$E$2:$E$6& " *")*{1;2;3;4;5},0))+1)) Examples: A1: Dr Dave A2: Drew Carey A3: Miss Smith A4: Mississippi Mary These title values are returned: C1: Dr C2: (blank) C3: Miss C4: (blank) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Berglund" wrote in message ... I'm trying to create a mailing list and have a list containing Mr, Ms, Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a macro that looks for the first name if and after one of these surnames occurs. I tried multiple IF's, as below? =IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),""))) The frst part works for Mrs * Miss, but the OR section gives me a #Value error. I there a better way? Jim Berglund |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter includes extra row | New Users to Excel | |||
average that includes estimates if data not available | Excel Worksheet Functions | |||
Using a macro that includes 2 worksheets | Excel Discussion (Misc queries) | |||
Getting a range that includes alternate rows only | Excel Programming | |||
=max(range includes #N/As) | Excel Worksheet Functions |