Combine formulas

[HatchetHarry]

Hello there,

I have a formula which extract a specific word from a cell and I could succeed :

=LEFT(MID(K2;FIND(" ";K2)+1;LEN(K2));FIND("/";MID(K2;FIND(" ";K2)+1;LEN(K2)))-1)

The result is "100ml"

After, my goal was to separate "100" and "ml" to make "100 ml" and I could also succeed with this formula :

=TRIM(REPLACE(A2;MIN(FIND("m";A2&"m"));0;" "))

Now I'm struggling a bit to find a way to put everything in ONE formula.

Any ideas ?

Thank you,
Harry

 

[Special-K99]

If you supply the original data there may be a better way

=TRIM(REPLACE(LEFT(MID(K2;FIND(" ";K2)+1;LEN(K2));FIND("/";MID(K2;FIND(" ";K2)+1;LEN(K2)))-1);MIN(FIND("m";LEFT(MID(K2;FIND(" ";K2)+1;LEN(K2));FIND("/";MID(K2;FIND(" ";K2)+1;LEN(K2)))-1)&"m"));0;" "))

Will it always be ml in the data ?

 

[HatchetHarry]

Hello Special-K99,

Thanks for your reply.
Unfortunately it doesn't work like I want.

Here are the data :

B2 : Water Mineral 100ml/3.4oz
A2 : =LEFT(MID(B2;FIND(" ";B2)+1;LEN(B2));FIND("/";MID(B2;FIND(" ";B2)+1;LEN(B2)))-1)
A3 : =TRIM(REPLACE(A2;MIN(FIND("m";A2&"m"));0;" "))

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want (after you replace "," with ";" for your Excel version)?
If not, please give some more varied sample data (say 5-6 rows) and expected results.

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(B2,FIND("/",B2)-1)," ",REPT(" ",20)),20)),"m"," m")

 

[Special-K99]

I have a formula which extract a specific word from a cell and I could succeed :

=LEFT(MID(K2;FIND(" ";K2)+1;LEN(K2));FIND("/";MID(K2;FIND(" ";K2)+1;LEN(K2)))-1)

The result is "100ml"

No it's not.
Not given the data you supplied.

"Water Mineral 100ml/3.4oz"

results in "Mineral 100ml"

 

[HatchetHarry]

Genius Peter !
Thanks a lot ! Please if you have time can you explain me quickly how you came out with this formula ?

No it's not.
Not given the data you supplied.

"Water Mineral 100ml/3.4oz"

results in "Mineral 100ml"

It does work for me...

Thanks anyway !

 

[HatchetHarry]

My bad there was suppose to be 2 spaces between "Mineral" and "100ml" that's why.

 

[Peter_SSs]

Thanks a lot ! Please if you have time can you explain me quickly how you came out with this formula ?

Working from the inside of the formula outwards ..
Extract the part to the left of the "/" with FIND and LEFT
Replace all the spaces with 20 spaces so the "words" are well spread out
Take the 20 characters at the right. This will get the 100ml with quite few leading spaces
TRIM removes the leading spaces
Replace (substitute) the "m" with " m" to introduce the required separation

It does work for me...

The point Special-K99 was making was that if the text is
Water Mineral 100ml/3.4oz
The formula you first posted returns "Mineral 100ml" not "100ml" as you stated.
Here it is:

Book1
	K	L
2	Water Mineral 100ml/3.4oz	Mineral 100ml
Sheet5

Cell Formulas
Range	Formula
L2	=LEFT(MID(K2,FIND(" ",K2)+1,LEN(K2)),FIND("/",MID(K2,FIND(" ",K2)+1,LEN(K2)))-1)

 

[HatchetHarry]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=LEFT(MID(K2,FIND(" ",K2)+1,LEN(K2)),FIND("/",MID(K2,FIND(" ",K2)+1,LEN(K2)))-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

During my copy paste one space has been removed from "Water Mineral 100ml/3.4oz" between the "l" and the "1" and my formula =LEFT(MID(K2,FIND(" ",K2) takes 2 spaces in argument.

Thank you very much both of you for your help,
Harry