delimitter formula

[ashani]

Hi,

Can someone please guide me with Delimiter Formula rather than Text to Column.

I have a cell value A;B;C and I want to split it to seperate columns.

Thank you

 

[Fluff]

Can you please post some representative samples of the strings you want to split.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ashani]

Hi @Fluff

so currently i have a list which updates daily through MS forms - in column H it comes up as : School;Uni;Pre;College.

What I want from column J to M to split all 4 of them in to seperate columns.

Sometimes there are all 4 of them, sometime could be only say School;Uni. All depends what user input in the inform.

Many thanks,

 

[Fluff]

Please post some representative samples of the strings you want to split, as requested.
Also are you doing this in the desktop version of Excel or with the Online Browser version?

 

[Peter_SSs]

Would this suit?

21 12 11.xlsm
	H	I	J	K	L	M	N
1
2	A;B;C		A	B	C
3	School;Uni;Pre;College		School	Uni	Pre	College
4	The cat sat on the mat;The dog ran out the door;The mouse stayed still		The cat sat on the mat	The dog ran out the door	The mouse stayed still
Split Text

Cell Formulas
Range	Formula
J2:L2,J4:L4,J3:M3	J2	=TRANSPOSE(FILTERXML("<p><c>"&SUBSTITUTE($H2,";","</c><c>")&"</c></p>","//c"))
Dynamic array formulas.