This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I want to try an array formula that spills
I tried this =HSTACK(J18,TOCOL(TEXTSPLIT(K18," "))) but number in A column doesn't repeat
Sample data
| 898990 | Cat Mat |
|---|---|
| 898767 | Nam Bam |
| 767676 | Gam Jam |
Either way is okay
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Hi,
I would solve this in the Query Editor. However, if you wish to use a formula, then enter this formula in cell D2
=LET(spt,DROP(REDUCE(" ",B2:B4,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ")))),1),HSTACK(TOCOL(IF(spt<>"",A2:A4),2),TOCOL(spt,2)))
Hope this helps.
Answer accepted by question author
This one?
=HSTACK(TEXTSPLIT(TEXTJOIN(" ",,TRIM(REPT(A1:A3&" ",2))),," "),TEXTSPLIT(TEXTJOIN(" ",,B1:B3),," "))
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
Exactly, I was tying figure out this part TEXTJOIN(" ",,TRIM(REPT(J18:J19&" ",2))) how to add space in REPT I was exploring MID for that.
I was trying to make use MAP LAMDA EXPAND FILTER to repeat column A values
You hard coded 2 in REPT need to make it dynamic for below example it will return #N/A
77676 GTR NNT MMU
Updated formula that works on more than 2 values in column B
=HSTACK(TEXTSPLIT(TEXTJOIN(" ",,TRIM(REPT(J18:J20&" ",MAP(K18:K20,LAMBDA(x,COUNTA(TEXTSPLIT(x," "))))))),," "),TEXTSPLIT(TEXTJOIN(" ",,K18:K20),," "))
Update the range in LET
=LET(num,J18:J20,txt,K18:K20,HSTACK(TEXTSPLIT(TEXTJOIN(" ",,TRIM(REPT(num&" ",MAP(txt,LAMBDA(x,COUNTA(TEXTSPLIT(x," "))))))),," "),TEXTSPLIT(TEXTJOIN(" ",,txt),," ")))