software

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 quot;amp;quot; or quot;concatenatequot; the preceeding
zeros are deleted.

Ex. Col. 1 Col.2 Col.3 concatenate or string
I get
000321 032140 001547 321321401547
Please help.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,quot;000000quot;)amp;TEXT(A2,quot;000000quot;)amp;TEXT(A3,quot;0000 00quot;)

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,quot;000000quot;)amp;TEXT(A2,quot;000000quot;)amp;TEXT(A3 ,quot;000000quot;))

HTH,
Elkar

quot;Wrkn4alivnquot; wrote:

gt; I have tried everything to convert 5 columns into a single string. The
gt; problem is that these cells contain preceeding zeros, and when I try to
gt; convert them into a string using quot;amp;quot; or quot;concatenatequot; the preceeding
gt; zeros are deleted.
gt;
gt; Ex. Col. 1 Col.2 Col.3 concatenate or string
gt; I get
gt; 000321 032140 001547 321321401547
gt;
gt;
gt;
gt; Please help.
gt;
gt;

=TEXT(A1,quot;000000quot;)amp;TEXT(B1,quot;000000quot;)amp;TEXT(C1,quot;0000 00quot;)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Wrkn4alivnquot; gt; wrote in message ups.com...
gt; I have tried everything to convert 5 columns into a single string. The
gt; problem is that these cells contain preceeding zeros, and when I try to
gt; convert them into a string using quot;amp;quot; or quot;concatenatequot; the preceeding
gt; zeros are deleted.
gt;
gt; Ex. Col. 1 Col.2 Col.3 concatenate or string
gt; I get
gt; 000321 032140 001547 321321401547
gt;
gt;
gt;
gt; Please help.
gt;