Concatenation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Francis Hookam - 22 Jun 2005 13:57 GMT

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

Reply to this Message

JE McGimpsey - 22 Jun 2005 14:10 GMT

> So, I might have a formula which looks like this
>
[quoted text clipped - 11 lines]
> 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], "")

Reply to this Message

Bob Greenblatt - 23 Jun 2005 13:14 GMT

On 6/22/05 9:10 AM, in article
jemcgimpsey-64D2B2.07100022062005@msnews.microsoft.com, "JE McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>> So, I might have a formula which looks like this
>>
[quoted text clipped - 15 lines]
>
> =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")

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Mac Forum / Applications / Excel / June 2005

Concatenation