Cell got number with decimal and text too - How to separate

[DIPASGL]

I have got column like below that has got L (liter), kg(kilogram) and so on. If I want to separate after highphyne - sign but also just want number including decimal (not with kg or g etc). How can I do . I want to achieve 250 , 2.6, .45 , .250 .Below is the problem

GREASE - 250L

GREASE STRO 30 - 2.6KG

GREASE - .45KG

GREASE - 250G

 

[Sufiyan97]

Do you still use Excel 2003? I don't know how to work with it but below are couple of options if you have updated to new versions

Book3
	A	B	C	D
1
2	GREASE - 250L	250	250
3	GREASE STRO 30 - 2.6KG	2.6	2.6
4	GREASE - .45KG	.45	.45
5	GREASE - 250G	250	250
6
7
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=LEFT(REPLACE(A2,1,SEARCH(" - ",A2)+2,""),AGGREGATE(15,6,SEARCH(CHAR(ROW($65:$90)),REPLACE(A2,1,SEARCH(" - ",A2)+2,"")),1)-1)
C2:C5	C2	=TEXTBEFORE(TEXTAFTER(A2," - "),(CHAR(ROW($65:$90))))

 

[DIPASGL]

=TEXTBEFORE(TEXTAFTER(A2," - "),(CHAR(ROW($65:$90))))

Thanks dear for reply but the above formulas not working . What about if I do not want to separate text with no , Can I use substitute formula and how?

for example, my rows contain different alphanumeric values. Lets say I have 2 apples, 3 oranges, 9 bananas in each row. Is there a way to automatically multiply them without having to manually enter the SUBSTITUTE formula?

 

[KRice]

Here is a variation of a solution offered by @Sufiyan97 except the newer functions have been replaced with those available in older versions of Excel:

Book1
	A	B
1
2	GREASE - 250G	250
3	GREASE STRO 30 - 2.6KG	2.6
4	GREASE - .45KG	0.45
5	GREASE - 250G	250
try

Cell Formulas
Range	Formula
B2:B5	B2	=0+LEFT(RIGHT(A2,LEN(A2)-FIND("- ",A2)-1),AGGREGATE(15,6,SEARCH(CHAR(ROW($65:$90)),RIGHT(A2,LEN(A2)-FIND("- ",A2)-1)),1)-1)

 

[DIPASGL]

Also what is (CHAR(ROW($65:$90)))) in your formula shows?

=TEXTBEFORE(TEXTAFTER(A2," - "),(CHAR(ROW($65:$90))))

 

[Fluff]

Are you still using xl 2003 or older? If so none of the formulae supplied will work.

 

[KRice]

@Fluff, shouldn't mine work...or am I missing something?

 

[Peter_SSs]

I have got column like below that has got L (liter), kg(kilogram) and so on. If I want to separate after highphyne - sign but also just want number including decimal (not with kg or g etc). How can I do . I want to achieve 250 , 2.6, .45 , .250

What about this with your older version?

23 06 07.xlsm
	A	B
1	GREASE - 250L	250
2	GREASE STRO 30 - 2.6KG	2.6
3	GREASE - .45KG	0.45
4	GREASE - 250G	250
Extract Number

Cell Formulas
Range	Formula
B1:B4	B1	=REPLACE(LEFT(A1,LOOKUP(1,-MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,99)),1),ROW(INDEX(A:A,1):INDEX(A:A,99)))),1,FIND("-",A1),"")+0

except the newer functions have been replaced with those available in older versions of Excel:

.. but AGGREGATE still requires 2010 or later.

 

[KRice]

but AGGREGATE still requires 2010 or later.

...oh, I missed that. Thank you.

 

[DIPASGL]

Here is a variation of a solution offered by @Sufiyan97 except the newer functions have been replaced with those available in older versions of Excel:

Book1
	A	B
1
2	GREASE - 250G	250
3	GREASE STRO 30 - 2.6KG	2.6
4	GREASE - .45KG	0.45
5	GREASE - 250G	250
try

Cell Formulas
Range	Formula
B2:B5	B2	=0+LEFT(RIGHT(A2,LEN(A2)-FIND("- ",A2)-1),AGGREGATE(15,6,SEARCH(CHAR(ROW($65:$90)),RIGHT(A2,LEN(A2)-FIND("- ",A2)-1)),1)-1)

Thanks for reply but wt is 65:90 in below formula and how should I use this in my sheet

=0+LEFT(RIGHT(A2,LEN(A2)-FIND("- ",A2)-1),AGGREGATE(15,6,SEARCH(CHAR(ROW($65:$90)),RIGHT(A2,LEN(A2)-FIND("- ",A2)-1)),1)-1)