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

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

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


"Don Guillett" wrote in message
...
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

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


"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

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/2...-with-outlook/

HTH. Best wishes Harald

"Matt Williamson" skrev i melding
...
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

I was so close <g

Thanks Tom

"Tom Ogilvy" wrote in message
...
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


"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

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

"Harald Staff" wrote in message
...
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/2...-with-outlook/

HTH. Best wishes Harald

"Matt Williamson" skrev i melding
...
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