abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 573
- Office Version
- 2019
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.
Result
the same thing for second sheet
thanks
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