Private Sub CommandButton1_Click()
Dim rng As Range, cel As Range
Set rng = Range("A2", "A5727")‘ Set rng = Range(“A2”, “A1000”)
For Each cel In rng
ParseName cel.value, cel
Next cel
MsgBox "Processing complete."End Sub
Public Sub ParseName(value As String, cel As Range)
‘Given a name string,
‘1. Clean it to remove non-valid characters.
‘2. Parse it into its consituent parts.
‘ Salutation
‘ First name
‘ Middle name
‘ Last name
‘ Suffix
‘3. Provide output for ETS-format record content.
‘ Salutation
‘ First name + Middle Initial
‘ Last name + Suffix
‘ Contact (First name if provided. Else, Salutation + Last name.)
‘
Const START_COL As Integer = 12
Const A_ASC As Integer = 65
Const Z_ASC As Integer = 90
Const DASH_ASC As Integer = 45
Const AMPERSAND_ASC As Integer = 38
Const QUOTE_ASC As Integer = 39
Dim theChar As String
Dim theAsc As Integer
Dim valid As Boolean
Dim i As Integer
Dim ary() As String
Dim saluNm As String
Dim firstNm As String
Dim lastNm As String
Dim midNm As String
Dim suffNm As String
Dim contactNm As String
Dim ampNm As String 'Processing for ampersand
Dim processed As Boolean
'----- Begin cleaning.
'Check each char.
'Remove non-alpha chars, except:
'"&" as in "Ken & Mary"
'"-" for hyphenated names
'"'" for O'Neill etc
'We should allow accented characters from other languages, but not yet.
For i = 1 To Len(value)
theChar = Mid(value, i, 1)
theAsc = Asc(theChar)
valid = False
If theAsc >= A_ASC _
And theAsc <= Z_ASC Then 'Valid alpha
valid = True
End If
If theAsc = DASH_ASC Then 'Dash. Make sure it looks valid.
If Mid(value, i - 1, 1) <> " " _
And Asc(Mid(value, i + 1, 1)) >= A_ASC _
And Asc(Mid(value, i + 1, 1)) <= Z_ASC _
Then
valid = True
End If
End If
If theAsc = QUOTE_ASC Then 'Quote. Make sure it looks valid.
If Mid(value, i - 1, 1) <> " " _
And Asc(Mid(value, i + 1, 1)) >= A_ASC _
And Asc(Mid(value, i + 1, 1)) <= Z_ASC _
Then
valid = True
End If
End If
If theAsc = AMPERSAND_ASC Then 'Ampersand. Make sure it looks valid.
If Mid(value, i - 1, 1) = " " _
And Mid(value, i + 1, 1) = " " Then
valid = True
End If
End If
'Invalid. Change to space.
If Not valid Then
Mid(value, i, 1) = " "
End If
Next i
'Remove any blank elements (remove duplicate spaces until there are no more.)
Do Until InStr(value, " ") = 0
value = Replace(value, " ", " ", 1, 1)
Loop
'Remove any leading or trailing spaces.
value = Trim(value)
'---- Begin parsing.
'Split line into array for parsing.
ary = Split(value, " ")
'Parse each array element.
'Find and set:
'saluNm
'firstNm
'midNm
'lastNm
'suffNm
For i = 0 To UBound(ary)
processed = False
ary(i) = UCase(ary(i))
'Remove any ATTN or ATT
If ary(i) = "ATTN" _
Or ary(i) = "ATT" _
Then
ary(i) = ""
processed = True
End If
'Find any salutation
If ary(i) = "MR" _
Or ary(i) = "MRS" _
Or ary(i) = "MS" _
Or ary(i) = "MISS" _
Or ary(i) = "ATTY" _
Or ary(i) = "DR" _
Or ary(i) = "SIR" _
Or ary(i) = "MADAM" _
Or ary(i) = "MIS" _
Or ary(i) = "SRA" _
Or ary(i) = "SR" _
Then
'Do not override salutations with "&"
'because we'd see MR & MRS showing up as MRS.
If InStr(1, saluNm, "&") = 0 Then
saluNm = ary(i)
End If
processed = True
End If
'Change AND (MR AND MRS) to an ampersand for better processing.
If ary(i) = "AND" _
Then
ary(i) = "&"
End If
So if you focus on getting your sex life back by using brand levitra supplementprofessors.com medications instead of locating the root cause, these symptoms could accelerate and lead to a serious cardiac event in the future. When it comes to different kinds of medications, they can sometimes cost more than we sildenafil overnight shipping like or prefer them to. But once they ingest Zenegra, the possibilities are high that they like to sit alone and want buy female viagra to save time also, purchase drugs from online Canada pharmacies. Because your HVAC system plays a significant role in improving the fertility. cialis in spain 'Handle ampersand ("Ken & Mary" , "Mr & Mrs").
'Will overwrite Mr or Mrs if they are already in Salutation.
If ary(i) = "&" _
Then
'Set ampNm to include the ampersand, and the elements before and after it.
ampNm = ary(i - 1) & " & " & ary(i + 1)
'And clear the next element so we don't process it twice.
ary(i + 1) = ""
'Put MR & MRS in salutation; anything else in firstname.
If ampNm = "MR & MRS" _
Or ampNm = "SR & SRA" _
Then
saluNm = ampNm
Else
firstNm = ampNm
End If
processed = True
End If
'Handle suffixes.
If ary(i) = "ESQ" _
Or ary(i) = "PHD" _
Or ary(i) = "JR" _
Or ary(i) = "III" _
Or ary(i) = "IV" _
Then
suffNm = ary(i)
processed = True
End If
'Remove garbage! BLDG, APT, PER (PER CALL),etc should not be there.
'Note that the "CALL" part of "PER CALL" requires a little more intelligence.
If ary(i) = "BLDG" _
Or ary(i) = "APT" _
Or ary(i) = "PER" _
Or ary(i) = "THANK" _
Or ary(i) = "THANKS" _
Or ary(i) = "YOU" _
Or ary(i) = "PHONE" _
Or ary(i) = "PLEDGE" _
Or ary(i) = "THANKYOU" _
Or ary(i) = "DONT" _
Or ary(i) = "REQUEST" _
Or ary(i) = "PAID" _
Or ary(i) = "REMINDER" _
Or ary(i) = "REBILL" _
Or ary(i) = "REMAIL" _
Or ary(i) = "MAIL" _
Or ary(i) = "CONVERSATION" _
Then
processed = True
End If
If ary(i) = "CALL" _
Then
If i > 1 Then
If ary(i - 1) = "PER" Then
processed = True
End If
End If
End If
'Middle initial?
If Len(ary(i)) = 1 _
And ary(i) <> "&" _
And ary(i) <> "-" _
And ary(i) <> "'" _
Then
'If last name is already found,
'or last array element,
'then this is probably part of address.
If lastNm = "" _
And i < UBound(ary) _
Then
'Do we already have a "middle" initial, like "B G BYRD"?
If midNm <> "" Then
'If first name is blank, fill it in.
If firstNm = "" Then
firstNm = midNm
midNm = ary(i)
Else
'We already have a first name. 2 middle initials?
midNm = midNm & " " & ary(i)
End If
Else
midNm = ary(i)
End If
End If
processed = True
End If
'Element was not processed; must be first or last name.
If Not processed Then
' "unprocessed" encountered goes into firstNm if empty;
'If firstNm already has data, it goes into lastNm if empty.
'Stop after 2, because bad data (if present) tends to follow the real name.
If firstNm = "" Then
firstNm = ary(i)
Else
If lastNm = "" Then
lastNm = ary(i)
Else 'Last name is already full. Could we have a 2-word first name?
If lastNm = "ANN" _
Or lastNm = "SUE" _
Or lastNm = "BOB" _
Or lastNm = "MARIE" _
Or lastNm = "MARY" _
Or lastNm = "JO" _
Or lastNm = "JOE" _
Or lastNm = "ELLEN" _
Or lastNm = "LEE" _
Or lastNm = "LEA" _
Or lastNm = "RAE" _
Or lastNm = "RAY" _
Then
firstNm = firstNm & " " & lastNm
lastNm = ary(i)
End If
End If
End If
End If
Next i
'Done parsing the elements.
'Ready to finalize name.
'If last is blank and first has data, it's probably last name. Move it there.
If firstNm <> "" And lastNm = "" Then
lastNm = firstNm
firstNm = ""
End If
'Append middlename to firstname
If midNm <> "" Then
firstNm = firstNm & " " & midNm
End If
'Append suffix to lastname
If suffNm <> "" Then
lastNm = lastNm & " " & suffNm
End If
'And set the contact.
If firstNm <> "" Then
contactNm = firstNm
Else
contactNm = saluNm & " " & lastNm
End If
Range("L" & cel.Row).value = saluNm
Range("M" & cel.Row).value = firstNm
Range("N" & cel.Row).value = lastNm
Range("O" & cel.Row).value = contactNmEnd Sub