How to split text

[Abdmujib]

Hello,

Please how can I split these strings with a common delimiter, the common Delimiter is "-", So I have difficulty in splitting it using Left, Right, Search functions because in some cases like B4 and B6 the delimiter appears twice leaving. so for case of B6 it will split it at the first "-" instead of the Second. Is there a way to go about it.

Note: the only thing that is common again is that there is are always three uppercase string before the "-". but my version does not support Regex.

Kindly help me out

 

[Abdmujib]

it won't work for B2 and B6 because they have two "-". @Joe4

 

[Joe4]

I deleted my original post when I realized it doesn't work. It actually WOULD work for B2, just not B6.
The issue with it isn't the two "-". That part is no problem. It is the capital letters in a row that causes my formulas not to work (where the first "-" it needs to look at HAS to be after the first set of three consecutive capital letters).
So my formula worked for all rows in your example except for row 6.

 

[Sergius]

Hi! As an option

Book1.xlsm
	J	K	L
22	Teams	Home	Away
23	Delta1-F58-1	Delta1	F58
24	Delta1-F	Delta1	F
25	Delta1-F-389	Delta1	F
26	Delta1-F-4-J	Delta1	F
Sheet3

Cell Formulas
Range	Formula
K23:K26	K23	=SUBSTITUTE(LEFT(MID("-"&SUBSTITUTE(J23:J26,"-",REPT("-",999)),1,999*2),999),"-","")
L23:L26	L23	=SUBSTITUTE(RIGHT(MID("-"&SUBSTITUTE(J23:J26,"-",REPT("-",999)),1,999*2),999),"-","")
Dynamic array formulas.

 

[Abdmujib]

I deleted my original post when I realized it doesn't work. It actually WOULD work for B2, just not B6.
The issue with it isn't the two "-". That part is no problem. It is the capital letters in a row that causes my formulas not to work (where the first "-" it needs to look at HAS to be after the first set of three consecutive capital letters).
So my formula worked for all rows in your example except for row 6

You're right. do you have a way you can work B6 out too

 

[Abdmujib]

Hi! As an option

Book1.xlsm
	J	K	L
22	Teams	Home	Away
23	Delta1-F58-1	Delta1	F58
24	Delta1-F	Delta1	F
25	Delta1-F-389	Delta1	F
26	Delta1-F-4-J	Delta1	F
Sheet3

Cell Formulas
Range	Formula
K23:K26	K23	=SUBSTITUTE(LEFT(MID("-"&SUBSTITUTE(J23:J26,"-",REPT("-",999)),1,999*2),999),"-","")
L23:L26	L23	=SUBSTITUTE(RIGHT(MID("-"&SUBSTITUTE(J23:J26,"-",REPT("-",999)),1,999*2),999),"-","")
Dynamic array formulas.

Thank You. it doesn't work. it has no way of differentiate home and away successfully.

 

[Joe4]

You're right. do you have a way you can work B6 out too

No, or else I would have posted it. Trying to write formulas that look at the case of characters in consecutive spaces is not my strong suit.
I believe it probably can be done, but is probably beyond my formula writing capabilities.
I could probably come up with a various "brute force" VBA solution, but it would be pretty ugly and inefficient.

 

[Abdmujib]

No, or else I would have posted it. Trying to write formulas that look at the case of characters in consecutive spaces is not my strong suit.
I believe it probably can be done, but is probably beyond my formula writing capabilities.
I could probably come up with a various "brute force" VBA solution, but it would be pretty ugly and inefficient.

REGEX would work but it's only on 365

 

[Joe4]

REGEX would work but it's only on 365

Yeah, there are a lot of good new functions, like TEXTSPLIT, that may also make things easier.

I am pretty sure you can use REGEX in VBA. So you should be able to write your own User Defined Function in VBA to do what you want.
I do not do much REGEX programming myself, so probably cannot offer any advice on that piece of it.

 

[Sufiyan97]

Try

Book1
	A	B	C	D	E
1
2		Teams	Home	Away
3		Anioly TorunAni-Bas BialystokBAS	Anioly TorunAni	Bas BialystokBAS
4		Astra Nowa SoIAST-B-BialaBBB	Astra Nowa SoIAST	B-BialaBBB
5		KS RudziniecRUD-Wks Czarni RadomWKS	KS RudziniecRUD	Wks Czarni RadomWKS
6		Avia-SwidnikAVI-McKis Energetyk JaworznoMCK	Avia-SwidnikAVI	McKis Energetyk JaworznoMCK
7		KS Lechia Tomaszow MazowieckiLEC-SiedlceSIE	KS Lechia Tomaszow MazowieckiLEC	SiedlceSIE
8
9
10
Sheet3

Cell Formulas
Range	Formula
C3:C7	C3	=IFERROR(LEFT(B3,MAX(AGGREGATE(15,6,FIND(CHAR(ROW($65:$90))&"-",B3),1),AGGREGATE(15,6,FIND("-"&CHAR(ROW($65:$90)),B3),1)-1)),LEFT(B3,AGGREGATE(15,6,FIND("-"&CHAR(ROW($65:$90)),B3),1)-1))
D3:D7	D3	=IFERROR(MID(B3,MAX(AGGREGATE(15,6,FIND(CHAR(ROW($65:$90))&"-",B3),1),AGGREGATE(15,6,FIND("-"&CHAR(ROW($65:$90)),B3),1)-1)+2,99),MID(B3,AGGREGATE(15,6,FIND("-"&CHAR(ROW($63:$88)),B3),1)+1,99))