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

tstruntz

New Member
Joined
Mar 25, 2002
Messages
11
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??
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
yes there is: use the TEXT function:
TEXT(value, formatting code)

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

Marc
 
Upvote 0
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)
 
Upvote 0
Well, that's a different question! I don't think that's possible just using the Concatenate function (or any other function).
 
Upvote 0
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?
 
Upvote 0
If you post your formula, I could try to find out the problem. Don't get discouraged. I think you're almost there.
Marc
 
Upvote 0
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
 
Upvote 0
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!!
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top