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

See if this does it
=MID(A4,FIND(" ",A4),FIND(" ",RIGHT(A4,LEN(A4)-FIND(" ",A4))))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Jim Berglund" <(E-Mail Removed)> wrote in message
news:356992CE-F889-4657-9376-(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:356992CE-F889-4657-9376-(E-Mail Removed)...
> 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
>

On Oct 17, 2:32 pm, "Jim Berglund" <jazz...@shaw.ca> 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.

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:356992CE-F889-4657-9376-(E-Mail Removed)...
>> 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
>>
>
>

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Oct 17, 2:32 pm, "Jim Berglund" <jazz...@shaw.ca> 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.
>

On Wed, 17 Oct 2007 15:32:43 -0600, "Jim Berglund" <(E-Mail Removed)> 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

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" <(E-Mail Removed)> wrote in message
news:FCC75A20-E0DE-4213-AB1F-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:356992CE-F889-4657-9376-(E-Mail Removed)...
>>> 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
>>>
>>
>>
>

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",LEFT(A31,2)="Dr"),LEFT(A31,2),"")))

Alan Beban

Thanks, that works!
Jim
"Alan Beban" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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",LEFT(A31,2)="Dr"),LEFT(A31,2),"")))
>
> Alan Beban