SOLVED

I need Excel Help

Copper Contributor

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.

BaseNameQtyIncrement
1234ABCDEF-A012340WABCD-01013ABCDEF-A012340WABCD-0101
ABCDEF-A012340WABCD-0102
ABCDEF-A012340WABCD-0103
4 Replies
best response confirmed by QuantumMaximus (Copper Contributor)
Solution

@QuantumMaximus 

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.

@QuantumMaximus 

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)
)

 image.png

@Peter Bartholomew 

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.

1 best response

Accepted Solutions
best response confirmed by QuantumMaximus (Copper Contributor)
Solution

@QuantumMaximus 

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.

View solution in original post