Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-03-2014, 06:20 AM
ScottA ScottA is offline Parsing 'FullName' column - help needed text to column Windows 7 64bit Parsing 'FullName' column - help needed text to column Office 2010 32bit
Advanced Beginner
Parsing 'FullName' column - help needed text to column
 
Join Date: Apr 2014
Posts: 30
ScottA is on a distinguished road
Default 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.
Attached Files
File Type: xlsx SampleNamesWB.xlsx (11.2 KB, 10 views)
Reply With Quote
  #2  
Old 05-03-2014, 11:51 PM
macropod's Avatar
macropod macropod is online now Parsing 'FullName' column - help needed text to column Windows 7 32bit Parsing 'FullName' column - help needed text to column Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 05-04-2014, 04:45 AM
ScottA ScottA is offline Parsing 'FullName' column - help needed text to column Windows 7 64bit Parsing 'FullName' column - help needed text to column Office 2010 32bit
Advanced Beginner
Parsing 'FullName' column - help needed text to column
 
Join Date: Apr 2014
Posts: 30
ScottA is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 05-06-2014, 12:49 PM
ScottA ScottA is offline Parsing 'FullName' column - help needed text to column Windows 7 64bit Parsing 'FullName' column - help needed text to column Office 2010 32bit
Advanced Beginner
Parsing 'FullName' column - help needed text to column
 
Join Date: Apr 2014
Posts: 30
ScottA is on a distinguished road
Default

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!
Reply With Quote
Reply

Tags
parsing data, text to column

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Parsing 'FullName' column - help needed text to column 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
Parsing 'FullName' column - help needed text to column 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
Parsing 'FullName' column - help needed text to column 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft