1. Registered User
Join Date
May 2004
Posts
18

I need to manipulate a string and take part of it into a variable

Example: Let say I have a string : "Bob Doe"
and a variable lastname

I want to manipulate the string so I only take the Doe for lastname so that
lastname = "Doe"

thanks all in advance for helping me

2. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
you first need to define how you want "Doe"

"Cruella de Ville"
"de Ville"
"deVille"
"Cruella"
"C. de Ville"
"Cruella de Ville Jr."
"Cruella Samantha de Ville"
...and you can imagine lots of other wierd cases i'm sure.

what is the "Doe" result do you want for these?

izy

3. Registered User
Join Date
Jun 2004
Location
Terrapin Nation
Posts
205
What you need to do is find the position with the space [chr(32)] and use a combonation of the length + inst functions to extract the last name. For instance:

Dim FullName As String
Dim LastName As String

Dim SpaceAt As Integer
Dim NameLength As Integer

FullName = "Terp FanInMD"

NameLength = Len(FullName)
SpaceAt = InStr(1, FullName, Chr(32))

LastName = Mid(FullName, SpaceAt, (NameLength - SpaceAt) + 1)

Debug.Print LastName

returns FanInMD

4. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
i guess it is impossible to have a perfect surname extract algorithm, so you end up with a compromise of one sort or another.

TerpInMD's algo returns "W. Bush" from "George W. Bush"

an alternative algo could look for the last space, but this would return "Jr." from "Cruella de Ville Jr."

izy

5. Registered User
Join Date
Jun 2004
Location
Terrapin Nation
Posts
205
He could check the data before it is ever inputed so that it is in forms which he can search out the last name.

Per his example, I think he may have done that. If not, he should.

6. Registered User
Join Date
Jun 2004
Location
Terrapin Nation
Posts
205
He could check the data before it is ever inputed so that it is in forms which he can search out the last name.

That "so that it is in the form he wants so he can search it".

sorry, in a hurry.

7. Registered User
Join Date
May 2004
Posts
18
I am sorry, I might have complicate the question...

all I need to know is the position of the letter in a string

lets say i have "Bob Doe"

I want to retrieve the "o" in the Doe, what do i have to do?
I know it's position is 6
so how do i retrieve a letter in position 6?

8. Registered User
Join Date
Aug 2002
Location
Northampton, England
Posts
266
The problem is that it might not always be the sixth character. Here is some code that will extract portions of text.

[Name] = “David Neagle”
Returned: David
Expression in field of query
expr: Left([Name],InStr(1,[Name]," ")-1)

[Name]=”David Neagle”
[Name]=”David John Neagle”
[Name]=”David J Neagle”
Returned: Neagle
Returned: John Neagle
Returned: J Neagle
Expression in field of query
expr: Right(Trim([Name]),Len(Trim([Name]))-InStr(1,[Name]," "))

[Name]=”David Neagle”
[Name]=”David-John Neagle”
[Name]=”David J Neagle
Returned: David
Returned: David-John
Returned: David
Expression in field of query
expr: Right(Trim([Name]),Len(Trim([Name]))-InStr(1,[Name]," "))

[Name]=”Neagle, David”
Returned: Neagle
Expression in field of query
expr: Left([Name],InStr(1,[Name],",")-1)

[Name]=”David John Neagle”
[Name]=”David J Neagle”
Returned: Neagle
Returned: Neagle
Expression in field query
expr: Right(Trim([Name]),Len(Trim([Name]))-InStr(InStr(1,[Name]," ")+1,[Name]," "))

[Name]=”David John Neagle”
[Name]=”David J Neagle”
Returned: John
Returned: J
Expression in query field
expr: Trim(Mid([Name],InStr(1,[Name]," ")+1,InStr(InStr(1,[Name]," ")+1,[Name]," ")-InStr(1,[Name]," ")))

9. Registered User
Join Date
May 2004
Posts
18
Dont' worry about the name covention is whatever...

i just want to get the character in a specific position on the string

like
let say my string is "ASDSXTXFSFSF"
I want the character in position 7 which is just a "T"

that's all i want in return: I want to specify characterposition = 7
returning character = "T"

Don't worry about the names and stuff..

sorry you be so confusing, maybe my english isn't clear

Thank you all for helping

10. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
Originally Posted by tb_vball
I am sorry, I might have complicate the question...

all I need to know is the position of the letter in a string

lets say i have "Bob Doe"

I want to retrieve the "o" in the Doe, what do i have to do?
I know it's position is 6
so how do i retrieve a letter in position 6?
xyz: Mid([YourFieldName],2,1)
xyz: Mid([YourFieldName],3,6)

The first number is where it starts and the second number is the number of characters.

So from Smith comes

m
ith

From Alexander comes

l
exande

A space is also picked up.

So xyz: Mid([YourFieldName],3,6) and B radley gives
radle and with a space before the radle, that is, radle is one space across from the left.

John Doe would give hn Doe

11. Registered User
Join Date
May 2004
Posts
18
Thank you so much...
that's just exactly what i needed

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•