Name parsing formula..

[Matt Williamson]

I'm trying to normalize a database and I've imported a single field with
names into excel and split them into multiple columns. The biggest problem I
have now is clean up. The majority of which seems to be users with no middle
name/initial where the lastname got pulled into the middle name column.

Example

first middle last suffix
John A Doe Sr.
Mary Doe

In this case the first line imports fine, but the second line Mary gets put
into the first name field, but Doe gets put into Middle instead of last.
I've isolated these and in all cases, if there is nothing in the last name
field and there is something in the middle name field, I want to replace the
value in Last (nothing) with the value in middle and delete middle. This is
the macro I've written so far, but I haven't done any excel macro's in over
a year, so I'm way rusty and I only have excel 97 to work with at the
moment.

Sub MoveLastName()

Dim MyRange As Range, cl As Range
Set MyRange = Range("c1", Range("c64000").End(xlUp))
For Each cl In MyRange
If IsEmpty(cl) And Not IsEmpty(cl.Offset(0, -1)) Then
Debug.Print cl.Row
With cl.Offset(0, -1)
Debug.Print cl
cl.Value = .Value
'.Delete
End With
End If
Next
End Sub

When I run this, I get goofy results that I can't quite place. I do get the
middle name in the lastname field, but then the middle name field gets data
in it from other odd places in the spreadsheet. What is wrong with my
function? Also, how can I debug.print a range object so I can see where the
data it is using is comming from?

Thanks

Matt

 

[Don Guillett]

see if this helps
http://www.cpearson.com/excel/FirstLast.htm

 

[Matt Williamson]

Thanks Don..

Unfortunately, the names don't follow the patterns that Chip's formulas
require, so other than giving me an interesting code example, this doesn't
help my immediate needs. I don't see any good way to modify it to meet them
either.

Matt

 

[Tom Ogilvy]

Sub ProcNames()
Dim MyRange As Range, cl As Range
Set MyRange = Range("c1", Range("c64000").End(xlUp))
For Each cl In MyRange
If IsEmpty(cl) And Not IsEmpty(cl.Offset(0, -1)) Then
With cl.Offset(0, -1)
cl.Value = .Value
.clearContents
End With
End If
Next
End Sub

 

[Harald Staff]

Hi Matt

If you have Outlook on your system: Dick Kusleika wrote a great little
routine that uses Outlook's name parser, remote controlled from Excel.
http://www.dicks-blog.com/archives/2004/11/11/parse-names-with-outlook/

HTH. Best wishes Harald

 

[Matt Williamson]

I was so close <g>

Thanks Tom

Sub ProcNames()
Dim MyRange As Range, cl As Range
Set MyRange = Range("c1", Range("c64000").End(xlUp))
For Each cl In MyRange
If IsEmpty(cl) And Not IsEmpty(cl.Offset(0, -1)) Then
With cl.Offset(0, -1)
cl.Value = .Value
.clearContents
End With
End If
Next
End Sub

--
Regards,
Tom Ogilvy


problem

 

[Matt Williamson]

Good stuff there Harold.
I actually ended up finding a routine in the NG's that Nick Hodge wrote. I
had to add a couple more scenarios to it for the goofy data I'm working
with, but overall, it works great.

I'll compare it against what this Outlook code gives me on the same dataset.

Thanks