Name Parsing

[theif68]

I have a database with the names in 1 column, is there a way to parse these out?

Column 1
Mr John Brown Jr.

TO

Expr1 Expr2 Expr3 Expr4
Mr John Brown Jr.

 

[mhartman1]

Hello:
Yes. You would do this in a query. Make a query with your column data in it. Enclosed are the first two expressions to extract your data.
'
Expr1: Left([Field1],InStr(1,[Field1]," ")-1)
'
Expr2: Mid([Field1],InStr(1,[Field1]," ")+1,InStr(InStr(1,[Field1]," ")+1,[Field1]," ")-InStr(1,[Field1]," ")-1)
'
Substitute your column name for Field1 in all the statements. Continue the same procedure for the rest.

Refer to Left, Mid, and Right functions in VBA
Regards
Mark

 

[alvechurchdata]

is there a way to parse these out?

Mark's routine will break the entries up into words but you might have to write extra code to deal with entries which don't exactly match the pattern of [title] [firstname] [lastname] [suffix]. Even then you'll have to check the results by eye - there are bound to be some weird entries that don't fit.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[missinglinq]

Geoff is right. Names are just like addresses when it comes to separating the various components. In my younger days I did some temp work for a huge bank that spents 10's 0f 1000's of dollars in their IT department trying to figure out how to "automatically" import data from an aquired bank's system. They finally spent far less hiring temps to re-enter the data by hand!

Good luck!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[theif68]

Thanks, Expr1 Works But I get an Error on Expr2, on all but the first record.


Example Query Results:

Expr2
Whittlesey
#Error
#Error
#Error
#Error
#Error