Concatenate returns #value error if blank cells are included in the range

  • Thread starter Thread starter Ernie Sersen
  • Start date Start date
E

Ernie Sersen

I am trying to combine 4 columns of data into 1 column
using =concatenate(A1,B1,C1,D1). It returns a #value
error if any of the cells A1-D1 are blank. I tried
copy/paste special to convert all of the cells to 'value'
with no affect. Please help.
 
Hi
this formula should not return a #VALUE error. Are you sure A1:D1 do
not contain this error?
 
Hello,

I can't think of anything obvious that you may be doing
wrong here.

However, try setting each of A1, B1, C1, D1 to "general"
number format, as well as applying this setting to the
cell you are trying to put your concatenated value in.

Also, did you know you can shorten this formula to:

=A1&B1&C1&D1

the "&" sign simply replaces the "CONCATENATE" string.

Happy to Help,

Gary Thomson
 
I used the TEXT command TEXT(A1,"#")&TEXT(B1,"#")&TEXT
(C1,"#")&TEXT(D1,"#") to solve this one. It must have
been a formatting interpretation of the blank cells that
caused the problem.

THANKS...
 
Back
Top