Forum Discussion
Solved
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...
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.
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.
