Concatenation help

[Nadine]

I have 3 cells I need to merge into one. One of the cells is a number
anywhere from 1-16 or more. What I need is for the number in this cells to
always be concatenated into a 2 digit number. Example:
Cell A1 = USA123-4555678952
Cell A2 = CD
Cell A3 = 4
I need the final output to be 234555678952CD04.
So far I have the following formula:
=RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&A3 but this doesn't put "04" after the
CD. It only puts "4" after it. Any ideas for Excel 2003?
Thanks.

 

[Gary''s Student]

A tiny trick:

=RIGHT(LEFT(A1,5)&RIGHT(A1,10),12)&A2&TEXT(A3,"00")

 

[Nadine]

THANK YOU!!!!! I knew you'd come through for me.

 

[Gary''s Student]

You are welcome. Thanks for the feedback.

 

[Rick Rothstein]

Two characters and one function call shorter...

=RIGHT(SUBSTITUTE(A1,"-",""),12)&A2&TEXT(A3,"00")

 

[Nadine]

Gary,
If the payment # is 10, this formula records it as 01. How do I write the
formula so pmt 1 comes out as 01 and pmt 10 comes out as 10 and pmt 15 comes
out as 15, etc?
Thanks.

 

[steve]

Change the
&TEXT(A3,"00")
to read
&TEXT(A3,"0#")

Regards
Steve

 

[Jackpot]

Which cell contain pmt number...

=TEXT(A1,"00")
will display the numeric in A1 as 2 digits (zero padded).

If this is nothing to do with the previous formula and in a totally
differnet cell; then change the number format of the cell to 00

 

[Rick Rothstein]

Does A3 contain the payment number? If so, I don't get 01 with Gary''s
Student's formula like you posted; however, I do get the beginning part of
the resulting number to be an incorrect value. For the values you posted in
A1 and A2 and 10 in A3, I get his formula to display...

124555678952CD10

whereas I think the value you wanted for these values is this instead...

234555678952CD10

If that latter value is correct, then take a look at the formula I posted
because that is the value it calculates to.