concatenating a number plus a formatted number

[Guest]

Hello:

Cell I2 has "70" in it. Cell J2 has "050" in it.

I want to combine these two cells to read "70-050". So, I used the
following formula: =I2&"-"&J2

Here's the problem. Cell J2 is a formatted number. The actual value of it
is "50" not "050". So, when I used my formula, I got "70-50" instead of
"70-050".

Then, I tried the following: =I2&"-"&"0"&J2

But, cell J45 has "002" in it but the actual value is "2". I had to copy
down this formula to a whole column, you see.

So, this latest formula brought back "70-02" instead of "70-002".

I can't win today, I guess!

Could someone please help me to get a formula to read "70-"three formatted
numbers no matter what the value is"?

Thanks!

childofthe1980s

 

[davesexcel]

Hi Child of the 80's, I loved the 80's

The first thing I thought of was something like this
=if(b2<10,a1&"-"&"00"&b2,a1&"-"&"0"&b2)

would something like this work for you??

 

[Guest]

Thank you, Dave!!! You have really saved my life today!!! I wish that I
knew how to repay you!

Yep, the 80's were great! The 70's were so depressing and the 90's were so
boring. The best thing of all is that this decade is much like the 80's. I
don't know about you, but I'm having a great time!

childofthe1980s

 

[bibi12]

try in C10

=A10&"-" & IF(B10<10,"00"&B10,IF(B10>=10,"0"&B10,U10))

oops, a small fixing & it works great

=A15&"-" & IF(B15<10,"00"&B15,IF(B15>=100,B15,IF(( B15<100),"0"&B15,"") ))

 

[Tom Ogilvy]

=I2&"-"&Text(J2,"000")

 

[Bob Phillips]

See Tom's response, it is a bit cleaner.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)