#1
|
|||
|
|||
Parsing 'FullName' column - help needed text to column
Hey every one. I'm working a project and need some help. I've stared at this so long I'm afraid I can't see the forest for the trees.
I have an address DB from a client and need to parse out the FullName field to normalize it for them. The issue I have is that they were not consistent in how they input the records over time so just using text to columns isn't working for me. A lot of their lists were being maintained directly in a Word document label sheets. (Converting that was a real joy! NOT) You can see in the attached sample there is a real mishmash of data there. Salutations / titles - full middle names vs. just initials - dual names - and on and on. One thing I thought of: Is there a way to determine the number of "words" in a cell? At least if I could sort by that I could handle the rows that are similar. Any suggestions welcome! I have about 25 - 30K records to deal with. |
#2
|
||||
|
||||
You could try the following macro. It will parse the data you've provided, but you'll need to check the final output for errors. For example:
• Surnames consisting of more than two words, such as 'Van Dieman' & 'Van Der Plaat' won't be handled correctly. • Although logic to turn all names into 'proper' case and to capitalize the first letter after the 'c' in surnames prefixed by Mc or Mac, that might not be appropriate for all such surnames. Code:
Sub Demo() Application.ScreenUpdating = False Dim lRow As Long, lCol As Long, i As Long, j As Long Dim StrGN As String, StrSN As String, StrTmp As String With ActiveSheet.UsedRange lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row On Error Resume Next For i = lRow To 1 Step -1 If .Range(.Cells(i, 1), .Cells(i, lCol)).SpecialCells(xlCellTypeBlanks).Count < lCol Then lRow = i: Exit For Next On Error GoTo 0 For i = 1 To lRow If .Cells(i, 1).Value = "" Then StrTmp = Trim(Replace(.Cells(i, 2).Value, ".", " ")) If StrTmp <> "" Then While InStr(StrTmp, " ") > 0 StrTmp = Replace(StrTmp, " ", " ") Wend StrSN = Split(StrTmp, " ")(UBound(Split(StrTmp, " "))) If StrSN Like "[SsJj][Rr]" Then StrSN = Split(StrTmp, " ")(UBound(Split(StrTmp, " ")) - 1) & " " & StrSN End If StrGN = Replace(StrTmp, " " & StrSN, "") StrSN = WorksheetFunction.Proper(StrSN) If Left(StrSN, 2) = "Mc" Then StrSN = "Mc" & UCase(Mid(StrSN, 3, 1)) & Mid(StrSN, 4) If Left(StrSN, 2) = "Mac" Then StrSN = "Mac" & UCase(Mid(StrSN, 4, 1)) & Mid(StrSN, 5) .Cells(i, 1).Value = WorksheetFunction.Proper(StrGN) .Cells(i, 2).Value = StrSN End If Else StrGN = Trim(.Cells(i, 1).Value) StrSN = Trim(.Cells(i, 2).Value) StrSN = WorksheetFunction.Proper(StrSN) If Left(StrSN, 2) = "Mc" Then StrSN = "Mc" & UCase(Mid(StrSN, 3, 1)) & Mid(StrSN, 4) If Left(StrSN, 2) = "Mac" Then StrSN = "Mac" & UCase(Mid(StrSN, 4, 1)) & Mid(StrSN, 5) .Cells(i, 1).Value = WorksheetFunction.Proper(StrGN) .Cells(i, 2).Value = StrSN End If Next End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Wow Paul -
Thanks! I didn't expect a written procedure just some guidance in accomplishing the task. I haven't studied the code yet but I will. I'm not new to programming but I'm fairly new to VBA. I had tools in other programs that would have snapped this out for me in a couple of steps but sadly I no longer have access to them. I'll give this a go and get back to you! I'm guessing I'll probably have questions based on your response. Scott |
#4
|
|||
|
|||
Paul -
This got me 99% there. Having to check names like you indicated with multiple word last name but a whole lot better that redoing it by hand!! Thanks again! |
Tags |
parsing data, text to column |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
keep each item/cell in column A associated with a group in column C | ht889 | Excel | 1 | 02-25-2014 12:36 PM |
Columns. How to move the left column so it's to the right of the 'right' column ? | Vit | Word | 9 | 11-21-2012 12:57 PM |
Automatically enter date into a column and make that column read only | Mr Davo | Excel | 1 | 10-29-2012 01:07 AM |
Can I change the horizontal scrollbar to scroll smoothly rather than column by column | carpat | Excel | 0 | 01-10-2012 09:34 AM |
Need to search a column for a macth and return a result from a third column | pdfaust | Excel | 2 | 02-03-2011 03:02 PM |