| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Concatenate numbers and text while holding a custom number format.
I want to join the values of two cells with different formatting. Example:
"E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenate numbers and text while holding a custom number format.
Hi,
Try this =N3&"_"&INT(O3)&"+"&MID(O3,SEARCH(".",O3,1)+1,2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... I want to join the values of two cells with different formatting. Example: "E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenate numbers and text while holding a custom number format.
=E3 &"_" & TEXT(O3,"your format")
##0+#0.00 does not return 48+29 for me but maybe you have a typo? Gord Dibben MS Excel MVP On Mon, 8 Feb 2010 15:18:06 -0800, jpowell111 wrote: I want to join the values of two cells with different formatting. Example: "E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenate numbers and text while holding a custom number for
Thanks. This is so close but it places the + where the . usesd to be instead
of two places in front of it. I am working on stationing where 100' would be 1+00.00. Is there a way to move the + over? "Ashish Mathur" wrote: Hi, Try this =N3&"_"&INT(O3)&"+"&MID(O3,SEARCH(".",O3,1)+1,2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... I want to join the values of two cells with different formatting. Example: "E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenate numbers and text while holding a custom number for
Hi,
I could not understand that - could you kindly explain again with an example. My formula should give you the answer as NPL_48+29 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... Thanks. This is so close but it places the + where the . usesd to be instead of two places in front of it. I am working on stationing where 100' would be 1+00.00. Is there a way to move the + over? "Ashish Mathur" wrote: Hi, Try this =N3&"_"&INT(O3)&"+"&MID(O3,SEARCH(".",O3,1)+1,2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... I want to join the values of two cells with different formatting. Example: "E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenate numbers and text while holding a custom number for
Thanks for your patience.
Your formula is correct. What I need for this station is NPL_0+48.29. If the station was 236.98 then I need it to appear as NPL_2+36.98. In these examples the "+" relates to 100 (1+00) as a "," relates to 1000 (1,000). Thank you again. "Ashish Mathur" wrote: Hi, I could not understand that - could you kindly explain again with an example. My formula should give you the answer as NPL_48+29 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... Thanks. This is so close but it places the + where the . usesd to be instead of two places in front of it. I am working on stationing where 100' would be 1+00.00. Is there a way to move the + over? "Ashish Mathur" wrote: Hi, Try this =N3&"_"&INT(O3)&"+"&MID(O3,SEARCH(".",O3,1)+1,2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... I want to join the values of two cells with different formatting. Example: "E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenate numbers and text while holding a custom number for
=E3 &"_" & TEXT(O3,"##0+#0.00")
Returns NPL_2+36.98 with 236.98 in O3 Not what your original post requested. I want to come up with "NPL_48+29" Gord Dibben MS Excel MVP On Wed, 10 Feb 2010 09:33:01 -0800, jpowell111 wrote: Thanks for your patience. Your formula is correct. What I need for this station is NPL_0+48.29. If the station was 236.98 then I need it to appear as NPL_2+36.98. In these examples the "+" relates to 100 (1+00) as a "," relates to 1000 (1,000). Thank you again. "Ashish Mathur" wrote: Hi, I could not understand that - could you kindly explain again with an example. My formula should give you the answer as NPL_48+29 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... Thanks. This is so close but it places the + where the . usesd to be instead of two places in front of it. I am working on stationing where 100' would be 1+00.00. Is there a way to move the + over? "Ashish Mathur" wrote: Hi, Try this =N3&"_"&INT(O3)&"+"&MID(O3,SEARCH(".",O3,1)+1,2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... I want to join the values of two cells with different formatting. Example: "E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenate numbers and text while holding a custom number for
This is perfect!
You are right, I messed up the original post. Too much time burried in data. Thank you for the help. "Gord Dibben" wrote: =E3 &"_" & TEXT(O3,"##0+#0.00") Returns NPL_2+36.98 with 236.98 in O3 Not what your original post requested. I want to come up with "NPL_48+29" Gord Dibben MS Excel MVP On Wed, 10 Feb 2010 09:33:01 -0800, jpowell111 wrote: Thanks for your patience. Your formula is correct. What I need for this station is NPL_0+48.29. If the station was 236.98 then I need it to appear as NPL_2+36.98. In these examples the "+" relates to 100 (1+00) as a "," relates to 1000 (1,000). Thank you again. "Ashish Mathur" wrote: Hi, I could not understand that - could you kindly explain again with an example. My formula should give you the answer as NPL_48+29 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... Thanks. This is so close but it places the + where the . usesd to be instead of two places in front of it. I am working on stationing where 100' would be 1+00.00. Is there a way to move the + over? "Ashish Mathur" wrote: Hi, Try this =N3&"_"&INT(O3)&"+"&MID(O3,SEARCH(".",O3,1)+1,2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... I want to join the values of two cells with different formatting. Example: "E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenate numbers and text while holding a custom number for
Thank you Ashish,
You nailed what I asked for in my original post, unfortulately, as Gord pointed out to me, I messed up my request. Thank you for your help. "Ashish Mathur" wrote: Hi, I could not understand that - could you kindly explain again with an example. My formula should give you the answer as NPL_48+29 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... Thanks. This is so close but it places the + where the . usesd to be instead of two places in front of it. I am working on stationing where 100' would be 1+00.00. Is there a way to move the + over? "Ashish Mathur" wrote: Hi, Try this =N3&"_"&INT(O3)&"+"&MID(O3,SEARCH(".",O3,1)+1,2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... I want to join the values of two cells with different formatting. Example: "E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Concatenate numbers and text while holding a custom number for
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... Thank you Ashish, You nailed what I asked for in my original post, unfortulately, as Gord pointed out to me, I messed up my request. Thank you for your help. "Ashish Mathur" wrote: Hi, I could not understand that - could you kindly explain again with an example. My formula should give you the answer as NPL_48+29 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... Thanks. This is so close but it places the + where the . usesd to be instead of two places in front of it. I am working on stationing where 100' would be 1+00.00. Is there a way to move the + over? "Ashish Mathur" wrote: Hi, Try this =N3&"_"&INT(O3)&"+"&MID(O3,SEARCH(".",O3,1)+1,2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jpowell111" wrote in message ... I want to join the values of two cells with different formatting. Example: "E3" = NPL and "O3" is a number 48.29548562458 formatted to appear 48+29. I want to come up with "NPL_48+29" but can't seem to concatenate the cells without losing the format. All I can get is: "NPL_48.29548562458" How do I get the format "##0+#0.00" to carry over? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| custom number format code using letters and numbers | Excel Worksheet Functions | |||
| custom format cell with text and numbers | Excel Discussion (Misc queries) | |||
| Custom number format codes for negative numbers | Excel Discussion (Misc queries) | |||
| Custom Number Format Text | Excel Discussion (Misc queries) | |||
| How to format text and numbers as custom | Excel Discussion (Misc queries) | |||
Linear Mode
