Delete first and last item for each cell in the same column for two sheets

[abdo meghari]

Hi guys,
I would macro to delete the first and last item for each cell in column J for two sheets.
the first item always contains space after it and last item always contains space before it . also will contain many letters for first and last item.

CH.xlsm
	I	J	K	L	M	N
1	CODE	BRAND	UNIT PRICE1	UNIT PRICE2	UNIT PRICE3	UNIT PRICE4
2	1241	BS 1200R20 G580 JAP	2035	0	0	0
3	1244	BS 1200R20 R187 JAP	2000	0	0	0
4	1269	BS 1200R24 G580 JAP	1900	0	0	0
5	1556	BS 185R14C R660 TR	423	0	0	0
6	1221	BS 205/70R15C R623 THI	405	0	0	0
7	1547	BS 205R16C D840 THI	625	0	0	0
8	1502	BS 215/65R16C R611 THI	600	583	0	0
9	1227	BS 215/70R15C R623 THI	425	544	0	0
10	1503	BS 225/85R16C R202 JAP	975	0	0	0
11	1310	BS 225/95R16C D618 JAP	515	695	715	700
12	1402	BS 245/70R16 D697 JAP	590	0	0	0
13	1346	DUN 255/70R15C D840 JAPAN	505	635	0	0
14	1326	BS 265/60R18 D840 JAP	721	0	0	0
15	1534	BS 265/65R17 D693 THAILAND	745	0	0	0
16	1391	BS 265/65R17 D840 JAP	535	690	0	0
17	1411	MARS 275/55R20 ALENZA1 KORIA	725	942	0	0
18	1190	BS 285/50R20 DSPORT JAP	936	705	0	0
19	1315	BS 315/80R22.5 G580 JAP	2470	0	0	0
20	1257	BS 315/80R22.5 R184 JAP	2015	0	0	0
21	1401	BS 650R16 R230 JAP	570	0	0	0
22	1305	HILO 700R16 R230 JAP	762	0	0	0
23	1306	BS 750R16 R230 JAP	715	940	0	0
24	1307	BS 750R16 VSJ JAP	910	0	0	0
25	1284	GC 1200R20 AZ0026 CHINA	895	1125	0	0
26	1285	GC 1200R20 AZ0183 CHI	925	1225	1205	0
27	1292	GC 1200R24 AZ166 CHI	935	0	0	0
28	1385	GC 315/80R22.5 AT161 CHI	735	955	0	0
29	1287	GC 315/80R22.5 AZ126 CHI	735	0	0	0
30	1294	GC 315/80R22.5 AZ188 CHI	745	965	0	0
31	1492	GC 385/65R22.5 AT131 CHI	1275	0	0	0
32	1528	TH 185/65R14 H-93 CHI	134	0	0	0
33	1493	WL 195/65R15 Z-108 CHI	173	0	0	0
34	1486	WL 205/55R16 Z-108 CHI	185	0	0	0
PURCHASING

CH.xlsm
	I	J	K	L	M
1	CODE	BRAND	QTY	UNIT PRICE	TOTAL
2	1306	BS 750R16 R230 JAP	10	775	7750
3	1306	BS 750R16 R230 JAP	4	780	3120
4	1305	BS 700R16 R230 JAP	2	770	1540
SELLING

Cell Formulas
Range	Formula
M2:M4	M2	=K2*L2

Result

CH.xlsm
	I	J	K	L	M	N
