Forum Discussion
I need Excel Help
In my sheet, cell B2 has the following string "ABCDEF-A012340WABCD-0101" and cell C2 has a value of 3. I need a formula that outputs 3 separate lines in cell D2. Each line should be separated by 'alt+enter' and the strings value should be incremented so that the end result is.
ABCDEF-A012340WABCD-0101
ABCDEF-A012340WABCD-0102
ABCDEF-A012340WABCD-0103
Does anyone know what formula I can use to accomplish this? I have over 500 lines where I need to do this and for every line the value in c2 will be different.
Basically I am seeking a formula that looks at the name and quantity and outputs what is in the result in the increment columns as shown below. Any help would greatly be appreciated.
| Base | Name | Qty | Increment |
| 1234 | ABCDEF-A012340WABCD-0101 | 3 | ABCDEF-A012340WABCD-0101 ABCDEF-A012340WABCD-0102 ABCDEF-A012340WABCD-0103 |
In D2:
=TEXTJOIN(CHAR(10), TRUE, LEFT(B2, LEN(B2)-4)&TEXT(SEQUENCE(C2, , --RIGHT(B2, 4)), "0000"))
Turn on 'Wrap Text' for D2.
Fill down.
In D2:
=TEXTJOIN(CHAR(10), TRUE, LEFT(B2, LEN(B2)-4)&TEXT(SEQUENCE(C2, , --RIGHT(B2, 4)), "0000"))
Turn on 'Wrap Text' for D2.
Fill down.
- Thank you soo much Hans, that worked like a charm. I have spent at least 2 days trying to figure this out. Thanks again.
My formula looks OK on the worksheet
= MAP(name, qty, ToListsλ)and calculates the entire array. It is once one opens the Lambda function, that I start to lose points to HansVogelaar on the grounds of complexity!
=LET( numeric, TEXTAFTER(string, "-", 2), text, TEXTBEFORE(string, "-", 2), seq, SEQUENCE(n, , numeric), combined, text & TEXT(seq, "-0000"), TEXTJOIN(CHAR(10), , combined) )Thank you for the response Peter. Not to worry, for an Excel novice like me both yours and Hans’ formulas are way over my head. I will be studying them both to understand how they really work.
