splitting a string?

[Jerome]

Hi, I've got the following problem:

I've inherited an old database with a table that contains ONE field for
both the first name and the surname! Now I'd need to separate them,
meaning I want to select the characters until the first space shows up
so I can paste them in a new column and then, in a second phase, all the
characters from the first space onwards to paste those in yet another
column!

What's the best way to do that? And I can I simply do that in a query?
Like using Right([field1];position of first space+1;x)for the surname
and Left([field1];position of first space;x) for the first name. How can
I get the position of the first space?

Thanks a lot!

 

[Rick Brandt]

Hi, I've got the following problem:

I've inherited an old database with a table that contains ONE field
for both the first name and the surname! Now I'd need to separate
them, meaning I want to select the characters until the first space
shows up so I can paste them in a new column and then, in a second
phase, all the characters from the first space onwards to paste those
in yet another column!

What's the best way to do that? And I can I simply do that in a query?
Like using Right([field1];position of first space+1;x)for the surname
and Left([field1];position of first space;x) for the first name. How
can I get the position of the first space?

Thanks a lot!

InStr() function will tell you where the space occurs. Check help for
specifics. You will need to use Mid() for the surname though rather than
Right(). Right() would require a number of positions to grab and InStr() is
going to give a position from the left, not from the right.

FirstName: Left([YourField], InStr(1, [YourField], " ") - 1)

SurName: Mid([YourField], InStr(1, [YourField], " ") + 1)

Of course if you have any "Mary Ann" type first names then this will produce
incorrect results.

 

[Jerome]

Hi, I've got the following problem:

I've inherited an old database with a table that contains ONE field
for both the first name and the surname! Now I'd need to separate
them, meaning I want to select the characters until the first space
shows up so I can paste them in a new column and then, in a second
phase, all the characters from the first space onwards to paste those
in yet another column!

What's the best way to do that? And I can I simply do that in a query?
Like using Right([field1];position of first space+1;x)for the surname
and Left([field1];position of first space;x) for the first name. How
can I get the position of the first space?

Thanks a lot!

InStr() function will tell you where the space occurs. Check help for
specifics. You will need to use Mid() for the surname though rather than
Right(). Right() would require a number of positions to grab and InStr() is
going to give a position from the left, not from the right.

FirstName: Left([YourField], InStr(1, [YourField], " ") - 1)

SurName: Mid([YourField], InStr(1, [YourField], " ") + 1)

Of course if you have any "Mary Ann" type first names then this will produce
incorrect results.

Thanks, I'll try that anyway. But you're right for the 'Mary Ann' type
names ... perhaps I should start at the end of the string and then use
the first space that comes. Family names tend to be one name or names
with a hyphen. On the other side you have portugese names like Dos
Santos .... argh, what a DB!?

 

[Per Larsen]

You can also use the Split function (as it works in VB6, included below):

Dim a As Variant
Dim i As Integer

a = Split("ABC DEF GHI", " ")
For i = 0 To UBound(a)
Debug.Print a(i)
Next i

Regards
PerL



'//****************************************************************************
'// Procedure: Function Split () As Variant
'// DateTime: 25.02.2004
'// Author: PerL
'//
'// Purpose: Split a string at positions where 'SplitChar' is found in
'// the string
'//
'// Returns: Variant type string array containing each substring
'//
'//****************************************************************************
Public Function Split(ByVal Expression As Variant, ByVal SplitChar As String) As Variant

Dim a As Variant
Dim pos1 As Long
Dim index As Long
Dim pos2 As Long

Expression = Expression & SplitChar

pos1 = InStr(1, Expression, SplitChar)

If (pos1 > 0) Then

ReDim a(0)
a(0) = Left(Expression, pos1 - 1)

pos2 = pos1 + Len(SplitChar)
pos1 = InStr(pos2, Expression, SplitChar)

While (pos1 > 0)
index = index + 1&
ReDim Preserve a(index)
a(index) = Mid(Expression, pos2, pos1 - pos2)
pos2 = pos1 + Len(SplitChar)
pos1 = InStr(pos2, Expression, SplitChar)
Wend

End If
Split = a

End Function

Jerome wrote:

Hi, I've got the following problem:

I've inherited an old database with a table that contains ONE field
for both the first name and the surname! Now I'd need to separate
them, meaning I want to select the characters until the first space
shows up so I can paste them in a new column and then, in a second
phase, all the characters from the first space onwards to paste those
in yet another column!

What's the best way to do that? And I can I simply do that in a query?
Like using Right([field1];position of first space+1;x)for the surname
and Left([field1];position of first space;x) for the first name. How
can I get the position of the first space?

Thanks a lot!

InStr() function will tell you where the space occurs. Check help for
specifics. You will need to use Mid() for the surname though rather than
Right(). Right() would require a number of positions to grab and InStr() is
going to give a position from the left, not from the right.

FirstName: Left([YourField], InStr(1, [YourField], " ") - 1)

SurName: Mid([YourField], InStr(1, [YourField], " ") + 1)

Of course if you have any "Mary Ann" type first names then this will produce
incorrect results.

Thanks, I'll try that anyway. But you're right for the 'Mary Ann' type
names ... perhaps I should start at the end of the string and then use
the first space that comes. Family names tend to be one name or names
with a hyphen. On the other side you have portugese names like Dos
Santos .... argh, what a DB!?