Remove text from name

[sobrien1234]

I have a list of names however some of them have text in the middle which i want removed. In column J I have the following names - I want to write a formula in column K that removes the text in between [ ] so it just shows their name - for example, in the 2nd row the answer would be "Grant Matthew" if the formula works. Thanks in advance!!

Radoslovic Stephen

Grant [MY-GRANT01] Matthew

Tait [MY-TAIT01] John

Virgo [MY-VIRGO01] Tamara

Tait [MY-TAIT01] John

Mezger - OLD ACC Kelly

Ruig Joel

Radoslovic Stephen

 

[sriche01]

Is it safe to assume first and last words are always what you want?

 

[Rick Rothstein]

Does this formula do what you want...

=IFERROR(REPLACE(J1,FIND("[",J1)-1,FIND("]",J1)-FIND("[",J1)+2,""),J1)

 

[sandy666]

with Power Query based on your example

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TAD = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}), type text),
    TBD = Table.AddColumn(TAD, "Text Before Delimiter", each Text.BeforeDelimiter([Name], " "), type text),
    Merge = Table.CombineColumns(TBD,{"Text Before Delimiter", "Text After Delimiter"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Names"),
    ROC = Table.SelectColumns(Merge,{"Names"})
in
    ROC

 

[sobrien1234]

Is it safe to assume first and last words are always what you want?

yeah I think so

 

[sobrien1234]

Does this formula do what you want...

=IFERROR(REPLACE(J1,FIND("[",J1)-1,FIND("]",J1)-FIND("[",J1)+2,""),J1)

Perfecto!! Thanks

 

[sriche01]

Does Rick's formula work on the items without brackets?
Here's another option, though huge and clumsy.
=LEFT(J1,FIND(" ",J1,1)-1)&" "&MID(J1,FIND(">",SUBSTITUTE(J1," ",">",LEN(J1)-LEN(SUBSTITUTE(J1," ",""))))+1,LEN(J1)-FIND(">",SUBSTITUTE(J1," ",">",LEN(J1)-LEN(SUBSTITUTE(J1," ","")))))
Try them each on line 5 for example.

 

[Rick Rothstein]

Does Rick's formula work on the items without brackets?

Yes, that is what the IFERROR function handles.

 

[sriche01]

What I was unclear in is whether the "- OLD ACC" part in line 6 needed to be removed even though it was not enclosed in brackets. Either way, there are 2 options.

Thanks for all you do Rick. I believe you were the first to help me years ago when I first got on this message board.