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.

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.

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.

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.

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.

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.

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.

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.


.

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.

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.