Is there a way to split text in a cell?

Question

I am doing a survey on Microsoft Forms and the responses are coming through as one string in one cell; as follows:

Product1;Product2;Product3;Product4,...

Is there an way in excel to split these individual strings into individual columns?


Product1	Product2	Product3

Or using the following formula : =INDEX(TEXTSPLIT($A2,";"),COLUMN(A1)) the reason why i have wrapped within INDEX() because you have tagged Excel Tables otherwise. TEXTSPLIT(A2,";") — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Jul 3, 2024 at 15:39
And if you are on an older version of Excel you can use find(), left(), right() and mid(). — Solar Mike
– Solar Mike, Commented Jul 3, 2024 at 17:28

JB-007 · Accepted Answer · 2024-07-17 06:43:49Z

1

Several ways.

textsplit fn - Per comment above (source: @MB)
textsplit as array fn: if you wish to apply as an array function across many cell (say B2:b200), thne you need to revise the approach, e.g.

= let(x_, b3:B200,y_,lambda(a_,textsplit(",",1,a_))),y_)

FilterXML (if you don't have Office 365 compatible version of Excel nor were your signed up to Insider Office initiative to experiment with beta version functions/office features)

= Filterxml("<x><y>" & Substitute(A1,";","</y><y>")&"</y></x>,"//y")

See 8 ways including above I'm sure to split text here

PS - composed freehand - kindly ensure veracity/operability by testing. revert if issue(s)

Hopefully not ☺ ta

answered Jul 17, 2024 at 6:43

JB-007

2,6441 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1