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]

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))

Wow Thank you so much.

It worked for all the examples I gave. but I went through the formula to understand how it works in case I come across another scenario in the future.

for Data in the picture attached, it was unable to separate it very well. is it possible you look for 3 Capital Letters follow by "-"

Thank you so much

 

[Sufiyan97]

instead of posting as picture, can you post your data using xl2bb? if that is not possible then just copy paste here as text.

 

[Abdmujib]

instead of posting as picture, can you post your data using xl2bb? if that is not possible then just copy paste here as text.

okay

B-BialaBBB-KS RudziniecRUD

Anioly-TorunANI-Bas BialystokBAS

B-BialaBBB-KS Arka Tempo ChelmARK

 

[Sufiyan97]

for Data in the picture attached, it was unable to separate it very well. is it possible you look for 3 Capital Letters follow by "-"

View attachment 117275

IGNORE
in 2nd line there are no 3 capital letters followed by "-", so what will be the criteria?

 

[Abdmujib]

IGNORE
in 2nd line there are no 3 capital letters followed by "-", so what will be the criteria?

first line hass "BBB-"
Second line has "ANI-"
Third has "BBB-"

 

[Sufiyan97]

Try

How to Split text AGGREGATE FIND.xlsx
	A	B	C	D
1	Teams	Home	Away
2	Anioly TorunAni-Bas BialystokBAS	Anioly TorunAni	Bas BialystokBAS
3	Astra Nowa SoIAST-B-BialaBBB	Astra Nowa SoIAST	B-BialaBBB
4	KS RudziniecRUD-Wks Czarni RadomWKS	KS RudziniecRUD	Wks Czarni RadomWKS
5	Avia-SwidnikAVI-McKis Energetyk JaworznoMCK	Avia-SwidnikAVI	McKis Energetyk JaworznoMCK
6	KS Lechia Tomaszow MazowieckiLEC-SiedlceSIE	KS Lechia Tomaszow MazowieckiLEC	SiedlceSIE
7	B-BiaLaBBB-KS RudziniecRUD	B-BiaLaBBB	KS RudziniecRUD
8	Anioly-TorunANI-Bas BialystokBAS	Anioly-TorunANI	Bas BialystokBAS
9	B-BialaBBB-KS Arka Tempo ChelmARK	B-BialaBBB	KS Arka Tempo ChelmARK
10
11
Sheet4

Cell Formulas
Range	Formula
B2:B9	B2	=IF(IF(SUM(--EXACT(CHAR(ROW($65:$90)),LEFT(MID(A2,SEARCH("???-",A2),3))),--EXACT(CHAR(ROW($65:$90)),MID(MID(A2,SEARCH("???-",A2),3),2,1)),--EXACT(CHAR(ROW($65:$90)),RIGHT(MID(A2,SEARCH("???-",A2),3),1)))=3,MID(A2,SEARCH("???-",A2),3),"")="",IFERROR(LEFT(A2,MAX(AGGREGATE(15,6,FIND(CHAR(ROW($65:$90))&"-",A2),1),AGGREGATE(15,6,FIND("-"&CHAR(ROW($65:$90)),A2),1)-1)),LEFT(A2,AGGREGATE(15,6,FIND("-"&CHAR(ROW($65:$90)),A2),1)-1)),LEFT(A2,FIND(IF(SUM(--EXACT(CHAR(ROW($65:$90)),LEFT(MID(A2,SEARCH("???-",A2),3))),--EXACT(CHAR(ROW($65:$90)),MID(MID(A2,SEARCH("???-",A2),3),2,1)),--EXACT(CHAR(ROW($65:$90)),RIGHT(MID(A2,SEARCH("???-",A2),3),1)))=3,MID(A2,SEARCH("???-",A2),3),""),A2)+2))
C2:C9	C2	=IF(IF(SUM(--EXACT(CHAR(ROW($65:$90)),LEFT(MID(A2,SEARCH("???-",A2),3))),--EXACT(CHAR(ROW($65:$90)),MID(MID(A2,SEARCH("???-",A2),3),2,1)),--EXACT(CHAR(ROW($65:$90)),RIGHT(MID(A2,SEARCH("???-",A2),3),1)))=3,MID(A2,SEARCH("???-",A2),3),"")="",IFERROR(MID(A2,MAX(AGGREGATE(15,6,FIND(CHAR(ROW($65:$90))&"-",A2),1),AGGREGATE(15,6,FIND("-"&CHAR(ROW($65:$90)),A2),1)-1)+2,99),MID(A2,AGGREGATE(15,6,FIND("-"&CHAR(ROW($65:$90)),A2),1)+1,99)),MID(A2,FIND(IF(SUM(--EXACT(CHAR(ROW($65:$90)),LEFT(MID(A2,SEARCH("???-",A2),3))),--EXACT(CHAR(ROW($65:$90)),MID(MID(A2,SEARCH("???-",A2),3),2,1)),--EXACT(CHAR(ROW($65:$90)),RIGHT(MID(A2,SEARCH("???-",A2),3),1)))=3,MID(A2,SEARCH("???-",A2),3),""),A2)+4,99))

 

[Abdmujib]

Wow thank you so much sir. Appreciate

 

[Abdmujib]

Another Alternative to the Answer

The Answer works Well and I will be making use of it. but for people that don't like complex formula, you can actually use Power Pivot to split it.
You will have to manipulate your data in the power pivot using

split by Uppercase to Lower or Split by Lower case to Upper, Merge and Split by number of Characters. You will manipulate it until you achieve a desired Results.


The raw data to be imported

Below is the Picture of the Power Query interface and the Applied Steps

The desired result

 

[Sufiyan97]

You're welcome!