1	CODE	BRAND	UNIT PRICE1	UNIT PRICE2	UNIT PRICE3	UNIT PRICE4
2	1241	1200R20 G580	2035	0	0	0
3	1244	1200R20 R187	2000	0	0	0
4	1269	1200R24 G580	1900	0	0	0
5	1556	185R14C R660	423	0	0	0
6	1221	205/70R15C R623	405	0	0	0
7	1547	205R16C D840	625	0	0	0
8	1502	215/65R16C R611	600	583	0	0
9	1227	215/70R15C R623	425	544	0	0
10	1503	225/85R16C R202	975	0	0	0
11	1310	225/95R16C D618	515	695	715	700
12	1402	245/70R16 D697	590	0	0	0
13	1346	255/70R15C D840	505	635	0	0
14	1326	265/60R18 D840	721	0	0	0
15	1534	265/65R17 D693	745	0	0	0
16	1391	265/65R17 D840	535	690	0	0
17	1411	275/55R20 ALENZA1	725	942	0	0
18	1190	285/50R20 DSPORT	936	705	0	0
19	1315	315/80R22.5 G580	2470	0	0	0
20	1257	315/80R22.5 R184	2015	0	0	0
21	1401	650R16 R230	570	0	0	0
22	1305	700R16 R230	762	0	0	0
23	1306	750R16 R230	715	940	0	0
24	1307	750R16 VSJ	910	0	0	0
25	1284	1200R20 AZ0026	895	1125	0	0
26	1285	1200R20 AZ0183	925	1225	1205	0
27	1292	1200R24 AZ166	935	0	0	0
28	1385	315/80R22.5 AT161	735	955	0	0
29	1287	315/80R22.5 AZ126	735	0	0	0
30	1294	315/80R22.5 AZ188	745	965	0	0
31	1492	385/65R22.5 AT131	1275	0	0	0
32	1528	185/65R14 H-93	134	0	0	0
33	1493	195/65R15 Z-108	173	0	0	0
34	1486	205/55R16 Z-108	185	0	0	0
PURCHASING

the same thing for second sheet
thanks

 

[abdo meghari]

works well .
thanks again for your help.

 

[Peter_SSs]

Cheers. You're welcome.

 

[Peter_SSs]

An option with a slightly shorter 'Evaluate' line would be

