Hi,
You can modify your formula as =CONCATENATE("FY09-Q4",CHAR(10),"
(",TEXT(SUM(A2:A4),"0,000"),")") .
Regards,
Deepak
On Tue, Jun 8, 2010 at 8:31 PM, Sharath Sambrani <
sharath.c.sambr...@gmail.com> wrote:
> thanks Paul,
>
> it worked amazingly!
>
> :)
>
> On Tue, Jun 8, 2010 at 8:19 PM, Paul Schreiner
> <schreiner_p...@att.net>wrote:
>
>> the =Text() function diplays numbers in different formats.
>>
>> replace your Round() function with:
>>
>> text(round(sum(a2:a4),0),"#,###")
>>
>> =CONCATENATE("FY09-Q4",CHAR(10),"
>> (",TEXT(ROUND(SUM(A2:A4),0),"#,###"),")")
>>
>> curiously enough,
>> you SHOULD be able to use:
>>
>> ="FY09-Q4" & CHAR(10) & " (" & TEXT(ROUND(SUM(A2:A4),0),"#,###") & ")"
>>
>> but for some reason, it doesn't "evaluate" the char(10) properly...
>>
>> interesting...
>>
>> Paul
>>
>> ------------------------------
>> *From:* sharath sambrani <sharath.c.sambr...@gmail.com>
>>
>> *To:* MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
>> *Sent:* Tue, June 8, 2010 7:22:00 AM
>> *Subject:* $$Excel-Macros$$ Need help on CONCATENATE and SUM function
>>
>> Hi,
>>
>> i am trying to use sum function in concatenate function. i want to
>> concatenate 'text' + 'sum of a range' + 'text'
>>
>> i want to use this in a column heading, say cell A1.
>>
>> cells A2, A3 and A4 has number values.
>>
>> in cell i want to display as:
>>
>> FY09-Q4
>> (4,464)
>>
>> FY10 should be displayed in the first line and (4,464) which is the
>> sum of A2:A4. however when i use the following formula:
>> =CONCATENATE("FY09-Q4",CHAR(10)," (",ROUND(SUM(A2:A4),0),")")
>>
>> it displays as follows:
>>
>> FY09-Q4
>> (4464)
>>
>> i need the comma after 1000th digit to be displayed.
>>
>> does anyone have idea how to deal with this?
>>
>> thank you in advance.
>>
>> sharath
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> HELP US GROW !!
>>
>> We reach over 7000 subscribers worldwide and receive many nice notes about
>> the learning and support from the group.Let friends and co-workers know they
>> can subscribe to group at
>> http://groups.google.com/group/excel-macros/subscribe
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> HELP US GROW !!
>>
>> We reach over 7000 subscribers worldwide and receive many nice notes about
>> the learning and support from the group.Let friends and co-workers know they
>> can subscribe to group at
>> http://groups.google.com/group/excel-macros/subscribe
>>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
