Results 1 to 4 of 4

Thread: Need to extract a range of values from 1 cell ie. cell value is A011-A055

  1. #1

    Need to extract a range of values from 1 cell ie. cell value is A011-A055



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

    Is there a formula that could take single cells with a range of values and the list each individual value? For example cell A2 is "A011-A055" Is there a formula that would list A011,A012, A013.....,A055? Thanks for any ideas!

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    900
    Articles
    0
    Excel Version
    Excel 2013

    Extract sequence from alphanumeric data

    Quote Originally Posted by bradm0138 View Post
    For example cell A2 is "A011-A055" Is there a formula that would list A011,A012, A013.....,A055?
    Try formulas as follows
    First formula in B2
    Code:
    =IF(VALUE(RIGHT(IF(VALUE(MID(A2,2,1))=0,LEFT(RIGHT(A2,4),1)&MID(A2,2,1),LEFT(LEFT(A2,4),1))&VALUE(RIGHT(LEFT(A2,4),3)),3))>VALUE(RIGHT($A$2,3)),"",IF(VALUE(MID(A2,2,1))=0,LEFT(RIGHT(A2,4),1)&MID(A2,2,1),LEFT(LEFT(A2,4),1))&VALUE(RIGHT(LEFT(A2,4),3)))
    Second formula in C2 (copy down)
    Code:
    =IFERROR(IF(VALUE(RIGHT(IF(VALUE(MID($A$2,2,1))=0,LEFT(LEFT($A$2,4),1)&MID($A$2,2,1),LEFT(LEFT($A$2,4),1))&MID(B2,2,3)+1,3))>VALUE(RIGHT($A$2,3)),"",IF(VALUE(MID($A$2,2,1))=0,LEFT(LEFT($A$2,4),1)&MID($A$2,2,1),LEFT(LEFT($A$2,4),1))&MID(B2,2,3)+1),"")
    See attached example
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Thanks for the help! It worked for the specific example I gave. I am having trouble dragging the formula down to extract additional sequences. The sequence can vary... some are only numerical, some are alpha numerical. additional examples.. 4001-4678, W012A-W115C ...do these require a different formula? Thanks!

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    900
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by bradm0138 View Post
    The sequence can vary... some are only numerical, some are alpha numerical. additional examples.. 4001-4678, W012A-W115C ...
    Of course that requires a different formula.
    You should immediately tell.
    Also, you should set the example of several possible options. (No one can read your thoughts).
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Tags for this Thread

Posting Permissions

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