I am importing data from a spreadsheet that I don't control. It has addresses that mostly look like this:

123 Sesame [ST] Street

Although some don't have the [ST] bit.

I want to remove the [ST] bit so the address is 123 Sesame Street.

I thought it would be easiest to do this:

= Table.ReplaceValue(#"Changed Type","[wildcard for any characters] ","",Replacer.ReplaceText,{"Address"})

But it seems that M doesn't have wildcards. I used the Column from Examples to get this (my If statement added to eliminate errors from the few addresses that don't contain [x]:

= if Text.Contains ([Address], "[") then Text.Combine({Text.Proper(Splitter.SplitTextByDelimiter("[", QuoteStyle.None)([Address]){0}?), Text.Start(Splitter.SplitTextByDelimiter("] ", QuoteStyle.None)([Address]){1}?, Text.Length(Splitter.SplitTextByDelimiter("] ", QuoteStyle.None)([Address]){1}?) - 2)}) else [Address]

But I don't like it because I'm getting a "cannot convert null value to logical" error that I'm pretty sure relates to it.

Is there something simpler I can do to achieve this?

Thanks, Mardi