HELP WITH FORMULA

[Leon]

Is there a formula which I can use to strip out a particular character from a
text string? I have been sent a database with addresses in one of the
columns. The data has presumably been copied from a capture program and where
some fields were empty then an asterisk is reflected eg. 4 LINGFIELD
CLOSE,*,*,*,MILNERTON RIDGE,7441. I want the result 4 LINGFIELD
CLOSE,MILNERTON RIDGE,7441

 

[Mike H]

With your address in a1 try this

=SUBSTITUTE(A1,"*,","")

Mike

 

[thechilipino]

Hello Leon -

If the text pattern you want to replace is consistent, i.e. always ",*",
you can use find and replace (ctrl-h), replacing ",*" with a blank space.

I hope that helps.

Cheers.

 

[Leon]

Perfect, thanks Mike

With your address in a1 try this

=SUBSTITUTE(A1,"*,","")

Mike

 

[Leon]

Thanks,

Hello Leon -

If the text pattern you want to replace is consistent, i.e. always ",*",
you can use find and replace (ctrl-h), replacing ",*" with a blank space.

I hope that helps.

Cheers.