Results 1 to 2 of 2

Thread: Retrieve characters after nth occurence of a string

  1. #1

    Retrieve characters after nth occurence of a string

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I have this data in my rows:

    1. BBY TOYS SJC 15
    2. BBY TOYS CUP 25
    3. BBY TOYS SKU(15X10)10
    4. SKU BOX 1000
    5. SKU BOX 10000

    I need to dynamically retrieve the following from each:

    1. 15
    2. 25
    3. 10
    4. 1000
    5. 10000

    I used the following formula:
    =RIGHT(<CellNo>,LEN(<CellNo>)-FIND(" ",<CellNo>,10))

    It works for all the above rows except the 3rd, for which it retrieves: SKU(15X10)10
    Can you please help? Is there a better formula that I can use?


  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    Assuming that your 3rd instance is the only kind of deviation from the norm (ie the other samples), then try perhaps:

    =--TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,")"," ")," ",REPT(" ",100)),100))

    where A2 contains first original string. Copy down.

Posting Permissions

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