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

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

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

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

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.

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

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

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

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

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