Forum Discussion

QuantumMaximus's avatar
QuantumMaximus
Copper Contributor
Nov 05, 2023

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.

BaseNameQtyIncrement
1234ABCDEF-A012340WABCD-01013ABCDEF-A012340WABCD-0101
ABCDEF-A012340WABCD-0102
ABCDEF-A012340WABCD-0103
Excel
Formulas and Functions
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Nov 05, 2023

    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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 05, 2023

    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.

    • QuantumMaximus's avatar
      QuantumMaximus
      Copper Contributor
      Nov 05, 2023
      Thank you soo much Hans, that worked like a charm. I have spent at least 2 days trying to figure this out. Thanks again.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 05, 2023

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

     

    • QuantumMaximus's avatar
      QuantumMaximus
      Copper Contributor
      Nov 07, 2023

      PeterBartholomew1 

      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.

Share

Resources