split data from 1 column into 2 columns

[Guest]

I am using Access 2005 and want to separate first and last name when they are
in the same column in a table. Thank you.

 

[John Vinson]

I am using Access 2005

That's a good trick, since that version doesn't yet exist, if it ever
will!

and want to separate first and last name when they are
in the same column in a table. Thank you.

If (as you should) you want to permanently store these names in
LastName and FirstName fields in the table, you can use an Update
query. Assuming that you have names stored in the format

Firstname Lastname

you can add the two new fields to your table; then run an Update query
updating FirstName to

Left([fullname], InStr([fullname], " ") - 1)

and LastName to

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

YOu may then want to run a query searching LastName using the
criterion

LIKE "* *"

This will find instances where the person's first name contains a
blank, such as "Billy Bob" or "Robert A." or "Rhoda Mae" - the
"middle" name will have been incorrectly included in the LastName
field and will need to be manually corrected.

John W. Vinson[MVP]

 

[pauld]

I saw this posting and hope you can help me with a simialr question

I have a field called SkipperName with a format of last, first.

I want to split it into FirstName and LastName.

How do I do this and get rid of the comma and space before the first name?

I am using Access 2005

That's a good trick, since that version doesn't yet exist, if it ever
will!

and want to separate first and last name when they are
in the same column in a table. Thank you.

If (as you should) you want to permanently store these names in
LastName and FirstName fields in the table, you can use an Update
query. Assuming that you have names stored in the format

Firstname Lastname

you can add the two new fields to your table; then run an Update query
updating FirstName to

Left([fullname], InStr([fullname], " ") - 1)

and LastName to

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

YOu may then want to run a query searching LastName using the
criterion

LIKE "* *"

This will find instances where the person's first name contains a
blank, such as "Billy Bob" or "Robert A." or "Rhoda Mae" - the
"middle" name will have been incorrectly included in the LastName
field and will need to be manually corrected.

John W. Vinson[MVP]

 

[Beetle]

Before you do anything, MAKE A BACKUP!

You would add LastName and FirstName fields to your table,
then as John suggested in that thread, you would use an Update
query but your calculated fields would look like;

LastName: Left([SkipperName], Instr([SkipperName], ",")-1)

FirstName: Mid([SkipperName], Instr([SkipperName], ",")+2)

The above assumes that the format for SkipperName is always
last name followed by a comma and space followed by first name.

Then you could delete the SkipperName field once you verify that
everything updates like you want.

__________

Sean Bailey

I saw this posting and hope you can help me with a simialr question

I have a field called SkipperName with a format of last, first.

I want to split it into FirstName and LastName.

How do I do this and get rid of the comma and space before the first name?

I am using Access 2005

That's a good trick, since that version doesn't yet exist, if it ever
will!

and want to separate first and last name when they are
in the same column in a table. Thank you.

If (as you should) you want to permanently store these names in
LastName and FirstName fields in the table, you can use an Update
query. Assuming that you have names stored in the format

Firstname Lastname

you can add the two new fields to your table; then run an Update query
updating FirstName to

Left([fullname], InStr([fullname], " ") - 1)

and LastName to

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

YOu may then want to run a query searching LastName using the
criterion

LIKE "* *"

This will find instances where the person's first name contains a
blank, such as "Billy Bob" or "Robert A." or "Rhoda Mae" - the
"middle" name will have been incorrectly included in the LastName
field and will need to be manually corrected.

John W. Vinson[MVP]

 

[tina]

try John's expressions, slightly altered, as

Left([fullname], InStr([fullname], ", ") - 1)
Mid([fullname], InStr([fullname], ", ") + 2)

and as he said, hopefully you're splitting the name field into two separate
fields in your table. however, if you want to just *display* the name as
first/last without changing the field value, you can use a custom function,
as

Public Function isSplitName(ByVal str As String) As String

isSplitName = Split(str, ", ")(1) & " " & Split(str, ", ")(0)

End Function

and include it as a calculated field in a query that includes the table with
the name field in it, as

FixedName: isSplitName([MyNameField])

