ExcelBanter - Parse Name field with multiple characthers

On Wed, 1 Apr 2009 12:07:01 -0700, suz wrote:

I need to parse the first 7 chars of last name from name field (ie. first
last, first middle last), however the last name can have a hyphen and or
multiple names.

Examples:
Joe De La Paz result needs to be DELAPAZJ (this one is tough because it was
entered with spaces between De La Paz)
Crystal Bakersmith result needs to be BAKERSMC
Sally Mahone-Lamm result needs to be MAHONES
Chan Ven Kaliq Kada result needs to be KADAC

Without a list of acceptable, space-separated, last names, I don't think you
will be able to entirely accomplish what you want.

One simple method, excluding the problem of the space-separated last name,
would be to use a regular expression type of function.

If you have a list of acceptable space-separated last names, they could be
incorporated into the routine.

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), with your data in A1, enter this
formula in some cell:

=UPPER(RegexSub(A1,"(^\w).*\s(\w{1,7})\S*$","$2$1" ))

=========================================
Option Explicit
Function RegexSub(Str As String, SrchFor As String, _
ReplWith As String) As String
Dim objRegExp As Object

Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.Pattern = SrchFor
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = False

RegexSub = objRegExp.Replace(Str, ReplWith)

End Function
========================================

--ron