Concatenate Function ... Help .... Please!!

tstruntz · Mar 26th, 2002, 04:48 PM

I am using the Concatenate function to string togehter text and formatted numbers (number, 2 decimal places and ( ) used for negative numbers). The whole string is a profit/loss discussion and analysis. The numbers I have referenced lose their formatting in the text string (concatenate function). Is there a way to keep the number format, or assign one while using the Concatenate function??

paelinck · Mar 26th, 2002, 04:55 PM

yes there is: use the TEXT function:
TEXT(value, formatting code)

e.g.:
TEXT(99,"000") displays the number as 099.

Marc

etres · Mar 26th, 2002, 04:57 PM

But how do you do it with text Bold and Italics?
If B1 is bold in size 10 and C1 is in italics in size 8, how to keep the formats when D1 has =Concatenate(B1,C1)

paelinck · Mar 26th, 2002, 05:11 PM

Well, that's a different question! I don't think that's possible just using the Concatenate function (or any other function).

tstruntz · Mar 26th, 2002, 05:28 PM

paelinck,

I tried the "Text" embedded in the Concatenate function, but it does not seem to be working. I still cannot get the comma for the thousands place, not can I get the parenthesis for the negative numbers. Have any ideas? Do you know of any other funcations that I may use to accomplish the same thing?

paelinck · Mar 26th, 2002, 05:30 PM

If you post your formula, I could try to find out the problem. Don't get discouraged. I think you're almost there.
Marc

paelinck · Mar 26th, 2002, 05:37 PM

Just another idea: the problem might lie with the format string in the TEXT formula. The best way to get the right format string is to edit the format of one of the cells whose format you want to copy, and to select "Custom". The string that is displayed should occur exactly in the format parameter of the TEXT function (between double quotes).

Marc

tstruntz · Mar 26th, 2002, 05:43 PM

paelinck,

Here is the formula I am using:

=CONCATENATE("Any Company, USA Operations reported a combinded net ",H26, " of ", G26," compared to a budgeted net ",I26," of ",F26," for the month of ",B15)

Where:
H26 = Cell referencing "Income" or "Loss"
G26 = Icome or loss amount (formatted)
I26 = Cell referencing "Income" or "Loss"
F26 = Icome or loss amount (formatted)
B15 = Month/Year

Thank you for all your help!!

tstruntz · Mar 26th, 2002, 05:49 PM

I got it to work!! I embedded the DOLLAR formula within the Concatenate format.

Thank you very much for your time and assistance with this!!

Anne Troy · Mar 26th, 2002, 05:55 PM

It's easier NOT to use the function, but to use the following. At least, I think it's a lot eaier to read. Not that this helps your dilemma.

="Any Company, USA Operations reported a combinded net "&H26"&" of "&G26&" compared to a budgeted net "&I26&" of "&F26&" for the month of "&B15

Mar 26th, 2002, 05:55 PM	#10
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,418	It's easier NOT to use the function, but to use the following. At least, I think it's a lot eaier to read. Not that this helps your dilemma. ="Any Company, USA Operations reported a combinded net "&H26"&" of "&G26&" compared to a budgeted net "&I26&" of "&F26&" for the month of "&B15 __________________ ~Anne Troy Office Articles

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 04:48 PM	#1
tstruntz Join Date: Mar 2002 Posts: 9	I am using the Concatenate function to string togehter text and formatted numbers (number, 2 decimal places and ( ) used for negative numbers). The whole string is a profit/loss discussion and analysis. The numbers I have referenced lose their formatting in the text string (concatenate function). Is there a way to keep the number format, or assign one while using the Concatenate function??

Mar 26th, 2002, 04:55 PM	#2
paelinck Join Date: Mar 2002 Location: Leiden, The Netherlands Posts: 68	yes there is: use the TEXT function: TEXT(value, formatting code) e.g.: TEXT(99,"000") displays the number as 099. Marc

Mar 26th, 2002, 04:57 PM	#3
etres Join Date: Mar 2002 Posts: 4	But how do you do it with text Bold and Italics? If B1 is bold in size 10 and C1 is in italics in size 8, how to keep the formats when D1 has =Concatenate(B1,C1)

Mar 26th, 2002, 05:11 PM	#4
paelinck Join Date: Mar 2002 Location: Leiden, The Netherlands Posts: 68	Well, that's a different question! I don't think that's possible just using the Concatenate function (or any other function).

Mar 26th, 2002, 05:28 PM	#5
tstruntz Join Date: Mar 2002 Posts: 9	paelinck, I tried the "Text" embedded in the Concatenate function, but it does not seem to be working. I still cannot get the comma for the thousands place, not can I get the parenthesis for the negative numbers. Have any ideas? Do you know of any other funcations that I may use to accomplish the same thing?

Mar 26th, 2002, 05:30 PM	#6
paelinck Join Date: Mar 2002 Location: Leiden, The Netherlands Posts: 68	If you post your formula, I could try to find out the problem. Don't get discouraged. I think you're almost there. Marc

Mar 26th, 2002, 05:37 PM	#7
paelinck Join Date: Mar 2002 Location: Leiden, The Netherlands Posts: 68	Just another idea: the problem might lie with the format string in the TEXT formula. The best way to get the right format string is to edit the format of one of the cells whose format you want to copy, and to select "Custom". The string that is displayed should occur exactly in the format parameter of the TEXT function (between double quotes). Marc

Mar 26th, 2002, 05:43 PM	#8
tstruntz Join Date: Mar 2002 Posts: 9	paelinck, Here is the formula I am using: =CONCATENATE("Any Company, USA Operations reported a combinded net ",H26, " of ", G26," compared to a budgeted net ",I26," of ",F26," for the month of ",B15) Where: H26 = Cell referencing "Income" or "Loss" G26 = Icome or loss amount (formatted) I26 = Cell referencing "Income" or "Loss" F26 = Icome or loss amount (formatted) B15 = Month/Year Thank you for all your help!!

Mar 26th, 2002, 05:49 PM	#9
tstruntz Join Date: Mar 2002 Posts: 9	I got it to work!! I embedded the DOLLAR formula within the Concatenate format. Thank you very much for your time and assistance with this!!