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 :

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?

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

 

[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

 

[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,"")


Regards
Claus Busch

 

[Andy Roberts]

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

 

[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.


Regards
Claus Busch