Concatenation

[Francis Hookam]

I am concatenating cells together and including &", "& between to provide a
comma and a space between pieces of info

Some of the cells contain data derived from further cells, although some may
be empty

So, I might have a formula which looks like this

=RC[13]&", "&RC[12]&", "&YEAR(RC[3])&IF(ISTEXT(RC[11]),", "&RC[11],"")

In the last part I have:

IF(ISTEXT(RC[11]),", "&RC[11],"")

which tests whether or not there is anything in RC[11]

BUT what is in RC[11] is already the result of a similar IF statement where
there may be something or just ""

Clearly the ISTEXT is not the right test since I always get the comma and
space even if there is nothing to be concatenated

Any suggestions?

Francis Hookham

 

[JE McGimpsey]

So, I might have a formula which looks like this

=RC[13]&", "&RC[12]&", "&YEAR(RC[3])&IF(ISTEXT(RC[11]),", "&RC[11],"")

In the last part I have:

IF(ISTEXT(RC[11]),", "&RC[11],"")

which tests whether or not there is anything in RC[11]

BUT what is in RC[11] is already the result of a similar IF statement where
there may be something or just ""

Clearly the ISTEXT is not the right test since I always get the comma and
space even if there is nothing to be concatenated

One way:

=IF(RC[11]<>"", ", " & RC[11], "")

 

[Bob Greenblatt]

So, I might have a formula which looks like this

=RC[13]&", "&RC[12]&", "&YEAR(RC[3])&IF(ISTEXT(RC[11]),", "&RC[11],"")

In the last part I have:

IF(ISTEXT(RC[11]),", "&RC[11],"")

which tests whether or not there is anything in RC[11]

BUT what is in RC[11] is already the result of a similar IF statement where
there may be something or just ""

Clearly the ISTEXT is not the right test since I always get the comma and
space even if there is nothing to be concatenated

One way:

=IF(RC[11]<>"", ", " & RC[11], "")

Or, another way, if you really want to see if the cell is empty and does not
contain a comma:

=if(len(rc[11])=0,"whatever","whatever else")