How to remove special characters and words from a cell value.

[Fred Wayne]

I need to remove the "*", the second word(Name), and the comma from a cell value. For example in cell B1 says *Chen,Hao,Mr.
I need that in cell C1, the asterisk, the 2 commas, and the name "Hao" to not be taken into account. So what I want is that in cell C1 to appear like this:
"Chen Mr.", the same thing will be for the rest of the names.

Is there a formula to get this done?

I am attaching an image of what I need and the original file.

 

[AlanSidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","*","",Replacer.ReplaceText,{"Column1.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Column1.1", "Column1.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1.2"})
in
    #"Removed Columns"

 

[Fred Wayne]

Ohhh I thought it was a formula. So where do I set that code?

 

[John Jairo V]

Hi to all!

One option could be:
[C1] : =TRIM(CONCAT(MID(SUBSTITUTE(SUBSTITUTE(B1,"*",""),",",REPT(" ",99)),1+99*{0;2},99)))
And drag it down. Blessings!

 

[Fred Wayne]

I have tried but it gives me an error

 

[Fred Wayne]

I just moved some cells down but just changed B1 for B2, but didn`t work.
=TRIM(CONCAT(MID(SUBSTITUTE(SUBSTITUTE(B2,"*",""),",",REPT(" ",99)),1+99*{0;2},99)))

 

[bosco_yip]

I have tried but it gives me an error

I think you don't have CONCAT function, of which only available in Excel 2019 or Office 365

Or,

you could try this formula instead, in C1 formula copied down :

=SUBSTITUTE(LEFT(B1,FIND(",",B1)-1),"*","")&" "&MID(B1,1-LOOKUP(,-FIND(",",B1,ROW(A:A))),9)

Regards

 

[Fred Wayne]

Yes I have Office 2016. I made the formula change and it did work! Thank you so much. You are amazing.

 

[pecoflyer]

Ohhh I thought it was a formula. So where do I set that code?

FYI Alan provided a Power Query solution as indicated. If you have some time try to learn it, it's pretty amazing ( and Alan too )

 

[AlanSidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.