Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519041 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519041 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519041 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying the number of a custom formatted number cell | Excel Discussion (Misc queries) | |||
Date column changed to number format while concatenating | Excel Discussion (Misc queries) | |||
Convert a number formatted as text to a number in a macro | Excel Programming | |||
number keep being formatted as date | New Users to Excel | |||
concatenating and formatting area code and phone number columns | Excel Worksheet Functions |