Breaking apart text

[kfowlow]

Group,
This maybe simple process that I should probably do in Excel but is there an
easy way of say breaking apart a name into two columns? I have some text data
that I need spearated out by first and last name. I could probably do it in
Excel and then import it back in but I thought there was a formula in Access
that will do this. Any help is appreciated.

Keith Fowlow

 

[Daniel]

use the split function

arrnames = split("daniel P"," ")
debug.print arrnames(0) 'daniel
debug.print arrnames(1) 'p

another option is use the instr to locate the position of the space and then
use the left and right function to extract the string before/after.

 

[John W. Vinson]

Group,
This maybe simple process that I should probably do in Excel but is there an
easy way of say breaking apart a name into two columns? I have some text data
that I need spearated out by first and last name. I could probably do it in
Excel and then import it back in but I thought there was a formula in Access
that will do this. Any help is appreciated.

Keith Fowlow

This may be trickier than it appears: Rhoda Mae Jones first name is Rhoda Mae;
Leo van Steen's last name is van Steen. You'll need to use a USB (Using
Someone's Brain) interface to resolve all such issues!

But for the most common two-name case, you can use an Update query. If you
currently have a field Fullname, add new fields Firstname and Lastname; run an
Update query updating FirstName to

Left([Fullname], InStr([Fullname], " "))

and LastName to

Mid([Fullname], InStr([Fullname], " ") + 1)

Use a criterion of

LIKE "* *"

on Fullname to select only those names with a blank.

Then run a separate query with a criterion of

LIKE "* *"

on LastName to select all the three-word names for manual processing.

John W. Vinson [MVP]