Adding "and"

[cohdab16]

I have a column called "Addressee"

The Addressee is like these 3 examples:

Mr. John Doe Mrs Kathy Doe
Ms. Becky Jones Ms. Diane Patel
Mr. Jack Smith Mr. Joseph Fine

I want to add an "and" between the Mrs. or Ms. or Mr. I have approximately
1,000 records to do this for.

Thank you in advance for your assistance.

Dave
(e-mail address removed)

 

[Ron Rosenfeld]

I have a column called "Addressee"

The Addressee is like these 3 examples:

Mr. John Doe Mrs Kathy Doe
Ms. Becky Jones Ms. Diane Patel
Mr. Jack Smith Mr. Joseph Fine

I want to add an "and" between the Mrs. or Ms. or Mr. I have approximately
1,000 records to do this for.

Thank you in advance for your assistance.

Dave
(e-mail address removed)

Download and install Longre's free morefunc.xll add-in (do a Google search to
find a functioning source.

Then use this formula (assuming your string is in A1):

=REGEX.SUBSTITUTE(A1,"(\sM(rs|r|s))\b"," and[1]")

Fill down as far as required.

If you cannot find the morefunc add-in, you could use a similar User Defined
Function, and then use this formula:

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter the formula

=InsertAnd(A1)

in some cell.

=========================
Option Explicit
Function InsertAnd(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\sM(rs|r|s))\b"
re.ignorecase = False
InsertAnd = re.Replace(str, " and$1")
End Function
=========================
--ron

 

[David Heaton]

I have a column called "Addressee"

The Addressee is like these 3 examples:

Mr. John Doe Mrs Kathy Doe
Ms. Becky Jones Ms. Diane Patel
Mr. Jack Smith Mr. Joseph Fine

I want to add an "and" between the Mrs. or Ms. or Mr.   I have approximately
1,000 records to do this for.

Thank you in advance for your assistance.

Dave
(e-mail address removed)

Download and install Longre's free morefunc.xll add-in (do a Google search to
find a functioning source.

Then use this formula (assuming your string is in A1):

=REGEX.SUBSTITUTE(A1,"(\sM(rs|r|s))\b"," and[1]")

Fill down as far as required.

If you cannot find the morefunc add-in, you could use a similar User Defined
Function, and then use this formula:

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter the formula

=InsertAnd(A1)

 in some cell.

=========================
Option Explicit
Function InsertAnd(str As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
    re.Global = True
    re.Pattern = "(\sM(rs|r|s))\b"
    re.ignorecase = False
InsertAnd = re.Replace(str, " and$1")
End Function
=========================
--ron- Hide quoted text -

- Show quoted text -

Dave,

For an inbuilt formula based solution try this rather long, with your
list of names in column A

LEFT(A1,IF(ISERROR(FIND("Mr",A1,3)),FIND("Ms",A1,3),FIND("Mr",A1,3))-1)
&"and"&RIGHT(A1,LEN(A1)-IF(ISERROR(FIND("Mr",A1,3)),FIND
("Ms",A1,3),FIND("Mr",A1,3))+2)

to make this more readable you could try having a hidden column (B)
with

=IF(ISERROR(FIND("Mr",A1,3)),FIND("Ms",A1,3),FIND("Mr",A1,3))

and then

=LEFT(A1,B1-1) & "and " & RIGHT(A1,LEN(A1)-B1-2)

in your results column


Regards

David