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 -