How to separate text in a single column

[marie515]

Hi there,

I have a column with members names and the names are listed as follows:

James*R*Smith
Susan*L*Jameson
etc.

I would like to separate the 1st and last name into two separate columns. Can you advise me how I can tell the data to pull all characters to the left of the 1st "*" and to the right of the 2nd "*"?

Thank you.

 

[CajunCenturion]

If you are using A2K or later, then you be able to use the Split function to separate the name into its three parts. Look at the Split function in the help files.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[marie515]

Thanks - My company still uses Access 97 and I don't see anything related to the split function. Any other ideas/suggestions?

 

[CajunCenturion]

Then I would suggest something like the following:

FirstName = Left(FullName, Instr(Fullname, "*") - 1)
LastName = Mid(FullName, InStrRev(FullName, "*") + 1)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PHV]

I guess InStrRev isn't an option in ac97.
You may try this(from CajunCenturion's code):
FirstName = Left(FullName, InStr(Fullname, "*") - 1)
LastName = Mid(FullName, InStr(FullName, "*") + 3)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CajunCenturion]

A97 doesn't have InStrRev? That's a shame.

Without InStrRev is does get more complicated, as now, you have to deal with those names that do not have a middle initial, or those with more than one.

Do these conditions apply marie515?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[marie515]

Thanks for your help.

I entered the following in the field name in my query:
FirstName = Left(FullName, InStr(Fullname, "*") - 1

When I run it, it's asking me to "enter parameter value" for "first name". Am I supposed to use the real name of my field here? My field name is "name" Can you tell me where I should use my actual field name in the above example?

 

[CajunCenturion]

It depends on where in the query this statement is. If it's in the Select clause, then the proper syntax would be:

Left(FullName,InStr(Fullname,"*")-1) As FirstName

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[marie515]

I changed it to read as follows:

Name1 = Left(Name, InStr(Name, "*") - 1

When I run it, I get only "0" in the field returned. Any idea why?

 

[CajunCenturion]

Could you please post the entire query?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PHV]

Try this:
Name1: Left(Name, InStr(Name, "*") - 1)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[marie515]

PHV -- that worked! Now -- what should I type for the last name?

Thank you!!

 

[PHV]

Try something like this:
LastName: Mid(Name, InStr(Name, "*") + 3)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[marie515]

Thank you both so much for your help!! I can't tell you how long this would have taken me to fix this.

 

[lespaul]

Realize that PHV's solution for your last name issue will only work if all the records have a middle initial.

Leslie

 

[CajunCenturion]

To be more precise - have exactly 1 middle initial.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[marie515]

Lespaul -- My data consitently has 3 "*" in the name field. As an example, it may show any of the following ways:

Terri**Smith
Terri*L*Smith
Terri*Lynn*Smith

So, would the way in which PHV advised above -- work in each of the above cases?

Thanks.

 

[marie515]

I meant to say 2 "*" in the note above, not 3.

Sorry

 

[CajunCenturion]

PHV's method will then not work in the first and will not work in the third example, as that method assumes exactly 1 character for the middle initial. That is why I asked those questions earlier.

What will work, and it really does look messy, is the following:

FirstName: IIf((InStr(Mid(FullName, InStr(FullName, "*") + 1), "*") = 0), Mid(FullName, InStr(FullName, "*") + 1), Mid(Mid(FullName, InStr(FullName, "*") + 1), InStr(Mid(FullName, InStr(FullName, "*") + 1), "*") + 1))

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Actually, since you can guarantee that a second asterik will always be present, that is you will not have Terri*Smith, then you don't need the conditional, and the following should work:

FirstName: Mid(Mid(FullName, InStr(FullName, "*") + 1), InStr(Mid(FullName, InStr(FullName, "*") + 1), "*") + 1)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein