Tricky extract

[HatchetHarry]

Hi there,

Coming back from my datas from yesterday and the help of Peter, I have 2 questions :

1st question :

My data :

B1 : Broad Spectrum SPF 15 - # Medium Beige 18g/0.6oz
B2 : Restructuring 1000ml/33.8oz
B3 : Treatment 118ml/4oz
B4 : Spectrum SPF 15 - # Medium 18g/0.6oz

I would like to extract "18g" OR "1000ml" in one formula.
For the moment I have these formulas working :
Basically, they take the word from the double space to the / and then format it to have a space between the num and text.

What I have :

A1 :
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(B1;FIND("/";B1)-1);" ";REPT(" ";20));20));"g";" g")
result : 18 g

A2 :
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(B2;FIND("/";B2)-1);" ";REPT(" ";20));20));"m";" m")
result : 1000 ml

etc...

Unfortunately, these formulas are independent.

What I would like to have a general formula that takes the 2 possibilities.
Something like :

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(B1;FIND("/";B1)-1);" ";REPT(" ";20));20));"m";" m" OR "g"; " g") - and of course doesn't make the formula crash if one of them isn't found.

2nd question

Still, with the same data.

In the case of this character "-" exists for B1 for example, I'm extracting the sentence from "-" to " " with this formula :
=LEFT(MID(B2;FIND("-";B2)+1;LEN(B2));FIND(" ";MID(B2;FIND("-";B2)+1;LEN(B2)))-1)

result : # Medium Beige

My final idea is to compile everything to have something like this :

A1 = # Medium Beige;18 g
A2 = 1000 ml
B3 = 118 ml
B4 = # Medium;18 g

For the record,
- There is always a "-" in front of the #
- There are always 2 spaces before the volume (ml or g)
- I can't split the results into different columns (formatted for API)

I know it's a bit complicated and I don't even know if it's possible this way (maybe with VBA ?)

If someone has a clue, I'll be glad to read it.

Thanks,
Harry

 

[Peter_SSs]

Try this link https://we.tl/t-cnmy8NJr9y

Since it appears you have some blank cells in your data, my formula would need modification.

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

 

[sandy666]

btw. do you really need these blank rows?

example file

 

[Peter_SSs]

@ HatchetHarry
Since you are considering different options, here is another one involving a very short user-defined function.To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Qty(s As String) As String
  If InStr(1, s, "/") Then Qty = Val(Split(s)(UBound(Split(s)))) & IIf(Right(Split(Split(s)(UBound(Split(s))), "/")(0), 1) = "g", " g", " ml")
End Function

Excel Workbook
	A	B
1	src
2	Ahava Deadsea Water Mineral Foot Cream 100ml/3.4oz	100 ml
3	Ahava Deadsea Water Mineral Foot Cream 150ml/5oz	150 ml
4
5	Aesop Resurrection Aromatique Hand Balm 500ml/16.67oz	500 ml
6	Aesop Resurrection Aromatique Hand Balm 120ml/4oz	120 ml
109	Avene High Protection Tinted Compact SPF 50 - # Honey 10g/0.3oz	10 g
110	Avene High Protection Tinted Compact SPF 50 - # Beige 10g/0.3oz	10 g
111
112	BareMinerals Collector's Edition Deluxe Original Foundation Broad Spectrum SPF 15 - # Medium Beige 18g/0.6oz	18 g
113	BareMinerals Collector's Edition Deluxe Original Foundation Broad Spectrum SPF 15 - # Medium 18g/0.6oz	18 g
Sheet1

 

[HatchetHarry]

@Peter it's working thank you very much for your time !
@sandy666 Haha yes I need the blank lines - a macro doing this with some conditions, you have only one column of data here
You code works perfectly now.
The only issue I have is Power Query itself haha (I don't want users to dig into this - most of them are really inexperience users).
I'm trying now to find a way to automate this procedure and link the result column (from PQ) to another column of the main Sheet.

I know there is a way with a macro but tbh the links look really complicated so for the moment I'll stick to it.

Thank you guys

 

[sandy666]

The only issue I have is Power Query itself haha (I don't want users to dig into this - most of them are really inexperience users).

They cannot change PQ Table (result) if they don't change source.
If you mean they can open PQ editor and change something there - use: Review - Protect workbook (structure)

I'm trying now to find a way to automate this procedure and link the result column (from PQ) to another column of the main Sheet.

You can load result column there where you want.
To get result column only, on the end of steps, simply remove source (src) column in PQ Editor (right click - remove column). It will work, don't worry
I don't understand what you mean: link result to another column in the main sheet. - be more descriptive with your description. read my footnote again

 

[Peter_SSs]

@Peter it's working thank you very much for your time !

Cheers. No problem.