CODEWITHSUNDEEP
excelexcel-formula
Zyre Soriano
Zyre Soriano

Reputation: 605

What is the equivalent of Split function in excel?

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

Upvotes: 0

Views: 843

Answers (2)

Harun24hr
Harun24hr

Reputation: 36870

If you have SEQUENCE() function access then try-

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

enter image description here

Upvotes: 3

JvdV
JvdV

Reputation: 75840

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.

Upvotes: 2

Related Questions