Parsing First and Last Names out of one field

I don’t know if you do a lot of string parsing, like getting First/Middle/Last name in one field. It’s something that I do fairly regularly. I got a spreadsheet of kids that I need to add to my system, and it has their full name in one field so I have to break it apart. Shouldn’t be a big deal.

Famous last words.

I actually couldn’t do it in Access, which was a first for me and quite got my knickers in a twist. Normally I’d do an update query with the Update From, but it didn’t work. I thought maybe the space between the first and last name was actually something weird in hex, but it wasn’t.  So I copied the data from Access back in to Excel (it was fewer than 500 records, and only eight fields or so). The Excel code is as follows:

=LEFT(B2,FIND(" ",B2))

And that should give you the first name. But it didn’t. Turned out that the field had a leading space! So I used the following code:

=LEFT(TRIM(B2),FIND(" ",TRIM(B2)))

And it worked. Then I had a clever thought for getting the last name:

=TRIM(RIGHT(B2,LEN(B2)-LEN(I2)))

(I2 being the first name)  Subtract the length of the first name from the full string length to give you the length of the last name, then a right() gives you the rest.

I was mildly proud of myself. After getting the data back in to an Access table in preparation of uploading it to SQL Server, I went ahead and trimmed leading and trailing spaces.

It’s not perfect. If they have a middle name, or a non-hyphenated compound last name, it’ll plug it all in to the last name and you’ll have to go back and do some manual cleanup. But it’ll get you most of the way there.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s