Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Posts
    18

    Unanswered: String Manipulation

    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. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you first need to define how you want "Doe"

    some cases for your consideration:
    "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
    currently using SS 2008R2

  3. #3
    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. #4
    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
    currently using SS 2008R2

  5. #5
    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. #6
    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. #7
    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. #8
    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. #9
    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. #10
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote 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. #11
    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
  •