Remove certain characters from a text string

[Andy Roberts]

I have a cell which groups together text into 1 string as follows:-

Name Address Postcode

Cell formula is A1&", "&B1&", "&C1

This returns Name, Address, Postcode

However sometime the postcode isnt known or relevant so we insert N/A in the
postcode column. When this happens the text string shows.... , N/A. I want
to be able to maintain the 3 columns in the string but if it includes ",
N/A" then it should remove it, but if it has a valid postocde (i.e. anythin
other than N/A) then it should display this as normal. I think the solution
lies with a nested FIND function but can't seem to get any joy.

Any ideas?

--
Regards

Andy
Win XP Pro
Office 2010

[h2so4]

Andy Roberts explained :

hello,

try

=A1 & ", " & B1 & IFERROR(", " & C1,"")

[Claus Busch]

Hi Andy,

Am Sat, 15 Jun 2013 13:52:12 +0100 schrieb Andy Roberts:

> However sometime the postcode isnt known or relevant so we insert N/A in the
> postcode column. When this happens the text string shows.... , N/A. I want
> to be able to maintain the 3 columns in the string but if it includes ",
> N/A" then it should remove it, but if it has a valid postocde (i.e. anythin
> other than N/A) then it should display this as normal. I think the solution
> lies with a nested FIND function but can't seem to get any joy.

try:
=A1&", "&B1&IF(ISNUMBER(C1),", "&C1,"")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Andy Roberts]

Thanks Guys for your fast response. Im not sure either will work as h2so4
suggestion i think is assuming the N/A is an excel generated error when its
something we specifically enter into the postcode cell. With your
suggestion Claus does it not require the postcode to be all numerical? Im
in the UK and we have a mixture of alpha and numerical characters in our
postcodes.

If I'm mistaken here then happily shout at me ;-)

Andy

"Andy Roberts" <an...@blue-bean.co.uk> wrote in message
news:0sZut.50286$9b....@fx32.am4...

[Claus Busch]

Hi Andy,

Am Sat, 15 Jun 2013 14:04:21 +0100 schrieb Andy Roberts:

> Thanks Guys for your fast response. Im not sure either will work as h2so4
> suggestion i think is assuming the N/A is an excel generated error when its
> something we specifically enter into the postcode cell. With your
> suggestion Claus does it not require the postcode to be all numerical? Im
> in the UK and we have a mixture of alpha and numerical characters in our
> postcodes.

if your "N/A" is not an error but a string, try:
=A1&", "&B1&IF(C1<>"N/A",", "&C1,"")

[Andy Roberts]

Pretty much perfect Claus, just a small tweak with cell references (my end)
and it worked. Many thanks


"Claus Busch" <claus...@t-online.de> wrote in message
news:kphp9g$abd$1...@news.albasani.net...

[Claus Busch]

Hi Andy,

Am Sat, 15 Jun 2013 14:41:45 +0100 schrieb Andy Roberts:

> Pretty much perfect Claus, just a small tweak with cell references (my end)
> and it worked. Many thanks

always glad to help. Thank you for the feedback.