concatenating a number plus a formatted number
 

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

concatenating a number plus a formatted number
 

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


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519041

concatenating a number plus a formatted number
 

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


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519041

concatenating a number plus a formatted number
 

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

--
Regards,
Tom Ogilvy


"childothe1980s" wrote in message
...
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

concatenating a number plus a formatted number
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"childothe1980s" wrote in message
...
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??:mad:


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile:
http://www.excelforum.com/member.php...o&userid=31708
View this thread:
http://www.excelforum.com/showthread...hreadid=519041