HELP WITH FORMULA
 

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

HELP WITH FORMULA
 

With your address in a1 try this

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

Mike

"Leon" wrote:

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

HELP WITH FORMULA
 

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" wrote:

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

HELP WITH FORMULA
 

Perfect, thanks Mike

"Mike H" wrote:

With your address in a1 try this

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

Mike

"Leon" wrote:

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

HELP WITH FORMULA
 

Thanks,

"thechilipino" wrote:

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" wrote:

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