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!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Need to extract a range of values from 1 cell ie. cell value is A011-A055
- Thread starter bradm0138
- Start date
navic
New member
- Joined
- Aug 27, 2013
- Messages
- 901
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Excel 2013
Extract sequence from alphanumeric data
First formula in B2
Second formula in C2 (copy down)
See attached example
Try formulas as followsFor example cell A2 is "A011-A055" Is there a formula that would list A011,A012, A013.....,A055?
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)))
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),"")Attachments
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!
navic
New member
- Joined
- Aug 27, 2013
- Messages
- 901
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Excel 2013
Of course that requires a different formula.The sequence can vary... some are only numerical, some are alpha numerical. additional examples.. 4001-4678, W012A-W115C ...
You should immediately tell.
Also, you should set the example of several possible options. (No one can read your thoughts).