Parsing Text

[Guest]

I have text from another database where the field looks as follows:

Smith,John(Donna),Mr. & Mrs.

I need to separate this out into 4 columns.

Smith|John|Donna|Mr. & Mrs.

Is there an easy method to do thie?

 

[Marshall Barton]

I have text from another database where the field looks as follows:

Smith,John(Donna),Mr. & Mrs.

I need to separate this out into 4 columns.

Smith|John|Donna|Mr. & Mrs.

Is there an easy method to do thie?

Easy is a highly subjective term ;-)

This is a way to separate the four parts using a VBA
procedure:

Sub ParseName(strFull As String)
Dim start As Integer
Dim pos as Integer

pos = InStr(strFull, ",")
txtLastName = Left(strFull, pos - 1)

start = pos + 1
pos = InStr(start, strFull, "(")
txtFirstName = Mid(strFull, start, pos - 1)

start = pos + 1
pos = InStr(start, strFull, ")")
txtSpouseName = Mid(strFull, start, pos - 1)

txtTitle = Mid(strFull, pos + 1)
End Sub

That will work fine if txtLastName, etc, are text boxes in a
report (or form), but it will need a different approach if
you want to do this stuff in a query.

 

[Guest]

Marshall,

Thank you. I probably posted to the wrong group. I do want to run this via
a query and have part of it working, but am having trouble with the middle.

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

Plugging away, but any of your thoughts would be appreciated!

Thanks,
Sash

 

[Marshall Barton]

You have to locate the ( before you can look for the )

Spouse: Mid(LongName, InStr(LongName, "(") + 1,
InStr(LongName, ")") - InStr(LongName, "(") - 1)
--
Marsh
MVP [MS Access]

Thank you. I probably posted to the wrong group. I do want to run this via
a query and have part of it working, but am having trouble with the middle.

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

Easy is a highly subjective term ;-)

This is a way to separate the four parts using a VBA
procedure:

Sub ParseName(strFull As String)
Dim start As Integer
Dim pos as Integer

pos = InStr(strFull, ",")
txtLastName = Left(strFull, pos - 1)

start = pos + 1
pos = InStr(start, strFull, "(")
txtFirstName = Mid(strFull, start, pos - 1)

start = pos + 1
pos = InStr(start, strFull, ")")
txtSpouseName = Mid(strFull, start, pos - 1)

txtTitle = Mid(strFull, pos + 1)
End Sub

That will work fine if txtLastName, etc, are text boxes in a
report (or form), but it will need a different approach if
you want to do this stuff in a query.