concatenating a number plus a formatted number

[childothe1980s]

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??

[childothe1980s]

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

"davesexcel" wrote:

>
> 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??
>
>
> --
> davesexcel
> ------------------------------------------------------------------------
> davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
> View this thread: http://www.excelforum.com/showthread...hreadid=519041
>
>

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"childothe1980s" <[email protected]> wrote in message
news:[email protected]...
> 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

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"childothe1980s" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "davesexcel" wrote:
>
> >
> > 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??
> >
> >
> > --
> > davesexcel
> > ------------------------------------------------------------------------
> > davesexcel's Profile:
http://www.excelforum.com/member.php...o&userid=31708
> > View this thread:
http://www.excelforum.com/showthread...hreadid=519041
> >
> >