How to split inconsistent text into 4columns

[mart_mrexcel]

I have an inconsistent Joined text and i want to split them into 4 columns (column C,D,E,F) as per the table below (expected output). The sequence of the joined text are all the same e.g. all currency are placed in the left and the grade is placed at the end and so on. Appreciate if someone can assist me to split it using TEXTSPLIT() OR LEFT, MID , RIGHT functions.

	A	B	C	D	E	F
1	Joined Text		Currency	Company	Group	Grade
2	EURPLNG (NIC) _Quezon (1000)NationalsG8		EUR	PLNG (NIC) _Quezon (1000)	Nationals	G8
3	USDPatio-Company (4115)Eastern ExpatriatesG6		USD	Patio-Company (4115)	Eastern Expatriates	G6
4	USDTank (NIC) _HQ H.O. Eastern ExpatriatesG12		USD	Tank (NIC) _HQ H.O.	Eastern Expatriates	G12
5	EURPLNG (NIC) _Quezon (1000)Non Nationals		EUR	PLNG (NIC) _Quezon (1000)	Non Nationals
6	KRWG10		KRW			G10
7	USDKiosk/FT (8300) WesternG9		USD	Kiosk/FT (8300)	Western	G9
8	SAR NationalsG18		SAR		Nationals	G18

 

[hagia_sofia]

Could you please list all "groups" here?

 

[mart_mrexcel]

Could you please list all "groups" here?

1.) Nationals
2.) Non Nationals
3.) Eastern Expatriates
3.) Western

 

[felixstraube]

Is Currency always in the text in column A?

 

[felixstraube]

Assuming that Currency is always present, how about?:

SplitInconsistentText.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Joined Text		Currency	Company	Group	Grade		Currency	Company	Group	Grade		Groups
2	EURPLNG (NIC) _Quezon (1000)NationalsG8		EUR	PLNG (NIC) _Quezon (1000)	Nationals	G8		EUR	PLNG (NIC) _Quezon (1000)	Nationals	G8		Nationals
3	USDPatio-Company (4115)Eastern ExpatriatesG6		USD	Patio-Company (4115)	Eastern Expatriates	G6		USD	Patio-Company (4115)	Eastern Expatriates	G6		Non Nationals
4	USDTank (NIC) _HQ H.O. Eastern ExpatriatesG12		USD	Tank (NIC) _HQ H.O.	Eastern Expatriates	G12		USD	Tank (NIC) _HQ H.O.	Eastern Expatriates	G12		Eastern Expatriates
5	EURPLNG (NIC) _Quezon (1000)Non Nationals		EUR	PLNG (NIC) _Quezon (1000)	Non Nationals			EUR	PLNG (NIC) _Quezon (1000)	Non Nationals			Western
6	KRWG10		KRW			G10		KRW			G10
7	USDKiosk/FT (8300) WesternG9		USD	Kiosk/FT (8300)	Western	G9		USD	Kiosk/FT (8300)	Western	G9
8	SAR NationalsG18		SAR		Nationals	G18		SAR		Nationals	G18
9	EURPLNG (NIC) _Quezon (1000)NationalsG8		EUR	PLNG (NIC) _Quezon (1000)	Nationals	G8		EUR	PLNG (NIC) _Quezon (1000)	Nationals	G8
10	USDEastern ExpatriatesG6		USD		Eastern Expatriates	G6		USD		Eastern Expatriates	G6
11	USDTank (NIC) _HQ H.O.G12		USD	Tank (NIC) _HQ H.O.		G12		USD	Tank (NIC) _HQ H.O.		G12
12	EURPLNG (NIC) _Quezon (1000)Nationals		EUR	PLNG (NIC) _Quezon (1000)	Nationals			EUR	PLNG (NIC) _Quezon (1000)	Nationals
13	KRWG10		KRW			G10		KRW			G10
14	USDKiosk/FT (8300)		USD	Kiosk/FT (8300)				USD	Kiosk/FT (8300)
15	SARNationals		SAR		Nationals			SAR		Nationals
16	EUR		EUR					EUR
Sheet1

Cell Formulas
Range	Formula
H2:H16	H2	=LEFT(A2, 3)
I2:I16	I2	=TEXTBEFORE(TEXTAFTER(A2, H2)&"|@", J2&K2&"|@")
J2:J16	J2	=LET(d, IFERROR(INDEX($M$2:$M$5, MAX(IFERROR(SEARCH($M$2:$M$5, A2)>0,0)*SEQUENCE(4))),""), IF(ROWS(d)>1, "", d))
K2:K16	K2	=LET(d,RIGHT(A2,3), IF(IFERROR(FIND("G",d),0), "G"&TEXTAFTER(d, "G"),"") )
A9:A16	A9	=CONCAT(C9:F9)

 

[Fuji]

Only if REGEXTRACT function can be used in your Excel. (Available in Microsoft 365 Insiders)

Book1.xlsm
	A	B	C	D	E	F
1	Joined Text		Currency	Company	Group	Grade
2	EURPLNG (NIC) _Quezon (1000)NationalsG8		EUR	PLNG (NIC) _Quezon (1000)	Nationals	G8
3	USDPatio-Company (4115)Eastern ExpatriatesG6		USD	Patio-Company (4115)	Eastern Expatriates	G6
4	USDTank (NIC) _HQ H.O. Eastern ExpatriatesG12		USD	Tank (NIC) _HQ H.O.	Eastern Expatriates	G12
5	EURPLNG (NIC) _Quezon (1000)Non Nationals		EUR	PLNG (NIC) _Quezon (1000)	Non Nationals
6	KRWG10		KRW			G10
7	USDKiosk/FT (8300) WesternG9		USD	Kiosk/FT (8300)	Western	G9
8	SAR NationalsG18		SAR		Nationals	G18
Sheet1

Cell Formulas
Range	Formula
C2:F8	C2	=IFERROR(REGEXEXTRACT(A2,"^([A-Z]{3})(.*?)([A-Z][a-z]+(?: [A-Z][a-z]+)*)*(G\d+)*$",2),"")
Dynamic array formulas.

 

[Cubist]

-Delete-