How can I split 1 field into 2?

[Guest]

I have one field with names with first and last names together, e.g. John
Smith. I need to split the first and last name into 2 fields, e.g. field
names: FirstName and LastName. Can this be done?

Best regards,

Kristjan

 

[Guest]

FirstName: Mid([Name],1,(InStr(1,[Name]," ",1))-1)

LastName: Mid([Name],(InStr(1,[Name]," ",1))+1,50)

This is assuming the name looks like this: John Doe

-Tony

 

[Steve Schapel]

Krisjan,

You can use an Update Query for this. You can add FirstName and
LastName fields to your table, and then use the Update Query to update
the FirstName field to...
Left([FullName],InStr([FullName]," ")-1)
.... and update the LastName field to...
Mid([FullName],InStr([FullName]," ")+1)

This will only work where the first name in the existing data is a
single word. For example, Billie Jo McAllister will end up as first
name Billie and last name Jo McAllister, so if you have any records like
this they will need to be attended to.

 

[Guest]

Thanks for your help. By doing the Update Query it worked. But one more
question. How can I do the same thing with middle names?
--
Best regards,

Kristjan

Krisjan,

You can use an Update Query for this. You can add FirstName and
LastName fields to your table, and then use the Update Query to update
the FirstName field to...
Left([FullName],InStr([FullName]," ")-1)
.... and update the LastName field to...
Mid([FullName],InStr([FullName]," ")+1)

This will only work where the first name in the existing data is a
single word. For example, Billie Jo McAllister will end up as first
name Billie and last name Jo McAllister, so if you have any records like
this they will need to be attended to.

--
Steve Schapel, Microsoft Access MVP

I have one field with names with first and last names together, e.g. John
Smith. I need to split the first and last name into 2 fields, e.g. field
names: FirstName and LastName. Can this be done?

Best regards,

Kristjan

 

[Douglas J. Steele]

Splitting names is a non-trivial thing to do, since you cannot be sure of
what the single field contains. Steve's already given you one example
(Billie Jo McAllister) where the first name is Billie Jo and the last name
McAllister, as opposed to first name Billie, middle name Jo and last name
McAllister. Another example would be Ludwig von Beethoven: again, there's no
middle name there, but the last name comprises of two words.

Mike Labosh outlines a good approach in
http://www.mcse.ms/archive147-2004-11-1255905.html There's also a KB article
about this at http://support.microsoft.com/?kbid=168799

There are 3rd party products that exist to do this. I have no experience
with them, but the one at http://www.infoplan.com.au/splitter/ sounds good.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your help. By doing the Update Query it worked. But one more
question. How can I do the same thing with middle names?
--
Best regards,

Kristjan

Krisjan,

You can use an Update Query for this. You can add FirstName and
LastName fields to your table, and then use the Update Query to update
the FirstName field to...
Left([FullName],InStr([FullName]," ")-1)
.... and update the LastName field to...
Mid([FullName],InStr([FullName]," ")+1)

This will only work where the first name in the existing data is a
single word. For example, Billie Jo McAllister will end up as first
name Billie and last name Jo McAllister, so if you have any records like
this they will need to be attended to.

--
Steve Schapel, Microsoft Access MVP

I have one field with names with first and last names together, e.g.
John
Smith. I need to split the first and last name into 2 fields, e.g.
field
names: FirstName and LastName. Can this be done?

Best regards,

Kristjan

 

[Guest]

I tried the Infoplan from Australia which Douglas wrote about at it´s
excellent. Thank your very much.
--
Best regards,

Kristjan

Splitting names is a non-trivial thing to do, since you cannot be sure of
what the single field contains. Steve's already given you one example
(Billie Jo McAllister) where the first name is Billie Jo and the last name
McAllister, as opposed to first name Billie, middle name Jo and last name
McAllister. Another example would be Ludwig von Beethoven: again, there's no
middle name there, but the last name comprises of two words.

Mike Labosh outlines a good approach in
http://www.mcse.ms/archive147-2004-11-1255905.html There's also a KB article
about this at http://support.microsoft.com/?kbid=168799

There are 3rd party products that exist to do this. I have no experience
with them, but the one at http://www.infoplan.com.au/splitter/ sounds good.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your help. By doing the Update Query it worked. But one more
question. How can I do the same thing with middle names?
--
Best regards,

Kristjan

Krisjan,

You can use an Update Query for this. You can add FirstName and
LastName fields to your table, and then use the Update Query to update
the FirstName field to...
Left([FullName],InStr([FullName]," ")-1)
.... and update the LastName field to...
Mid([FullName],InStr([FullName]," ")+1)

This will only work where the first name in the existing data is a
single word. For example, Billie Jo McAllister will end up as first
name Billie and last name Jo McAllister, so if you have any records like
this they will need to be attended to.

--
Steve Schapel, Microsoft Access MVP


Kristjan wrote:
I have one field with names with first and last names together, e.g.
John
Smith. I need to split the first and last name into 2 fields, e.g.
field
names: FirstName and LastName. Can this be done?

Best regards,

Kristjan

 

[wuzhangwu126]

I tried the Infoplan from Australia which Douglas wrote about at it¡äs
excellent. Thank your very much.
--
Best regards,

Kristjan

Splitting names is a non-trivial thing to do, since you cannot be sure of
what the single field contains. Steve's already given you one example
(Billie Jo McAllister) where the first name is Billie Jo and the last name
McAllister, as opposed to first name Billie, middle name Jo and last name
McAllister. Another example would be Ludwig von Beethoven: again, there's no
middle name there, but the last name comprises of two words.

Mike Labosh outlines a good approach in
http://www.mcse.ms/archive147-2004-11-1255905.html There's also a KB article
about this at http://support.microsoft.com/?kbid=168799

There are 3rd party products that exist to do this. I have no experience
with them, but the one at http://www.infoplan.com.au/splitter/ sounds good.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your help. By doing the Update Query it worked. But one more
question. How can I do the same thing with middle names?
--
Best regards,

Kristjan


:

Krisjan,

You can use an Update Query for this. You can add FirstName and
LastName fields to your table, and then use the Update Query to update
the FirstName field to...
Left([FullName],InStr([FullName]," ")-1)
.... and update the LastName field to...
Mid([FullName],InStr([FullName]," ")+1)

This will only work where the first name in the existing data is a
single word. For example, Billie Jo McAllister will end up as first
name Billie and last name Jo McAllister, so if you have any records like
this they will need to be attended to.

--
Steve Schapel, Microsoft Access MVP


Kristjan wrote:
I have one field with names with first and last names together, e.g.
John
Smith. I need to split the first and last name into 2 fields, e.g.
field
names: FirstName and LastName. Can this be done?

Best regards,

Kristjan