Re: Name parsing formula..

From: Matt Williamson (ih8spam_at_spamsux.org)
Date: 12/16/04 

Date: Thu, 16 Dec 2004 18:17:14 -0500

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" <innocent@enron.invalid> wrote in message
news:%23ZZj3D74EHA.3784@TK2MSFTNGP10.phx.gbl...
> Hi Matt
>
> If you have Outlook on your system: *** 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" <ih8spam@spamsux.org> skrev i melding
> news:uGRLdi54EHA.3244@TK2MSFTNGP10.phx.gbl...
> > 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 spread***. 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
> >
> >
>
>