Results 1 to 4 of 4

Thread: Cutting off part of a word in one cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2005
    Location
    Virginia
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cutting off part of a word in one cell

    Problem: I want to sort part #'s by type, but the # comes first so if I sort it will go by the parts number.

    ex: Srg000241-A500
    Srg000356-B3779

    where A500=machine 1 and B3779=machine 2


    I have a bunch of those parts and they all end in -_____. I want to sort by what comes after the dash. My idea was to search the cell for the dash, copy everything that comes after the dash and then paste it into a row of empty cells and then sort by this new row.

    Can anyone help with this code?

    Thanks!

  2. #2
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Kevin,

    Assuming the original data is in A1 then add this to B1:

    =MID(A1,FIND("-",A1)+1,999)

    HTH
    Richie

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default

    Try Data>Text to Columns... with - as a delimiter.
    If posting code please use code tags.

  4. #4
    Board Regular
    Join Date
    Mar 2005
    Location
    Juneau, Alaska
    Posts
    365
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ritchie,

    I was looking at your formula and I don't quite follow how it functions...

    =MID(A10,FIND("-",A10)+1,999)

    I follow the formula up to the +1, but I don't understand the 999 part.

    If you have time to post an explanation, I would really appreciate it. This is an interesting formula to me...

    Thanks
    Wally

    *************

    Ok, I get it....you used 999 as a really high number of characters to look up.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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