remove character before middle and after and replace some values & delete specific columns

[abdelfattah]

hi
I need delete specific columns with remove some characters before numbers and letters the middles for instance BS 1200R20 G580 TCF the meddle should be 1200R20 the spaces are one then should delete BS ,G580, TCF and some columns and replace the values in COL O are matched with COL J then should replace COL R for in COL J I know this is not clear I face difficulty to explain clearly if any body see my post is not clear please inform me what I have to explain to understand my post
the original data

(3).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	cost	pur		price	pack	pr	pw	kn	type	origin	qty	NM	BRAND	nm	item
2	178.57	284		1,500.00	pack	740	N/A	BRIDGESTONE	G580	JAPAN	50	inv1	BS 1200R20 G580 TCF	inv1	1		origin	typ
3	272.95	272.95		1,400.00	pack	412.91	N/A	BRIDGESTONE	G580	THAILAND	179	inv2	BS 1200R20 G580 THA	inv2	2		JAPAN	JAP
4	282.14	281		1,370.00	pack	385.57	N/A	BRIDGESTONE	R187	JAPAN	835	inv3	BS 1200R20 R187 TCF	inv3	3		THAILAND	THI
5	257.65	252.35		1,270.00	pack	392.92	N/A	BRIDGESTONE	R187	THAILAND	167	inv4	BS 1200R20 R187 THA	inv4	4		INDONESIA	IND
6	343.54	337.84		1,750.00	pack	409.4	N/A	BRIDGESTONE	G580	JAPAN	164	inv5	BS 1200R24 G580	inv5	5		POLAND	POL
7	283.9	278.1		1,425.00	pack	401.93	N/A	BRIDGESTONE	R187	JAPAN	70	inv6	BS 13R22.5 R187	inv6	6		INDIA	INDI
8	466.08	458.35		0	pack	-100	N/A	BRIDGESTONE	R180	JAPAN	103	inv7	BS 1400R20 TCF R180	inv7	7		CHINA	CHI
9	35.67	35		0	pack	-100	N/A	BRIDGESTONE	R623	JAPAN	100	inv8	BS 155R12C R623	inv8	8
10	34.65	34.65		155	pack	347.33	N/A	BRIDGESTONE	R624	INDONESIA	185	inv9	BS 155R12C R624	inv9	9
11	40.95	40.95		185	pack	351.77	N/A	BRIDGESTONE	R624	INDONESIA	52	inv10	BS 165R13C R624	inv10	10
12	29.4	29.4		125	pack	325.17	N/A	BRIDGESTONE	EP150	INDONESIA	247	inv11	BS 175/65R14 EP150	inv11	11
13	27.81	27.81		125	pack	349.48	N/A	BRIDGESTONE	EP150	THAILAND	42	inv12	BS 175/70R13 EP150	inv12	12
14	30.19	30.45		133	pack	340.54	N/A	BRIDGESTONE	EP150	THAILAND	239	inv13	BS 175/70R14 EP150	inv13	13
15	53.82	120		135	pack	150.84	N/A	BRIDGESTONE	RL	INDONESIA	755	inv14	FS 1600-25 RL	inv14	14
16	31.42	31.42		135	pack	329.73	N/A	BRIDGESTONE	VRLS	THAILAND	331	inv15	OL 2400R35 ** VRLS	inv15	15
17	35.02	35.02		0	pack	-100	N/A	BRIDGESTONE	**VUT	JAPAN	60	inv16	BS 20.5R25 ** VUT	inv16	16
18
sheet1

expected result

(3).xlsx
	A	B	C	D	E
1	item	BRAND	type	origin	qty
2	1	1200R20	G580	JAP	50
3	2	1200R20	G580	THI	179
4	3	1200R20	R187	JAP	835
5	4	1200R20	R187	THI	167
6	5	1200R24	G580	JAP	164
7	6	13 R22.5	R187	JAP	70
8	7	1400R20	R180	JAP	103
9	8	155 R12C	R623	JAP	100
10	9	155 R12C	R624	INDO	185
11	10	165 R13C	R624	INDO	52
12	11	175/65 R14	EP150	INDO	247
13	12	175/70 R13	EP150	THI	42
14	13	175/70 R14	EP150	THI	239
15	14	1600-25	RL	INDO	755
16	15	2400R35	VRLS	THI	331
17	16	20.5R25	**VUT	JAP	60
result

about the stars showing are short word JAPAN but i no know why show stars

 

[Snakehips]

@abdelfattah

Does this formula approach help?

MRXLMAY21.xlsm
	A	B	C	D	E
1	item	BRAND	type	origin	qty
2	1	1200R20	G580	***	50
3	2	1200R20	G580	THI	179
4	3	1200R20	R187	***	835
5	4	1200R20	R187	THI	167
6	5	1200R24	G580	***	164
7	6	13R22.5	R187	***	70
8	7	1400R20	R180	***	103
9	8	155R12C	R623	***	100
10	9	155R12C	R624	IND	185
11	10	165R13C	R624	IND	52
12	11	175/65R14	EP150	IND	247
13	12	175/70R13	EP150	THI	42
14	13	175/70R14	EP150	THI	239
15	14	1600-25	RL	IND	755
16	15	2400R35	VRLS	THI	331
17	16	20.5R25	**VUT	***	60
18
Result

Cell Formulas
Range	Formula
A2:A18	A2	=Sheet1!P2
B2:B18	B2	=TRIM(MID(SUBSTITUTE(Sheet1!N2," ",REPT(" ",99)),99,99))
C2:C18,E2:E18	C2	=Sheet1!J2
D2:D18	D2	=IFERROR(INDEX(Sheet1!$S$2:$S$8,MATCH(Sheet1!K2,Sheet1!$R$2:$R$8,0),),"")

 

[abdelfattah]

thanks I think made mistake about row 16 like row 17 when show the result