|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 8th, 2010, 11:18 PM
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
February 9th, 2010, 12:07 AM
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
February 9th, 2010, 12:16 AM
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
February 9th, 2010, 06:10 PM
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
February 9th, 2010, 11:22 PM
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
February 10th, 2010, 05:33 PM
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
February 10th, 2010, 06:51 PM
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
February 10th, 2010, 09:49 PM
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
February 10th, 2010, 09:52 PM
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
February 10th, 2010, 11:22 PM
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
