• 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.

Remove data from cells in Excel

suzie_blue

New Member
Hello everybody!

  1. I'm trying to remove "concert" and "***completed on 11APR2024" for Italy and have the results in corresponding C2 cell
  2. I'm trying to remove "exposition" and "***completed on 12JUN2024" for Spain and have the results in corresponding C3 cell
  3. I'm trying to remove "concert" and "completed on 11APR2024" for Italy and have the results in corresponding C4 cell
  4. I'm trying to remove "exposition" and "completed on 12JUN2024" for Spain and have the results in corresponding C5 cell
I've tried to use find a replace but it doesn't work. I have thousands of data like this so it would be nice to not remove them manually.

Could someone help me? The excel sheet is attached.

Thank you!
Suzie Blue
 

Attachments

  • Country Analysis.xlsx
    9.2 KB · Views: 5
Oh yes it does! Thank you very much @pecoflyer !

And then if I want to remove "concert" and "exposition" between ')' and ':' - should I use the same formula?

Thanks,
Suzie Blue
 
Attached a Power Query solution which does it all at once It is available as an add-in for many XL versions ( and included in others).
If you add data and click " Refresh all" in the Data ribbon, the result is updated immediately
 

Attachments

  • Country Analysis(1).xlsx
    19.3 KB · Views: 1
Here is the M code for those interested
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Details", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Details.1", type text}, {"Details.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","concert","",Replacer.ReplaceText,{"Details.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","exposition","",Replacer.ReplaceText,{"Details.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Details.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Details.1", "Details.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Result"}})
in
    #"Renamed Columns"
 
Back
Top