Constant in concatenation
 

Is there a way to have a constant not appear in a concatenation cell until
the full number of cells in the concatenation are completed?

What I'm trying to do is create a concatenation of several cells to come up
with a part description. In this case, it's for a cable. The cables can
have several (many)conductors, and part of the description calls for the
conductor segment to have a "C" before the number of conductors.

Everything works fine, but it seems silly to have a validation for one value
("C"). I can do this, and I have also included a "C" in the concatenation
formula. Only problem is, this way the "C" shows up in the concatenation
cell before the other values are established. This isn't really 'elegant'

Thanks,

Dave

Constant in concatenation
 

Maybe you can check to see if any of the cells are used:
=if(counta(a1:e1)=0,"","C" & ...)

or if all the cells are filled:
=if(counta(a1:e1)<5,"","C" & ...)



Dave wrote:

Is there a way to have a constant not appear in a concatenation cell until
the full number of cells in the concatenation are completed?

What I'm trying to do is create a concatenation of several cells to come up
with a part description. In this case, it's for a cable. The cables can
have several (many)conductors, and part of the description calls for the
conductor segment to have a "C" before the number of conductors.

Everything works fine, but it seems silly to have a validation for one value
("C"). I can do this, and I have also included a "C" in the concatenation
formula. Only problem is, this way the "C" shows up in the concatenation
cell before the other values are established. This isn't really 'elegant'

Thanks,

Dave

--

Dave Peterson

Constant in concatenation
 

Hi,
Not sure if I'm on the right track, but if you are concatenating Cells A1,
B1, C1, D1, all into E1, perhape something like:
IF(COUNTA(A1:D1)=4,CONCATENATE(A1:D1),"")
or some other use of COUNTA to check that all contributing cells have data.
Regards - Dave.

Constant in concatenation
 

It would have helped if you would've posted your formula.

So, my best guess is:

=IF(COUNTA(A1:D1)<4,"","C"&A1&B1&C1&D1)


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Is there a way to have a constant not appear in a concatenation cell until
the full number of cells in the concatenation are completed?

What I'm trying to do is create a concatenation of several cells to come
up
with a part description. In this case, it's for a cable. The cables can
have several (many)conductors, and part of the description calls for the
conductor segment to have a "C" before the number of conductors.

Everything works fine, but it seems silly to have a validation for one
value
("C"). I can do this, and I have also included a "C" in the concatenation
formula. Only problem is, this way the "C" shows up in the concatenation
cell before the other values are established. This isn't really 'elegant'

Thanks,

Dave

Constant in concatenation
 

....CONCATENATE(A1:D1)

That won't work. CONCATENATE won't work on arrays. You'd have to do this:

.....CONCATENATE(A1,B1,C1,D1)

Saves a few keystrokes if you do it like this:

.....A1&B1&C1&D1

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi,
Not sure if I'm on the right track, but if you are concatenating Cells A1,
B1, C1, D1, all into E1, perhape something like:
IF(COUNTA(A1:D1)=4,CONCATENATE(A1:D1),"")
or some other use of COUNTA to check that all contributing cells have
data.
Regards - Dave.

Constant in concatenation
 

On Jun 9, 3:31 pm, Dave wrote:
Is there a way to have a constant not appear in a concatenation cell until
the full number of cells in the concatenation are completed?

What I'm trying to do is create a concatenation of several cells to come up
with a part description. In this case, it's for a cable. The cables can
have several (many)conductors, and part of the description calls for the
conductor segment to have a "C" before the number of conductors.

Everything works fine, but it seems silly to have a validation for one value
("C"). I can do this, and I have also included a "C" in the concatenation
formula. Only problem is, this way the "C" shows up in the concatenation
cell before the other values are established. This isn't really 'elegant'

Thanks,

Dave

Just for fun:
=IF(OR(ISBLANK(E27:E30)),"","C"&E27&E28&E29&E30)
Use CTRL-SHIFT-ENTER since it is an array.