Splitting a field

[Donna Brooks]

Hi,
I have an Excel spreadsheet with the First and Last names
combined in one field and I need to split it into two
fields. Could someone please tell me how I can do this?
I'm pretty new at the Access thing.

Thanks in advance,
Donna B.

 

[Allen Browne]

After importing (or attaching) the spreadsheet data into a field named (say)
FullName, create a query into this table.

Enter this expression into a fresh column of the query design grid, in the
Field row:
FirstName: Split([FullName], " ")(0)
and in another column:
LastName: Split([FullName], " ")(1)

After verifying that these expressions give the right results, you can
change the query to an Update query (Update on Query menu), and place a
similar expression in the Update row under each field. Drop the alias. So
the Update row under your FirstName field will be:
Split([FullName], " ")(0)

The Split function parses the FullName field at the space.
The number in brackets specifies which word you want, where 0 is the first
word, 1 is the next, and so on.

After running the update query, you will need to check for names that have
embedded spaces, e.g.:
Julie van Leen

 

[Guest]

I am getting an error that says "You may have entered an
invalid identifier or typed parentheses following the
Null constant. I know I need to learn my syntax, but I'm
working on it. Help please.

-----Original Message-----
After importing (or attaching) the spreadsheet data into a field named (say)
FullName, create a query into this table.

Enter this expression into a fresh column of the query design grid, in the
Field row:
FirstName: Split([FullName], " ")(0)
and in another column:
LastName: Split([FullName], " ")(1)

After verifying that these expressions give the right results, you can
change the query to an Update query (Update on Query menu), and place a
similar expression in the Update row under each field. Drop the alias. So
the Update row under your FirstName field will be:
Split([FullName], " ")(0)

The Split function parses the FullName field at the space.
The number in brackets specifies which word you want, where 0 is the first
word, 1 is the next, and so on.

After running the update query, you will need to check for names that have
embedded spaces, e.g.:
Julie van Leen

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I have an Excel spreadsheet with the First and Last names
combined in one field and I need to split it into two
fields. Could someone please tell me how I can do this?
I'm pretty new at the Access thing.

Thanks in advance,
Donna B.

.