Is there a way to break a name field such as "Lname, Fname Initia.

[Roy Laws]

I have a need to import a large address list into Access from Excell. The
original list is not properly normalized. Is there any way to break up a
name field which consists of "LastName, FirstName Initial" into proper
subfields either while importing or by use of an update query after the data
is imported?

 

[Terry]

Roy,

There are lots of ways to go about seperating the name
components.

I would probably do it in excel before importing.
NOTE:
This assumes the name is in column "c".

In a blank excel column =trim(left(c1,find(c1,",")-1))
should give you the lastname. =Right(c1,len(c1)-find
(c1,","))should give you the firstname and initial.
Select the column with the Firstname & initial then under
data select text to columns delimited by a space. (You
want to copy and paste special "values" prior to this step)

This should seperate the name fields.

You could do all this in access with instr functions but
is probably easier in excel.

HTH,

Terry

 

[David Seeto via AccessMonster.com]

I would probably do it in excel before importing.

If you're going to do it in Excel, I wouldn't bother with the formula, but just use the Data -> Text to Columns function to split the single column into two columns: [Lname] and [Fname Initia], and then use it again on the second column into [Fname] and [Initia].

I'd use a similar approach in Access, if you have no control over the spreadsheet.