Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tokenize Strings (2000)

    Hello!

    Say I have a strings as such:

    Kevin Liu - 12312415126
    John Doe - 21421526212

    How can I trim these strings so that the only part preserved is the string preceding the " - " to create

    Kevin Liu
    John Doe

    Basically, I want to cut a string up to the occurence of the dash.

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tokenize Strings (2000)

    If the number of digits to the right of the dash is always 11, the following should work:

    <pre>=LEFT(A1,(LEN(A1)-13))
    </pre>



    Where A1 is the range of the cell which contains the text.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tokenize Strings (2000)

    Hi Kevin,

    Assuming the string is in cell A1, use the following formulas to get each piece:

    <pre>=RIGHT(A1,SEARCH("- ",A1,1))
    =LEFT(A1,LEN(A1)-SEARCH(" -",A1,1)-2)
    </pre>


    The first one returns "12312415126". Note that the sub-string with the dash in it also has the extra space. This works, assuming that the separator always has the space.

    The second one returns "Kevin Liu". The extra -2 near the end accounts for the dash and space; without it, you would get "Kevin Liu -".

    Hope this helps!

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tokenize Strings (2000)

    It looks like your problem has been solved in the previous posts, but there is another method that requires no code.

    Select the data you want to tokenise.
    Using the text to columns option in the Data menu, select Delimited and in the delimiter selection box choose other and type in the character '-' (minus the quotes)

    Press finish and it is done.

    A word of warning
    - this will expand your list to as many colums wide as there are '-' in the cell (you can perform the oprtation on the whole list at once) so make sure that the columns to the right are empty.
    - It also does not trim the trailing space in the name portion, but that can be fixed though use of the TRIM function (refer to excel help)

Posting Permissions

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