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!

    Hi,


    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?


    Thanks

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    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
  •