0

I have a dynamic formula in google sheet that splits a rept string into multiple columns using this formula: =split(rept(A1,B1)," ").

But I can't seem to figure out how to do this in excel. Any suggestion would be helpful.

https://docs.google.com/spreadsheets/d/1IcIvhuwA3z2lngz-l4TwuenPgCMOGdCwe_Ukhu_f5zQ/edit?usp=sharing

enter image description here

Zyre Soriano
  • 575
  • 4
  • 13
  • 25
  • Wow. I've never heard of a spreadsheet function that *outputs to* multiple cells, but [that's what the docs say it does](https://support.google.com/docs/answer/3094136?hl=en). – T.J. Crowder Apr 22 '21 at 07:26

2 Answers2

3

If you have SEQUENCE() function access then try-

=TRANSPOSE(INDEX(A1,SEQUENCE(B1,,,0)))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
2

You could use FILTERXML() to mimic a split funcitonality:

enter image description here

Formula in A3:

=TRANSPOSE(FILTERXML("<t><s>"&REPT(A1&"</s><s>",B1)&"</s></t>","//s[.!='']"))

The above will spill the returned array horizontally using Microsoft365. If you don't have acces to dynamic array funtionality, you could use indices to return values from the resulting array.

For further explainations and examples using FILTERXML() I'd like to refer you to this Q&A on the topic.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you, solves my problem! I really hope excel has a much simple way to do this. – Zyre Soriano Apr 22 '21 at 07:36
  • @ZyreSoriano. If your task is not actually to find the equivalent to "split" functionality but just to repeat a certain value, then there is definately a better way. See the answer by Harun24HR for an example. – JvdV Apr 22 '21 at 07:45