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
=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND("/",B1)-1)," ",REPT(" ",20)),20)),"m"," m"),"g"," g")

 

[HatchetHarry]

Hey Peter thanks again,

It returns #VALUE ! probably because it expects to find both "m" AND "g" and not ONLY one of them ?

 

[sandy666]

you can try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"src", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([src], " ", "/"), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text After Delimiter", each Text.AfterDelimiter([Text Between Delimiters], "- "), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Text After Delimiter", "result", each if [Text After Delimiter] = "" then [Text Between Delimiters] else [Text After Delimiter]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Text Between Delimiters", "Text After Delimiter"})
in
    #"Removed Columns"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]src[/td][td=bgcolor:#70AD47]result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Broad Spectrum SPF 15 - # Medium Beige 18g/0.6oz[/td][td=bgcolor:#E2EFDA]# Medium Beige 18g[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Restructuring 1000ml/33.8oz[/td][td] 1000ml[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Treatment 118ml/4oz[/td][td=bgcolor:#E2EFDA] 118ml[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Spectrum SPF 15 - # Medium 18g/0.6oz[/td][td]# Medium 18g[/td][/tr]
[/table]

 

[Peter_SSs]

It returns #VALUE ! probably because it expects to find both "m" AND "g" and not ONLY one of them ?

I would expect that error if the text did not contain a "/" character, but your 4 examples all did contain such a character. The error is not related to the "m" "g" issue as SUBSTITUTE is not offended if the text to be substituted is not found.

Here is my formula working on your 4 examples.

<b>Extract Amt</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:354px;" /><col style="width:190px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Broad Spectrum SPF 15 - # Medium Beige 18g/0.6oz</td><td style="font-size:10pt; ">18 g</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Restructuring 1000ml/33.8oz</td><td style="font-size:10pt; ">1000 ml</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Treatment 118ml/4oz</td><td style="font-size:10pt; ">118 ml</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Spectrum SPF 15 - # Medium 18g/0.6oz</td><td style="font-size:10pt; ">18 g</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=SUBSTITUTE(SUBSTITUTE<span style=' color:008000; '>(TRIM<span style=' color:#0000ff; '>(RIGHT<span style=' color:#ff0000; '>(SUBSTITUTE<span style=' color:#804000; '>(LEFT<span style=' color:#ff7837; '>(B1,FIND<span style=' color:#8000ff; '>("/",B1)</span>-1)</span>," ",REPT<span style=' color:#ff7837; '>(" ",20)</span>)</span>,20)</span>)</span>,"m"," m")</span>,"g"," g")</td></tr><tr><td >C2</td><td >=SUBSTITUTE(SUBSTITUTE<span style=' color:008000; '>(TRIM<span style=' color:#0000ff; '>(RIGHT<span style=' color:#ff0000; '>(SUBSTITUTE<span style=' color:#804000; '>(LEFT<span style=' color:#ff7837; '>(B2,FIND<span style=' color:#8000ff; '>("/",B2)</span>-1)</span>," ",REPT<span style=' color:#ff7837; '>(" ",20)</span>)</span>,20)</span>)</span>,"m"," m")</span>,"g"," g")</td></tr><tr><td >C3</td><td >=SUBSTITUTE(SUBSTITUTE<span style=' color:008000; '>(TRIM<span style=' color:#0000ff; '>(RIGHT<span style=' color:#ff0000; '>(SUBSTITUTE<span style=' color:#804000; '>(LEFT<span style=' color:#ff7837; '>(B3,FIND<span style=' color:#8000ff; '>("/",B3)</span>-1)</span>," ",REPT<span style=' color:#ff7837; '>(" ",20)</span>)</span>,20)</span>)</span>,"m"," m")</span>,"g"," g")</td></tr><tr><td >C4</td><td >=SUBSTITUTE(SUBSTITUTE<span style=' color:008000; '>(TRIM<span style=' color:#0000ff; '>(RIGHT<span style=' color:#ff0000; '>(SUBSTITUTE<span style=' color:#804000; '>(LEFT<span style=' color:#ff7837; '>(B4,FIND<span style=' color:#8000ff; '>("/",B4)</span>-1)</span>," ",REPT<span style=' color:#ff7837; '>(" ",20)</span>)</span>,20)</span>)</span>,"m"," m")</span>,"g"," g")</td></tr></table></td></tr></table>

 

[HatchetHarry]

@Peter it's working now.
Thank you very much for this part.
@sandy666 : that's indeed a good idea your code is working with manual inputs (can't figure why it's not working with my data...).
With some thoughts, it's a bit complicated for inexperience users that will use the file + these datas come per batches and not as a single hard file.

If someone has suggestions I'm still here,

Thanks !

 

[sandy666]

can't figure why it's not working with my data...)

try to attach more representative data ca. 50 rows. (any chance for excel file?)

 

[Peter_SSs]

@Peter it's working now.
Thank you very much for this part.

You're welcome. Glad you got it sorted.

 

[HatchetHarry]

Hey @sandy !

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

Thanks for you help

 

[sandy666]

I will
but you try this first:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"src", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([src], "- ", "  "), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters.1", each Text.BetweenDelimiters([src], "  ", "/"), type text),
    #"Merged Columns" = Table.CombineColumns(#"Inserted Text Between Delimiters1",{"Text Between Delimiters", "Text Between Delimiters.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"result"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"result", Text.Trim, type text}})
in
    #"Trimmed Text"[/SIZE]