Splitting Text by different delimiters

[Peter Davison]

I have the following excel cell formula

=IFERROR(MID($A14,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A14:$B14))+COLUMN(A14)),FIND($B$3,RIGHT($A14,LEN($A14)-IFERROR(FIND($B$3,$A14,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A14:$B14))+COLUMN(A14))-1),0))&$B$3)-1),"")

It looks at Cell B3 to determine the delimiter (e.g. a comma ,)

As my original data may have both a comma , or a dash (or maybe something different but lets say 2 for now) - is there a way of changing the formula above to look at two cells with different delimiters and then split accordingly? (e.g. Cells B3 and C3)

Thanks for your help

 

[Snakehips]

@Peter Davison It may help if you could give us a couple of examples of before and after.

 

[Peter Davison]

No problem.
This is what needs splitting

Wholesale;FRESH;MEALSOLS&SNACKING;FRESH-MEALSOLS&SNACKING-TRIAL-11250H.WSL

Wholesale;FRESH;MEALSOLS&SNACKING;FRESH-MEALSOLS&SNACKING-TRIAL-11250H.WSL

Wholesale;FRESH;MEAL SOLUTIONS;FRESH-MEALSOLS-TRIAL-10937H.NEW

Using the formula above it will only split with the semi colon ; which is in cell B3
So the result is -

Wholesale	FRESH	MEALSOLS&SNACKING	FRESH-MEALSOLS&SNACKING-TRIAL-11250H.WSL
Wholesale	FRESH	MEALSOLS&SNACKING	FRESH-MEALSOLS&SNACKING-TRIAL-11250H.WSL
Wholesale	FRESH	MEAL SOLUTIONS	FRESH-MEALSOLS-TRIAL-10937H.NEW

What I needed it to do is be able to split with a second option say the dash - or a third option the dot .

Any thoughts?
Thanks for you time

 

[taurean]

You can have few nested substitutes to handle the delimiters you need. Copy down and across.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(";"&$A2,";",REPT(" ",99)),"-",REPT(" ",99)),".",REPT(" ",99)),COLUMNS($A$1:A$1)*99,99))

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2
3		;	-
4
5	Wholesale;FRESH;MEALSOLS&SNACKING;FRESH-MEALSOLS&SNACKING-TRIAL-11250H.WSL	Wholesale	FRESH	MEALSOLS&SNACKING	FRESH	MEALSOLS&SNACKING	TRIAL	11250H.WSL
6	Wholesale;FRESH;MEALSOLS&SNACKING;FRESH-MEALSOLS&SNACKING-TRIAL-11250H.WSL	Wholesale	FRESH	MEALSOLS&SNACKING	FRESH	MEALSOLS&SNACKING	TRIAL	11250H.WSL
7	Wholesale;FRESH;MEAL SOLUTIONS;FRESH-MEALSOLS-TRIAL-10937H.NEW	Wholesale	FRESH	MEAL SOLUTIONS	FRESH	MEALSOLS	TRIAL	10937H.NEW
8
Lists

Cell Formulas
Range	Formula
B5:H7	B5	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5,$C$3,"</m><m>"),$B$3,"</m><m>"),"&","&amp;")&"</m></k>","//m"))
Dynamic array formulas.

 

[Peter Davison]

Fantastic result.
Thanks to everyone.
Your all brilliant.
It only let me mark one with a tick.

 

[Fluff]

Glad we could help & thanks for the feedback.