software

I have a number 21-/A/-525-/E/-0001 the / represent the cells, I want it in
one cell to look like 21-A-525-E-0001. We I concatenate or use amp; it removes
the dashes and turns 0001 into 1. Does anyone have any suggestions?


Stupid suggestion but have you tried formatting the cell as quot;Textquot;? I
don't know if this will work - just a thought

Good luck

Tony--
y_not
------------------------------------------------------------------------
y_not's Profile: www.excelforum.com/member.php...oamp;userid=19947
View this thread: www.excelforum.com/showthread...hreadid=525399Yes I tried that but when I do it removes the dashes and the o's
quot;y_notquot; wrote:

gt;
gt; Stupid suggestion but have you tried formatting the cell as quot;Textquot;? I
gt; don't know if this will work - just a thought
gt;
gt; Good luck
gt;
gt; Tony
gt;
gt;
gt; --
gt; y_not
gt; ------------------------------------------------------------------------
gt; y_not's Profile: www.excelforum.com/member.php...oamp;userid=19947
gt; View this thread: www.excelforum.com/showthread...hreadid=525399
gt;
gt;


If the last set is always 4 chars:

=A1amp;quot;-quot;amp;B1amp;quot;-quot;amp;C1amp;quot;-quot;amp;D1amp;quot;-quot;amp;(REPT(0,4-LEN(E1))amp;E1)

ABCDEFORMULA (RESULT)

121A525E121-A-525-E-0001

268B525E2168-B-525-E-0021--
jbrackett
------------------------------------------------------------------------
jbrackett's Profile: www.excelforum.com/member.php...oamp;userid=32577
View this thread: www.excelforum.com/showthread...hreadid=525399I think your problem is the cells with numbers have some wierd custom
formats. If this is the case you can use a formula like this

=TEXT(A1,quot;#-quot;)amp;B1amp;TEXT(C1,quot;-#-quot;)amp;D1amp;TEXT(E1,quot;-0000quot;)

quot;Clarequot; wrote:

gt; I have a number 21-/A/-525-/E/-0001 the / represent the cells, I want it in
gt; one cell to look like 21-A-525-E-0001. We I concatenate or use amp; it removes
gt; the dashes and turns 0001 into 1. Does anyone have any suggestions?

Wow that's graet except that the last 4 always show as 4 Zeros

quot;jbrackettquot; wrote:

gt;
gt; If the last set is always 4 chars:
gt;
gt; =A1amp;quot;-quot;amp;B1amp;quot;-quot;amp;C1amp;quot;-quot;amp;D1amp;quot;-quot;amp;(REPT(0,4-LEN(E1))amp;E1)
gt;
gt; ABCDEFORMULA (RESULT)
gt;
gt; 121A525E121-A-525-E-0001
gt;
gt; 268B525E2168-B-525-E-0021
gt;
gt;
gt; --
gt; jbrackett
gt; ------------------------------------------------------------------------
gt; jbrackett's Profile: www.excelforum.com/member.php...oamp;userid=32577
gt; View this thread: www.excelforum.com/showthread...hreadid=525399
gt;
gt;

Thanks that worked perfectly

quot;Slothquot; wrote:

gt; I think your problem is the cells with numbers have some wierd custom
gt; formats. If this is the case you can use a formula like this
gt;
gt; =TEXT(A1,quot;#-quot;)amp;B1amp;TEXT(C1,quot;-#-quot;)amp;D1amp;TEXT(E1,quot;-0000quot;)
gt;
gt; quot;Clarequot; wrote:
gt;
gt; gt; I have a number 21-/A/-525-/E/-0001 the / represent the cells, I want it in
gt; gt; one cell to look like 21-A-525-E-0001. We I concatenate or use amp; it removes
gt; gt; the dashes and turns 0001 into 1. Does anyone have any suggestions?

maybe:

=A1amp;quot;-quot;amp;B1amp;quot;-quot;amp;C1amp;quot;-quot;amp;D1amp;quot;-quot;amp;text(e1,quot;0000quot;)

Clare wrote:
gt;
gt; Wow that's graet except that the last 4 always show as 4 Zeros
gt;
gt; quot;jbrackettquot; wrote:
gt;
gt; gt;
gt; gt; If the last set is always 4 chars:
gt; gt;
gt; gt; =A1amp;quot;-quot;amp;B1amp;quot;-quot;amp;C1amp;quot;-quot;amp;D1amp;quot;-quot;amp;(REPT(0,4-LEN(E1))amp;E1)
gt; gt;
gt; gt; A B C D E FORMULA (RESULT)
gt; gt;
gt; gt; 1 21 A 525 E 1 21-A-525-E-0001
gt; gt;
gt; gt; 2 68 B 525 E 21 68-B-525-E-0021
gt; gt;
gt; gt;
gt; gt; --
gt; gt; jbrackett
gt; gt; ------------------------------------------------------------------------
gt; gt; jbrackett's Profile: www.excelforum.com/member.php...oamp;userid=32577
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=525399
gt; gt;
gt; gt;

--

Dave Peterson