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

--
Don Guillett
SalesAid Software

"Matt Williamson" wrote in message
...
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