Break names into separate fields

[Randal]

Is there a way to search for a space and break a name that is in one field
into 3 separate fields?

For example:

Field 1: John S. Doe
becomes
Field 2: John
Field 3: S.
Field 4: Doe

 

[Guest]

Randal:

You can use Instr(), Left(), Mid(), Right(), and InstrRev() functions to search for spaces and then parse your name. For example, you could create an update query, and use expressions similar to the ones I've listed below that I ran in the Immediate window of VBA.

strName = "David C. Atkins"
? left(strName, instr(strName, " ") - 1)
David

? right(strName, len(strName) - InstrRev(strName, " "))
Atkins

? mid(strName, instr(strName, " ") + 1, 2)
C.

------------------

This assumes, however, that your names are highly standardized. If you have names such as the following ...

Mr. and Mrs. David C. and Nancy Atkins, Jr., Executive Vice President
Smith, A. M.; Jones, T.; Tompson, J. T.
M/M David C. & Nancy L. Atkins
Atkins, David and Nancy
Van Gough, Martin and Sheila
Martin & Shiela Mc Donald
Tom and Mary Smith
James T. Bringham, Ph.D.
David & Nancy Atkins
Mr. John P. Williford & Ms. Mary K. Smith, Ph.D.

.... then you need a true heuristic name parser, like the one I've written. If you want it, email me this week at (e-mail address removed), and I'll send it to you.

David Atkins, MCP

 

[Ted Allen]

Hi Randal,

You can do this by using combinations of the Instr(), Left
(), Mid() and Len() functions.

Basically, Field2 would use the Left() function, and the
later fields would use Mid(), but you would then use
combinations of Instr() and Len() to set appropriate
starting points and lengths. For the later fields you
will likely have to nest some of the functions.

HTH, Ted Allen

 

[Malcolm Cook]

Split

 

[Malcolm Cook]

1) use split to split the string into an array, using space as delimiter.
Problems: what about double whitespace?
2) use regular expressions to find all "words" = "([\w|\.])*" and travers
the matches to extract the words

 

[John Wessell]

Try http://www.mvps.org/access/strings/index.html

Great site - saved my (beginner's) bacon more than once!

HTH

John

 

[Malcolm Cook]

Ted,

1) split(strField1," ") will return an array of strings delimited by
spaces - but confusion if multiple whitespace
2) better: use VBScript regular expressions to find all 'matches' to the
pattern "([\w|.])" - more work to learn but worith it if you parse strings
much

 

[Ted Allen]

Very good point Malcolm. I had not suggested Split()
because as far as I know you can only use it in a query
by calling a custom function (although I wouldn't be
surprised if I am wrong on this). With the sample
expression given of only a first name, initial, and last
name I didn't recommend a custom function because it
wouldn't be very difficult to parse with the other string
functions.

But, I fully agree that for anything more than that (or
maybe even just for the three fields) a custom function
is the way to go.

Let me know if you do know of a way to use Split() in a
query without a custom function call - I'm always looking
to learn new things in Access (which usually happens
through this newsgroup).

-Ted Allen

-----Original Message-----
Ted,

1) split(strField1," ") will return an array of strings delimited by
spaces - but confusion if multiple whitespace
2) better: use VBScript regular expressions to find all 'matches' to the
pattern "([\w|.])" - more work to learn but worith it if you parse strings
much


--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

Hi Randal,

You can do this by using combinations of the Instr(), Left
(), Mid() and Len() functions.

Basically, Field2 would use the Left() function, and the
later fields would use Mid(), but you would then use
combinations of Instr() and Len() to set appropriate
starting points and lengths. For the later fields you
will likely have to nest some of the functions.

HTH, Ted Allen

.

 

[Randal]

David,

Your name parser works really well. Looks like it cleaned up almost all my
names with exception of the suffix "DO".

Thanks a lot,

Randal:

You can use Instr(), Left(), Mid(), Right(), and InstrRev() functions to

search for spaces and then parse your name. For example, you could create
an update query, and use expressions similar to the ones I've listed below
that I ran in the Immediate window of VBA.

strName = "David C. Atkins"
? left(strName, instr(strName, " ") - 1)
David

? right(strName, len(strName) - InstrRev(strName, " "))
Atkins

? mid(strName, instr(strName, " ") + 1, 2)
C.

have names such as the following ...

Mr. and Mrs. David C. and Nancy Atkins, Jr., Executive Vice President
Smith, A. M.; Jones, T.; Tompson, J. T.
M/M David C. & Nancy L. Atkins
Atkins, David and Nancy
Van Gough, Martin and Sheila
Martin & Shiela Mc Donald
Tom and Mary Smith
James T. Bringham, Ph.D.
David & Nancy Atkins
Mr. John P. Williford & Ms. Mary K. Smith, Ph.D.

... then you need a true heuristic name parser, like the one I've written.

If you want it, email me this week at (e-mail address removed), and I'll send
it to you.

 

[Randal]

David,

Your name parser works really well. Looks like it cleaned up almost all my
names with exception of the suffix "DO".

Thanks a lot,

Randal:

You can use Instr(), Left(), Mid(), Right(), and InstrRev() functions to

search for spaces and then parse your name. For example, you could create
an update query, and use expressions similar to the ones I've listed below
that I ran in the Immediate window of VBA.

strName = "David C. Atkins"
? left(strName, instr(strName, " ") - 1)
David

? right(strName, len(strName) - InstrRev(strName, " "))
Atkins

? mid(strName, instr(strName, " ") + 1, 2)
C.

have names such as the following ...

Mr. and Mrs. David C. and Nancy Atkins, Jr., Executive Vice President
Smith, A. M.; Jones, T.; Tompson, J. T.
M/M David C. & Nancy L. Atkins
Atkins, David and Nancy
Van Gough, Martin and Sheila
Martin & Shiela Mc Donald
Tom and Mary Smith
James T. Bringham, Ph.D.
David & Nancy Atkins
Mr. John P. Williford & Ms. Mary K. Smith, Ph.D.

... then you need a true heuristic name parser, like the one I've written.

If you want it, email me this week at (e-mail address removed), and I'll send
it to you.

 

[Randal]

Good information - Thanks.

Try http://www.mvps.org/access/strings/index.html

Great site - saved my (beginner's) bacon more than once!

HTH

John