Add elements for adjacent cells by insert column based on multiple characters

[abdo meghari]

Hello
I'm not sure if could be by macro , but what I want if the item in column B contains R & number like this 700R16 or 195R15C or 29.5R25** VSDL as bold after R letter then should add element (TIRES) in adjacent cell for column C and if the item in column B contains / then the element in column C should be TIRES and if the item in column B contains × & - then the element in column C should be TIRES like this 28×9-15 LO as bold, if the item in column B contains number & letter A then the element in column C should be BATTERY like this 70A R OR 74A L or 100A as bold, if if the item in column B contains number & × & number & L then the element in column C should be OIL like this PP MEG 24KG 4×5L or CYU MEG 4×4L as bold .

[

CC1.xlsm
	A	B	C	D	E
1	ITEM	BATCH	QTY	PRICE	TOTAL
2	1	235/60R18	4.00	LYD 425.00	LYD 1,700.00
3	2	750R16 L50	2.00	LYD 400.00	LYD 800.00
4	3	700R16	7.00	LYD 800.00	LYD 5,600.00
5	4	215/65R16	4.00	LYD 355.00	LYD 1,420.00
6	5	265/70R16	5.00	LYD 550.00	LYD 2,750.00
7	6	29.5R25** VSDL	24.00	LYD 385.00	LYD 9,240.00
8	7	265/60R18	4.00	LYD 540.00	LYD 2,160.00
9	8	225/75R15	1.00	LYD 340.00	LYD 340.00
10	9	275/50R21	4.00	LYD 780.00	LYD 3,120.00
11	10	265/65R17	5.00	LYD 700.00	LYD 3,500.00
12	11	195R15C	5.00	LYD 600.00	LYD 3,000.00
13	12	18.4/15-30-20	4.00	LYD 310.00	LYD 1,240.00
14	13	28×9-15 LO	2.00	LYD 580.00	LYD 1,160.00
15	14	70A R	10.00	LYD 405.00	LYD 4,050.00
16	15	60A	10.00	LYD 335.00	LYD 3,350.00
17	16	90A	6.00	LYD 470.00	LYD 2,820.00
18	17	74A L	4.00	LYD 310.00	LYD 1,240.00
19	18	100A	3.00	LYD 490.00	LYD 1,470.00
20	19	150A	4.00	LYD 650.00	LYD 2,600.00
21	20	120A	2.00	LYD 565.00	LYD 1,130.00
22	21	23.5R25 18.4-30	1.00	LYD 385.00	LYD 385.00
23	22	PP MEG 24KG 4×5L	21.00	LYD 40.00	LYD 840.00
24	23	CYU MEG 4×4L	24.00	LYD 50.00	LYD 1,200.00
25	TOTAL				LYD 55,115.00
combined

Cell Formulas
Range	Formula
E2:E24	E2	=C2*D2
E25	E25	=SUM(E2:E24)

result

CC1.xlsm
	A	B	C	D	E	F
1	ITEM	BATCH	GROUP	QTY	PRICE	TOTAL
2	1	235/60R18	TIRES	4.00	LYD 425.00	LYD 1,700.00
3	2	750R16 L50	TIRES	2.00	LYD 400.00	LYD 800.00
4	3	700R16	TIRES	7.00	LYD 800.00	LYD 5,600.00
5	4	215/65R16	TIRES	4.00	LYD 355.00	LYD 1,420.00
6	5	265/70R16	TIRES	5.00	LYD 550.00	LYD 2,750.00
7	6	29.5R25** VSDL	TIRES	24.00	LYD 385.00	LYD 9,240.00
8	7	265/60R18	TIRES	4.00	LYD 540.00	LYD 2,160.00
9	8	225/75R15	TIRES	1.00	LYD 340.00	LYD 340.00
10	9	275/50R21	TIRES	4.00	LYD 780.00	LYD 3,120.00
11	10	265/65R17	TIRES	5.00	LYD 700.00	LYD 3,500.00
12	11	195R15C	TIRES	5.00	LYD 600.00	LYD 3,000.00
13	12	18.4/15-30-20	TIRES	4.00	LYD 310.00	LYD 1,240.00
14	13	28×9-15 LO	TIRES	2.00	LYD 580.00	LYD 1,160.00
15	14	70A R	BATTERY	10.00	LYD 405.00	LYD 4,050.00
16	15	60A	BATTERY	10.00	LYD 335.00	LYD 3,350.00
17	16	90A	BATTERY	6.00	LYD 470.00	LYD 2,820.00
18	17	74A L	BATTERY	4.00	LYD 310.00	LYD 1,240.00
19	18	100A	BATTERY	3.00	LYD 490.00	LYD 1,470.00
20	19	150A	BATTERY	4.00	LYD 650.00	LYD 2,600.00
21	20	120A	BATTERY	2.00	LYD 565.00	LYD 1,130.00
22	21	23.5R25 18.4-30	TIRES	1.00	LYD 385.00	LYD 385.00
23	22	PP MEG 24KG 4×5L	OIL	21.00	LYD 40.00	LYD 840.00
24	23	CYU MEG 4×4L	OIL	24.00	LYD 50.00	LYD 1,200.00
25	TOTAL					LYD 55,115.00
EXPECTED

Cell Formulas
Range	Formula
F2:F24	F2	=D2*E2
F25	F25	=SUM(F2:F24)

should be clear data in second sheet from row 2 before bring data . if it's impossible by macro or any suggestion to make easy things I accept that or formula
thanks

 

[SunnyAlv]

That formula will still error in 2019 as only 2012 & 365 have the LET function

Yes Mr @Fluff. I have also created a private chat with him for further checking

 

[Fluff]

I have also created a private chat with him for further checking

Please do NOT use the message system, all communication must remain on the board for all to see.

 

[SunnyAlv]

Please do NOT use the message system, all communication must remain on the board for all to see.

hm sory Mr @Fluff I just asked what formulas can be done in excel 2019, I will give the answer in this thread

 

[felixstraube]

How about this monster of a formula?:

=IF(OR(ISNUMBER(VALUE(MID(C2;FIND("R";C2)+1;2)));ISNUMBER(FIND("/";C2));ISNUMBER(MATCH("*×*-*";C2;0)));"TIRES";IF(ISNUMBER(VALUE(LEFT(C2;FIND("A";C2)-1)));"BATTERIES";IF(ISNUMBER(VALUE(MID(C2;FIND("×";C2)+1;FIND("L";C2)-FIND("×";C2)-1)))*ISNUMBER(VALUE(MID(C2;FIND(" ";C2;FIND("×";C2)-2)+1;FIND("×";C2)-FIND(" ";C2;FIND("×";C2)-2)-1)));"OIL";"")))

 

[abdo meghari]

@felixstraube
thank you but shows messgae about Circular reference and shows zero !

 

[felixstraube]

How about now?

Libro1
	A	B	C	D	E	F
1	ITEM	BATCH	GROUP	QTY	PRICE	TOTAL
2	1	235/60R18	TIRES	4	425	1700
3	2	750R16 L50	TIRES	2	400	800
4	3	700R16	TIRES	7	800	5600
5	4	215/65R16	TIRES	4	355	1420
6	5	265/70R16	TIRES	5	550	2750
7	6	29.5R25** VSDL	TIRES	24	385	9240
8	7	265/60R18	TIRES	4	540	2160
9	8	225/75R15	TIRES	1	340	340
10	9	275/50R21	TIRES	4	780	3120
11	10	265/65R17	TIRES	5	700	3500
12	11	195R15C	TIRES	5	600	3000
13	12	18.4/15-30-20	TIRES	4	310	1240
14	13	28×9-15 LO	TIRES	2	580	1160
15	14	70A R	BATTERIES	10	405	4050
16	15	60A	BATTERIES	10	335	3350
17	16	90A	BATTERIES	6	470	2820
18	17	74A L	BATTERIES	4	310	1240
19	18	100A	BATTERIES	3	490	1470
20	19	150A	BATTERIES	4	650	2600
21	20	120A	BATTERIES	2	565	1130
22	21	23.5R25 18.4-30	TIRES	1	385	385
23	22	PP MEG 24KG 4×5L	OIL	21	40	840
24	23	CYU MEG 4×4L	OIL	24	50	1200
25	TOTAL					55115
Hoja1

Cell Formulas
Range	Formula
C2:C24	C2	=IF(OR(ISNUMBER(VALUE(MID(B2,FIND("R",B2) + 1,2))),ISNUMBER(FIND("/",B2)),ISNUMBER(MATCH("*×*-*",B2,0))),"TIRES",IF(ISNUMBER(VALUE(LEFT(B2,FIND("A",B2) - 1))),"BATTERIES",IF(ISNUMBER(VALUE(MID(B2,FIND("×",B2) + 1,FIND("L",B2) - FIND("×",B2) - 1))) * ISNUMBER(VALUE(MID(B2,FIND(" ",B2,FIND("×",B2) - 2) + 1,FIND("×",B2) - FIND(" ",B2,FIND("×",B2) - 2) - 1))),"OIL","")))
F2:F24	F2	=D2*E2
F25	F25	=SUM(F2:F24)

 

[abdo meghari]

@felixstraube
awesome !
thank you so much .

 

[felixstraube]

You are welcome. Thanks for the feedback.