Concatenate and keep preceeding zeros
 

I have tried everything to convert 5 columns into a single string. The
problem is that these cells contain preceeding zeros, and when I try to
convert them into a string using "&" or "concatenate" the preceeding
zeros are deleted.

Ex. Col. 1 Col.2 Col.3 concatenate or string
I get
000321 032140 001547 321321401547



Please help.

Concatenate and keep preceeding zeros
 

The problem is that the value of your cells is 321, 32140, and 1547. The
leading zeros are comming from cell formatting. Thus, if a funcion
references these cells, it only picks up the stored value, not what is
displayed.

To get around this, you'll need to tell the formula to reference the
displayed value, rather than the actual value. Try this:

=TEXT(A1,"000000")&TEXT(A2,"000000")&TEXT(A3,"0000 00")

The result will be a text value. If you need it to be a number, then
enclose it in a VALUE() funcion.

=VALUE(TEXT(A1,"000000")&TEXT(A2,"000000")&TEXT(A3 ,"000000"))

HTH,
Elkar

"Wrkn4alivn" wrote:

I have tried everything to convert 5 columns into a single string. The
problem is that these cells contain preceeding zeros, and when I try to
convert them into a string using "&" or "concatenate" the preceeding
zeros are deleted.

Ex. Col. 1 Col.2 Col.3 concatenate or string
I get
000321 032140 001547 321321401547



Please help.

Concatenate and keep preceeding zeros
 

=TEXT(A1,"000000")&TEXT(B1,"000000")&TEXT(C1,"0000 00")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Wrkn4alivn" wrote in message
ups.com...
I have tried everything to convert 5 columns into a single string. The
problem is that these cells contain preceeding zeros, and when I try to
convert them into a string using "&" or "concatenate" the preceeding
zeros are deleted.

Ex. Col. 1 Col.2 Col.3 concatenate or string
I get
000321 032140 001547 321321401547



Please help.