Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    TIP: >> Sort data in middle of a string (Excel 2000 >)

    Ever needed to sort data from the middle of a string, ie Sort (ascend) the 123 number of a product code like "Prod123-(SA). The solution is simple if you can extract the data first. See the attachment for a detailed analysis of the solution!
    Regards,
    Rudi

  2. #2
    Star Lounger
    Join Date
    Mar 2004
    Location
    Gulfport, Florida, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TIP: >> Sort data in middle of a string (Excel

    I understand how to extract data using Left, Mid, and Right, but is there a way to extract when the number of characters is not uniform? For example, my column has spouses first names enclosed in parens.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: TIP: >> Sort data in middle of a string (Excel

    Do some of the answers to <post#=184614>post 184614</post#> give you some ideas?

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TIP: >> Sort data in middle of a string (Excel

    If A1 is the cell with such a name, the following monster formula will return the name between the parentheses, if available.

    <code>=IF(ISERROR(FIND("(",A1)),"",MID(A1,FIND("(" ,A1)+1,FIND(")",A1,FIND("(",A1)+1)-FIND("(",A1)-1))</code>

    This formula could be made more transparent by putting <code>=FIND("(",A1)</code> in a separate cell ad referring to that cell 4 times instead of writing it out 4 times.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    Gulfport, Florida, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TIP: >> Sort data in middle of a string (Excel

    Thanks, Hans and Steve. The formula is perfect for me. I don't know VBA but it appears I should learn.

Posting Permissions

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