Name parsing formula..

  • Thread starter Thread starter Matt Williamson
  • Start date Start date
M

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
 
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
 
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
 
I was so close <g>

Thanks Tom

Tom Ogilvy said:
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
Click to expand...
 
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
 
Back
Top