Sub Second_Brand_Only_v4()
  Dim aSheets As Variant, Sh As Variant
  
  aSheets = Split("PURCHASING|SELLING", "|")
  For Each Sh In aSheets
    With Sheets(Sh).Range("J2", Sheets(Sh).Range("J" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("trim(mid(substitute(trim(#&"" ""&#),"" "",REPT("" "",100)),100,100))", "#", .Address(External:=True)))
    End With
  Next Sh
End Sub

 

[abdo meghari]

An option with a slightly shorter 'Evaluate' line would be

this is what I got.

DLETEITEM.xlsm
	I	J	K	L	M	N
1	CODE	BRAND	UNIT PRICE1	UNIT PRICE2	UNIT PRICE3	UNIT PRICE4
2	1241	1200R20	2035	0	0	0
3	1244	1200R20	2000	0	0	0
4	1269	1200R20	1900	0	0	0
5	1556	1200R20	423	0	0	0
6	1221	1200R20	405	0	0	0
7	1547	1200R20	625	0	0	0
8	1502	1200R20	600	583	0	0
9	1227	1200R20	425	544	0	0
10	1503	1200R20	975	0	0	0
11	1310	1200R20	515	695	715	700
12	1402	1200R20	590	0	0	0
13	1346	1200R20	505	635	0	0
14	1326	1200R20	721	0	0	0
15	1534	1200R20	745	0	0	0
16	1391	1200R20	535	690	0	0
17	1411	1200R20	725	942	0	0
18	1190	1200R20	936	705	0	0
19	1315	1200R20	2470	0	0	0
20	1257	1200R20	2015	0	0	0
21	1401	1200R20	570	0	0	0
22	1305	1200R20	762	0	0	0
23	1306	1200R20	715	940	0	0
24	1307	1200R20	910	0	0	0
25	1284	1200R20	895	1125	0	0
26	1285	1200R20	925	1225	1205	0
27	1292	1200R20	935	0	0	0
28	1385	1200R20	735	955	0	0
29	1287	1200R20	735	0	0	0
30	1294	1200R20	745	965	0	0
31	1492	1200R20	1275	0	0	0
32	1528	1200R20	134	0	0	0
33	1493	1200R20	173	0	0	0
34	1486	1200R20	185	0	0	0
PURCHASING

DLETEITEM.xlsm
	I	J	K	L	M	N
1	CODE	BRAND	QTY	UNIT PRICE	TOTAL	UNIT PRICE4
2	1306	750R16	10	775	7750	0
3	1306	750R16	4	780	3120	0
4	1305	750R16	2	770	1540	0
SELLING

Cell Formulas
Range	Formula
M2:M4	M2	=K2*L2

 

[Peter_SSs]

this is what I got.

Don't know how you got that. For me ..

Before:

abdo meghari_4.xlsm
	J
1	BRAND
2	BS 1200R20 G580 JAP
3	BS 1200R20 R187 JAP
4	BS 1200R24 G580 JAP
5	BS 185R14C R660 TR
6	BS 205/70R15C R623 THI
7	BS 205R16C D840 THI
8	BS 215/65R16C R611 THI
9	BS 215/70R15C R623 THI
10	BS 225/85R16C R202 JAP
11	BS 225/95R16C D618 JAP
12	BS 245/70R16 D697 JAP
13	DUN 255/70R15C D840 JAPAN
14	BS 265/60R18 D840 JAP
15	BS 265/65R17 D693 THAILAND
16	BS 265/65R17 D840 JAP
17	MARS 275/55R20 ALENZA1 KORIA
18	BS 285/50R20 DSPORT JAP
19	BS 315/80R22.5 G580 JAP
20	BS 315/80R22.5 R184 JAP
21	BS 650R16 R230 JAP
22	HILO 700R16 R230 JAP
23	BS 750R16 R230 JAP
24	BS 750R16 VSJ JAP
25	GC 1200R20 AZ0026 CHINA
26	GC 1200R20 AZ0183 CHI
27	GC 1200R24 AZ166 CHI
28	GC 315/80R22.5 AT161 CHI
29	GC 315/80R22.5 AZ126 CHI
30	GC 315/80R22.5 AZ188 CHI
31	GC 385/65R22.5 AT131 CHI
32	TH 185/65R14 H-93 CHI
33	WL 195/65R15 Z-108 CHI
34	WL 205/55R16 Z-108 CHI
PURCHASING

Code:

Sub Second_Brand_Only_v4()
  Dim aSheets As Variant, Sh As Variant
  
  aSheets = Split("PURCHASING|SELLING", "|")
  For Each Sh In aSheets
    With Sheets(Sh).Range("J2", Sheets(Sh).Range("J" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("trim(mid(substitute(trim(#&"" ""&#),"" "",REPT("" "",100)),100,100))", "#", .Address(External:=True)))
    End With
  Next Sh
End Sub

After:

abdo meghari_4.xlsm
	J
1	BRAND
2	1200R20
3	1200R20
4	1200R24
5	185R14C
6	205/70R15C
7	205R16C
8	215/65R16C
9	215/70R15C
10	225/85R16C
11	225/95R16C
12	245/70R16
13	255/70R15C
14	265/60R18
15	265/65R17
16	265/65R17
17	275/55R20
18	285/50R20
19	315/80R22.5
20	315/80R22.5
21	650R16
22	700R16
23	750R16
24	750R16
25	1200R20
26	1200R20
27	1200R24
28	315/80R22.5
29	315/80R22.5
30	315/80R22.5
31	385/65R22.5
32	185/65R14
33	195/65R15
34	205/55R16
PURCHASING

Ran the code again and the column remained as above.

 

[abdo meghari]

I have no idea !
here is attachment
Dropbox.

 

[Peter_SSs]

I have no idea !
here is attachment
Dropbox.

Worked for me. It might relate to your Excel version - which I don't have to test with.

Perhaps we could revert to a line-by-line 'Split' method like Alex was using in the early part of the thread.

Sub Second_Brand_Only_v4()
  Dim aSheets As Variant, Sh As Variant, a As Variant, Bits As Variant
  Dim i As Long
 
  aSheets = Split("PURCHASING|SELLING", "|")
  For Each Sh In aSheets
    With Sheets(Sh).Range("J2", Sheets(Sh).Range("J" & Rows.Count).End(xlUp))
      a = .Value
      For i = 1 To UBound(a)
        Bits = Split(Application.Trim(a(i, 1)))
        If UBound(Bits) > 0 Then a(i, 1) = Bits(1)
      Next i
      .Value = a
    End With
  Next Sh
End Sub

 

[abdo meghari]

finally works.
thanks again.

 

[Peter_SSs]

Good news!