SOLVED

I need Excel Help

Copper Contributor

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-0101ABCDEF-A012340WABCD-0102ABCDEF-A012340WABCD-0103
4 Replies
best response confirmed by QuantumMaximus (Copper Contributor)
Solution

Re: I need Excel Help

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.

Re: I need Excel Help

Thank you soo much Hans, that worked like a charm. I have spent at least 2 days trying to figure this out. Thanks again.

Re: I need Excel Help

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 @Hans Vogelaar 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)
)``````

Re: I need Excel Help

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.