Forum Discussion

Alex_Barbosa99's avatar
Alex_Barbosa99
Copper Contributor
Apr 30, 2023

How to divide cell value in many lines?

Hello,   I would like to create a macro to divide cell value in many lines as the following:   Input: A 360 B 120 C 240 D 480 E 60   Output: A 120 A 120 A 1...
excel
Macros and VBA
mtarler's avatar
mtarler
Silver Contributor
May 01, 2023

Alex_Barbosa99  I have been avoiding macros because of security/compatibility issues.  This could be accomplished using a formula:

=LET(in,A2:B6,base,D2,
baseStack,LAMBDA(inRow,base,LET(
      name, INDEX(inRow,1),
      num, INDEX(inRow,2),
      partial,MOD(num,base),
      nBases, QUOTIENT(num,base),
      outA,IF(nBases,VSTACK("",HSTACK(TEXTSPLIT(REPT(name&";",nBases),,";",1),SEQUENCE(nBases,,base,0))),""),
      DROP(IF(partial,VSTACK(outA,HSTACK(name,partial)),outA),1))),
DROP(REDUCE("",SEQUENCE(ROWS(in)),LAMBDA(p,q,VSTACK(p,baseStack(INDEX(in,q,0),base)))),1))

someone else may even give a more simple formula but this could also be put into a lambda in the name manager if desired.

Resources