• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula help - extract specific text

Hi All,

I have text in cell A1 & A2 and I need to extract specific text from it.

Cell A1 - the text I have to extract is always located at the end between ".jpg" and last "/"

Cell A2 - the text I have to extract is always located between "https://www.arabia.com/" and ".html". In addition I have to delete the "-" between the text.

Please see attached file and image below showing what i want to achieve in cells B1 & B2

Can you please help.

64320
 

Attachments

  • Product.xlsx
    8.7 KB · Views: 3
Would you be okay with a solution based on Power Query steps?


Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Input", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Input.1", "Input.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Input.1", type text}, {"Input.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Input.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Input.2.1", "Input.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Input.2.1", type text}, {"Input.2.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","-"," ",Replacer.ReplaceText,{"Input.2.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Input.1", "Input.2.2"})
in
    #"Removed Columns"
 

Attachments

  • Copy of Product.xlsx
    17.6 KB · Views: 3
Would you be okay with a solution based on Power Query steps?


Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Input", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Input.1", "Input.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Input.1", type text}, {"Input.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Input.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Input.2.1", "Input.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Input.2.1", type text}, {"Input.2.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","-"," ",Replacer.ReplaceText,{"Input.2.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Input.1", "Input.2.2"})
in
    #"Removed Columns"
Thanks ! why not. However, I am not too familiar with Power Query. How will it impact if the data is in column B & C instead of Rows A2 & A3?
 
Thanks ! why not. However, I am not too familiar with Power Query. How will it impact if the data is in column B & C instead of Rows A2 & A3?
Transpose as an extra and first step might be required. Please make a sample file that is exactly structured as your real life example.
Notice PQ is designed to use the mouse as magic want so to speak. If you are a bit familiar with Excel and its functions, it is rather easy and addictive to get used too.

Hi, @bosco_yip, just to say your formula looks ridiculously easy. #YouShowOff ;-), it is wonderful what you do here.
 
Transpose as an extra and first step might be required. Please make a sample file that is exactly structured as your real life example.
Notice PQ is designed to use the mouse as magic want so to speak. If you are a bit familiar with Excel and its functions, it is rather easy and addictive to get used too.

Hi, @bosco_yip, just to say your formula looks ridiculously easy. #YouShowOff ;-), it is wonderful what you do here.
Thanks. please see updated file. Result columns are highlighted in yellow
 

Attachments

  • Copy of Product.xlsx
    15.4 KB · Views: 5
Here is a Power Query Solution.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Product code", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Product code.1", "Product code.2", "Product code.3", "Product code.4"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Product code.3", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Product code.3.1", "Product code.3.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Product code.1", "Product code.2", "Product code.3.1", "Product code.4"})
in
    #"Removed Columns"
 
This is the formula version for
Here is a Power Query Solution.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Product code", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Product code.1", "Product code.2", "Product code.3", "Product code.4"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Product code.3", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Product code.3.1", "Product code.3.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Product code.1", "Product code.2", "Product code.3.1", "Product code.4"})
in
    #"Removed Columns"

Struggling with where to paste as my excel is not opening Power Query editor but will make it work and let you know !
 
Back
Top