Text after sign need to separate

[DIPASGL]

Hi all

I would like to separate text and number in a cell after hyphen sign, then multiply with quantity.

On left side my description has got 1000l or 20l or .45kg etc sometimes and I want the result as per last column 3000(1000*3) so on - Thanks

DIESEL MAX 15W/40 CK4 - 1000L	3		3000
GEAR OIL 80W/90 - 20L	32		640
HYDRAULIC 32 - 20L	16		320
HYDRAULIC 46 - 20L	32		640
HYDRAULIC 46 - 1000L	2		2000
HYDRAULIC 68 - 1000L	10		10000
HYDRAULIC H 68 - 1000L	1		1000
GREASE LX2 - 0.45KG	640		288
GREASE LXMO2 - 180KG	1		180

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2	DIESEL MAX 15W/40 CK4 - 1000L	3	3000
3	GEAR OIL 80W/90 - 20L	32	640
4	HYDRAULIC 32 - 20L	16	320
5	HYDRAULIC 46 - 20L	32	640
6	HYDRAULIC 46 - 1000L	2	2000
7	HYDRAULIC 68 - 1000L	10	10000
8	HYDRAULIC H 68 - 1000L	1	1000
9	GREASE LX2 - 0.45KG	640	288
10	GREASE LXMO2 - 180KG	1	180
Master

Cell Formulas
Range	Formula
C2:C10	C2	=SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("-",A2)+1,100),"L",""),"KG","")*B2

 

[Peter_SSs]

Based on the sample data in your similar thread it seems that you may have quite a variety of units including in upper/lower case and full/abbreviated versions. If that is still the case then perhaps basically my formula over there ..

BTW, it would be helpful if you followed up your questions with feed-back to helpers so that they know if their suggestions are useful to you and if not, why not.

23 06 14.xlsm
	A	B	C
1
2	DIESEL MAX 15W/40 CK4 - 1000L	3	3000
3	GEAR OIL 80W/90 - 20L	32	640
4	HYDRAULIC 32 - 20L	16	320
5	HYDRAULIC 46 - 20L	32	640
6	HYDRAULIC 46 - 1000L	2	2000
7	HYDRAULIC 68 - 1000L	10	10000
8	HYDRAULIC H 68 - 1000L	1	1000
9	GREASE LX2 - 0.45KG	640	288
10	GREASE LXMO2 - 180KG	1	180
DIPASGL

Cell Formulas
Range	Formula
C2:C10	C2	=REPLACE(LEFT(A2,LOOKUP(1,-MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,99)),1),ROW(INDEX(A:A,1):INDEX(A:A,99)))),1,FIND("-",A2),"")*B2

 

[DIPASGL]

Based on the sample data in your similar thread it seems that you may have quite a variety of units including in upper/lower case and full/abbreviated versions. If that is still the case then perhaps basically my formula over there ..

BTW, it would be helpful if you followed up your questions with feed-back to helpers so that they know if their suggestions are useful to you and if not, why not.

23 06 14.xlsm
	A	B	C
1
2	DIESEL MAX 15W/40 CK4 - 1000L	3	3000
3	GEAR OIL 80W/90 - 20L	32	640
4	HYDRAULIC 32 - 20L	16	320
5	HYDRAULIC 46 - 20L	32	640
6	HYDRAULIC 46 - 1000L	2	2000
7	HYDRAULIC 68 - 1000L	10	10000
8	HYDRAULIC H 68 - 1000L	1	1000
9	GREASE LX2 - 0.45KG	640	288
10	GREASE LXMO2 - 180KG	1	180
DIPASGL

Cell Formulas
Range	Formula
C2:C10	C2	=REPLACE(LEFT(A2,LOOKUP(1,-MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,99)),1),ROW(INDEX(A:A,1):INDEX(A:A,99)))),1,FIND("-",A2),"")*B2

Thanks Peter, its working fine except row 11 that I missed to mention before

MT BRAKE CLEANER - 400G

where my description has got g which means grams and when I want to multiply 400g I want my formula to pick .45 multiply with qty. The formula you gave is good except for g above row 11. Any idea how can I fix this. Thanks

 

[DIPASGL]

How about

Fluff.xlsm
	A	B	C
1
2	DIESEL MAX 15W/40 CK4 - 1000L	3	3000
3	GEAR OIL 80W/90 - 20L	32	640
4	HYDRAULIC 32 - 20L	16	320
5	HYDRAULIC 46 - 20L	32	640
6	HYDRAULIC 46 - 1000L	2	2000
7	HYDRAULIC 68 - 1000L	10	10000
8	HYDRAULIC H 68 - 1000L	1	1000
9	GREASE LX2 - 0.45KG	640	288
10	GREASE LXMO2 - 180KG	1	180
Master

Cell Formulas
Range	Formula
C2:C10	C2	=SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("-",A2)+1,100),"L",""),"KG","")*B2

Hi Fluff,
Thanks for help.
Your formula works fine except when my description has got g means gram like below, its giving error

#VALUE!

My figures as below

MT BRAKE CLEANER - 400G

96 qty

I want to achieve .45 multiply 96 as I want your formula to put decimal in case of gram and then multiply. Thanks

 

[Fluff]

What other conditions are there that you haven't mentioned?

 

[Peter_SSs]

Also, what is the logic for 400G to become 0.45 and not 0.4?

 

[DIPASGL]

What other conditions are there that you haven't mentioned? No other conditions. I have data like below

Description	Qty. Received (Base)
GEAR SYN 75W/140 - 20L	8
GEAR SYN - 75W/90 -200g	32
GEAR SYN 75W/90 -5L	64
PREMIUM SN -5W/30 - 20L	48
PREMIUM SN 5W30 - 1000L	2

I want to multiply qty with all number in description column that comes after hyphen. Thanks

 

[DIPASGL]

Also, what is the logic for 400G to become 0.45 and not 0.4?

Sorry Fluff I just wrote by mistake , it should be .400 for 400g

 

[Peter_SSs]

Sorry Fluff I just wrote by mistake

Did you mean Peter?

No other conditions.

Looks like there are to me -

• Now you have data with multiple "-" symbols in the one line which we have not seen before.
  
• You also now have an example where there is no space character after the dash, unlike all the earlier samples.