hth

I saw this posting and hope you can help me with a simialr question

I have a field called SkipperName with a format of last, first.

I want to split it into FirstName and LastName.

How do I do this and get rid of the comma and space before the first name?

I am using Access 2005

That's a good trick, since that version doesn't yet exist, if it ever
will!

and want to separate first and last name when they are
in the same column in a table. Thank you.

If (as you should) you want to permanently store these names in
LastName and FirstName fields in the table, you can use an Update
query. Assuming that you have names stored in the format

Firstname Lastname

you can add the two new fields to your table; then run an Update query
updating FirstName to

Left([fullname], InStr([fullname], " ") - 1)

and LastName to

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

YOu may then want to run a query searching LastName using the
criterion

LIKE "* *"

This will find instances where the person's first name contains a
blank, such as "Billy Bob" or "Robert A." or "Rhoda Mae" - the
"middle" name will have been incorrectly included in the LastName
field and will need to be manually corrected.

John W. Vinson[MVP]

 

[John W. Vinson]

I saw this posting and hope you can help me with a simialr question

I have a field called SkipperName with a format of last, first.

I want to split it into FirstName and LastName.

How do I do this and get rid of the comma and space before the first name?

The InStr() function returns a number, the position of a substring within a
string. So if your SkipperName field contains "Nescio, Nomen" the expression

InStr(",", [SkipperName])

would return 7 - the comma is the seventh character in the field.

If you want to extract the last name, you want the leftmost six characters -
up to but not including the comma; so you could use

Left([SkipperName], InStr([SkipperName], ",") - 1)

The Instr returns seven, so you subtract one to get up to but not including
that position.

To get the firstname, you want to start two characters past the position of
the comma. Actually, since some user might have mistakenly typed
"Nescio,Nomen" - leaving out the blank - it's safer to just get the rest of
the string after the comma and trim off the blank using the builtin Trim()
function:

Trim(Mid([SkipperName], InStr([SkipperName], ",") + 1))

From the inside out, InStr finds the position of the comma (7); you add 1 to
get past the comma; Mid([SkipperName], 8) starts at the 8th character - the
blank - and returns a text string " Nomen"; and Trim throws away the leading
blank.

 

[pauld]

The expression for LastName copies the comma.

How do I get rid of the comma at the end of the last name?

Am I maybe not seeing the spacing in the expression correctly?

Thanks.

Before you do anything, MAKE A BACKUP!

You would add LastName and FirstName fields to your table,
then as John suggested in that thread, you would use an Update
query but your calculated fields would look like;

LastName: Left([SkipperName], Instr([SkipperName], ",")-1)

FirstName: Mid([SkipperName], Instr([SkipperName], ",")+2)

The above assumes that the format for SkipperName is always
last name followed by a comma and space followed by first name.

Then you could delete the SkipperName field once you verify that
everything updates like you want.

__________

Sean Bailey

I saw this posting and hope you can help me with a simialr question

I have a field called SkipperName with a format of last, first.

I want to split it into FirstName and LastName.

How do I do this and get rid of the comma and space before the first name?

On Sun, 20 Mar 2005 15:29:01 -0800, "kiltnshirt"

I am using Access 2005

That's a good trick, since that version doesn't yet exist, if it ever
will!

and want to separate first and last name when they are
in the same column in a table. Thank you.

If (as you should) you want to permanently store these names in
LastName and FirstName fields in the table, you can use an Update
query. Assuming that you have names stored in the format

Firstname Lastname

you can add the two new fields to your table; then run an Update query
updating FirstName to

Left([fullname], InStr([fullname], " ") - 1)

and LastName to

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

YOu may then want to run a query searching LastName using the
criterion

LIKE "* *"

This will find instances where the person's first name contains a
blank, such as "Billy Bob" or "Robert A." or "Rhoda Mae" - the
"middle" name will have been incorrectly included in the LastName
field and will need to be manually corrected.

John W. Vinson[MVP]

 

[Beetle]

Did you include the -1 at the end of the expression?

__________

Sean Bailey