Format a concantated text string

Sorry if this is boring but I'm stuck.


I have a cell that counts something, it also has a piece of text at the
front of the count but I want the count to be formatted as currency and
it won't.


="Major "&COUNTIF(C:C,"Major")*245
I've tried ="Major "& Format(COUNTIF(C:C,"Major")*245****) but I can't
get it right and think it's a VBA command!


Help please!


Thanks
Dean



-- 
dkso@ntlworld.com
http://www.dkso.co.uk/
http://homepage.ntlworld.com/dkso 


0
dkso (36)
10/13/2005 3:47:43 PM
excel 39879 articles. 2 followers. Follow

3 Replies
1268 Views

Similar Articles

[PageSpeed] 25

Thank you!

Dean
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:OFUpsKB0FHA.1168@TK2MSFTNGP10.phx.gbl...
> Depends upon the currency, but something like
>
> ="Major "&TEXT(COUNTIF(C:C,"Major")*245,"$#,##0.00")
>
> -- 
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Dkso" <dkso@ntlworld.com> wrote in message
> news:zkv3f.551$qX5.441@newsfe7-gui.ntli.net...
>> Sorry if this is boring but I'm stuck.
>>
>>
>> I have a cell that counts something, it also has a piece of text at the
>> front of the count but I want the count to be formatted as currency and
>> it won't.
>>
>>
>> ="Major "&COUNTIF(C:C,"Major")*245
>> I've tried ="Major "& Format(COUNTIF(C:C,"Major")*245****) but I can't
>> get it right and think it's a VBA command!
>>
>>
>> Help please!
>>
>>
>> Thanks
>> Dean
>>
>>
>>
>> -- 
>> dkso@ntlworld.com
>> http://www.dkso.co.uk/
>> http://homepage.ntlworld.com/dkso
>>
>>
>
> 


0
dkso (36)
10/13/2005 4:39:42 PM
Depends upon the currency, but something like

="Major "&TEXT(COUNTIF(C:C,"Major")*245,"$#,##0.00")

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Dkso" <dkso@ntlworld.com> wrote in message
news:zkv3f.551$qX5.441@newsfe7-gui.ntli.net...
> Sorry if this is boring but I'm stuck.
>
>
> I have a cell that counts something, it also has a piece of text at the
> front of the count but I want the count to be formatted as currency and
> it won't.
>
>
> ="Major "&COUNTIF(C:C,"Major")*245
> I've tried ="Major "& Format(COUNTIF(C:C,"Major")*245****) but I can't
> get it right and think it's a VBA command!
>
>
> Help please!
>
>
> Thanks
> Dean
>
>
>
> -- 
> dkso@ntlworld.com
> http://www.dkso.co.uk/
> http://homepage.ntlworld.com/dkso
>
>


0
bob.phillips1 (6510)
10/14/2005 12:25:14 AM
Just to add on a little ..

Think Debra Dalgleish provides some examples at her:
http://www.contextures.com/xlCombine01.html
Scroll down to "Formatting Examples"

And we could also discover the "type" by say, clicking on a cell
pre-formatted as currency, then do a Format > Cells > Custom to see/copy
what appears inside the "Type:" box [to paste as the "format_text" inside
the TEXT(...)]
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


0
demechanik (4694)
10/14/2005 9:05:30 AM
Reply:

Similar Artilces:

How do you convert a string to a double?
How do you convert a string to a double? This does not work: string1 = Right(Left(data(k), 21), 2) double1 = CDbl(string1) It gives a "Type mismatch" error every time. Hi, I think you need to tell us what data(k) is holding but in the meantime isn't string1 = Mid(data(k), 20, 2) the same as string1 = Right(Left(data(k), 21), 2) and a bit more simple -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Philosophaie" wrote: ...

Excel
Text pasted into a cell from Word or other software will keep the formats including carriage returns, line numbers, tabs, etc. Can text be typed into a cell with control characters that will force this type of formating? -- mwaller If I understood your question correctly - you might try hitting Alt+Enter in order to go to a new line in the same cell Micky "mwaller" wrote: > Text pasted into a cell from Word or other software will keep the formats > including carriage returns, line numbers, tabs, etc. > Can text be typed into a cell with control charac...

Format Cell/Row
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I am not sure if this can be done, but is there a way to insert space above ling of text so that it is not flush with the line above it? like an indent, only above the contents of the cell so that there is white space around the cell/line content? <br> I tried centering the text, but I wanted a more uniform look. Nothing such as the paragraph spacing in Word. Adjust the height of the row then use Format> Cells> Alignment> Vertical Alignment, set it to either Bottom or Center. I'm not sure what you mean by &qu...

Separating strings in a field to separate fields
Hi, I have a field that consists of a string value that looks like Item1, Item2, Item3, each item is separated by commas. I want to separate that string value so that each item has its own field. Is there code that will easily do this? Thanks, Jaime On Wed, 5 Dec 2007 12:08:57 -0800 (PST), jseger22@yahoo.com wrote: >Hi, > >I have a field that consists of a string value that looks like Item1, >Item2, Item3, each item is separated by commas. I want to separate >that string value so that each item has its own field. Is there code >that will easily do this? > >Thank...

Make a text box fit the value inside
In my report design view, I have a text box which is filled with blue color. When I view the report in print preview, the box always remains the same size depsite the length of characters of the value. I want the entire value to fit on one line. Values range from two characters to 30 characters in size. Selecting "yes" for "can grow" or "can shrink" for the text box properties does not make a difference. Any help is appreciated. Thanks! *Glen* Glen, Check the Detail Section have you set that to Can Grow = Yes? (I am, of course, assuming ...

PivotTable
Hi, I have a PivotTable with two columns - student names and tuition. I highlighted the **grand total tuition** with fill color. Please note that only ONE cell had this format. However, when I changed the column of student names to a row of student names (by moving the "button"), all the tuition (and not just the grand total) had the fill color. Is this a bug? Is there a way to get around this? I know I can remove the fill color first, change from column to row then put back the fill color. Just wondering if there is a better way. Appreciate advice. Epinn ...

Can I insert excel spreadsheet in word and keep currency format?
In Office 2000 I could insert an excell spreadsheet in word and the currency formats would be retained in the table. Office 2003 does not. I have to format each indiviaual cell. Is there another way to do this? Also allall coumns are inserted instead of the selected columns. ...

How can I use wraparound of text in MS Access 2003?
How can I use wraparound of text in MS Access 2003? Where do you want the text to be wrapped? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "depthwish" <depthwish@discussions.microsoft.com> wrote in message news:A58EF03B-EE30-40F5-829E-B38C00A774B5@microsoft.com... > How can I use wraparound of text in MS Access 2003? For reports, you can use the "can grow" and "can shrink" options to have text boxes expand as necessary (don't forget to set these options for the section as well). The controls on forms a...

how do i convert from .pub to .txt format
Want to use another program to open items in the publication if this is possible. KNOBBY wrote: > Want to use another program to open items in the publication if this > is possible. ========================================= Which version of Publisher? Did you go to...File / Save As...to see the Save options? Also you could copy / paste into a different program and save the resulting file. -- John Inzer MS-MVP Digital Media Experience Notice This is not tech support I am a volunteer Solutions that work for me may not work for you Proceed at your own risk ...

VB.net select column and format
Hi, I am using VB.net 2005 with Excel 2003. I need to select a single column and apply formating to it. Can someone please explain how to do this? Thanks, What sort of formatting do you mean? The basic code would be something like Dim RangeToFormat As Excel.Range Dim WS As Excel.Worksheet RangeToFormat = DirectCast( _ XLApp.Workbooks("Book1.xls"). _ Worksheets.Item("Sheet1"), Excel.Worksheet). _ Range("A1").EntireColumn WS = RangeToFormat.Worksheet RangeToFormat = _ XLApp.Intersect(WS.UsedRange, RangeToFormat) RangeToForma...

Merging text strings into a single cell
HELP!!!! I have created 2 separate columns of text data in a large spread sheet (over 600 rows of data). Now I need to merge the 2 columns together so that the text descriptions from each column are combined together into a single column. If the cells were numeric it would not be a problem (i.e. if C3 were the finished result I was looking for cell C3 would read "=A3+B3. Only problem is Excel simply tells me the formula is invalid because it's text data. Merging the cells of each row simply cuts the text off from the 2nd cell. How can this problem be resolved????? Hi you'...

How to find the string with (utf-8) and no sign but all the same results in MS S
How to find the string with (utf-8) and no sign but all the same results in MS SQL 2005 Example: I want to search string "nhà đẹp" or "nha dep" all the result in "nhà đẹp" Thanks, nhphuong -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201003/1 Use a case insensitive collation; for example Latin1_General_CI_AI wich is both Case Insensitive (CI) and Accent Insensitive (AI) : Select Case when N'nh� d?p' = N'nha dep' then 'Equal' Else 'Not Equal' End as Test1,...

Tiff format problems with Access 2007
We have some databases created in Access 2000 with .tif files embedded to the forms and reports. And we are planning to migrate to Access 2007. We open databases created in Access 2000 with Access 2007 and we get the following error massage: 2114 <database name> doesn't support of the file 'I:\Tmimage\2005\20051028.tif,' or file is too large. Try converting the file to BMP format. Is Access 2007 supports tif format or is there any solution for this problem? The TIFF graphics filter was withdrawn in Office 2003, so many users with tiff images found that their app...

Conditional formatting not quite working right
I have a form that is generated by a query. The query is populated via table. I want all values in the report that are over 1.99% to show up as red. Those values at 1.99% and lower should show up as green. IOW, percent values from 0 to 1.99% are good values and show green. Anything at 2% and over is bad, and should show up as red. I've created conditinal formatting in several different ways, using the options available under conditional formatting. It seems that no matter what I select, greater than, equal to, etc, all the values change to whatever the cond format is suppos...

Summing instances of text across worksheets
Hello, I have a workbook with separate sheets for each month. The sheets contain a list client names (entered exactly the same on each sheet). I need to summarize how many times a client name appears in total. For example, John Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec (=3). I've been trying pivot tables without luck. Hoping someone can help. Thanks in advance! Never mind, I consolidated all the worksheet data into one and the pivot table works fine. Thanks anwyay! "George" wrote: > Hello, I have a workbook with separate sheets fo...

Inserting text in a basic shape
In PowerPoint 2007, I've drawn several basic shapes, then clicked the shapes to add text; however, the text in several of the shapes is very strange...as though it's "outdented" on the first line or indented on the following lines. The lines are all out of alignment, and I've tried everything I can possibly think of but to no avail. Has anyone else experienced anything like this, and if so, do you know how to fix it? I know this will sound weird, but I suspect it's a printer driver issue. Please humor me and try this exact printer driver installed in t...

html formatting
i used to create emails using Frontpage for my business and they need to be in html format. Now I have 2007 and am using Publisher and have been trying for hours to create a email in html format and can't. Does anyone know how to change it to html? I tried the publish as web page but it is all over the page and my pictures don't work. sharnie Create and send e-mail publications by using Publisher http://office.microsoft.com/en-us/publisher/HA102200511033.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Sharnie&...

typing text from top to botton?
Is it possible for the text to be typed from top to botton in publisher? Yessenia wrote: > Is it possible for the text to be typed from top to > botton in publisher? ======================== Try using a narrow, vertical textbox with center alignment. -- John Inzer return e-mail disabled ...

A cell copied onto one worksheet from another worksheet does display same formatting.
I have 2 worksheets in my workbook. On worksheet "A" i have a number in red font with black background in cell B1. On worksheet "B" the contents of cell A1 is =A!B1. This copies the value located in cell B1 of worksheet "A" but not the color formatting. How can that get copied as well? Hi A formula has one single job: to return a value into its own cell. No formats, no copying, no dancing, no fun, only that. -- HTH. Best wishes Harald Followup to newsgroup only please "Mark" <mark@lbrgroup.com> skrev i melding news:32B8AA2D-DA1D-4B3F-AF73-9...

UDF to Convert formula results to text
Can a UDF be used to convert formula results from column A to text in column B? I know that I can manually cut and paste "special" to achieve this, but I want to avoid a manual step. Instead of a UDF use a macro. Change columns(2) to columns(1) to change the column withOUT the need for an additional column. Sub converttovalues() Columns(2).Value = Columns(1).Value End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Don" <don87109@hotmail.com> wrote in message news:%230kPQ8gDKHA.4608@TK2MSFTNGP02.phx.gbl... > Can a UD...

format colors on bar charts by value
I'm working on a Bar chart with 5 bars. What I would like to do is change the color of each bar depending on the number it is graphing. My table is very simple, column A is the data names, column B are the values from 0 to 3. If the value is 1 I would like it to change the color of the bar to red, if it is 2 I'd like it to change to yellow, if it is 3 I'd like it to change to green. Can this be done? Thanks Bill See http://peltiertech.com/Excel/Charts/ConditionalChart1.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "BillC...

Is it possible to Unicode only for menu text...
Hi i am working on application where i need menu text in Arabic. I have done it (VC++ 6)with the following method: I have added... Project-Settings-C++-Preprocessor definitions : _UNICODE & Project-Settings-Link-Category-OutPut : wWinMainCRTStartup Then i have write arabic text in menu enteries, it works fine. But in my application i an using lot of other data types like CString etc. These don't work if i not convert them in unicode. What i want to ask "is there is any way to unicode only the menu resouce not whole the application". Means i don't want t...

Paste Special as Text
I have a small program that drops text from a Word table into Excel using Paste Special as Text. Although Paste Special as Text should insert the contents of the clipboard as text without any formatting this does not appear to be happening as it did with Excel 2000 . Excel 2003 splits the data when it meets a New Line or Carriage Return and drops anything after it down to row below. Any ideas what is causing this behaviour Thanks for your assistance Phil ...

Line up text
I have a column, Members, where sometimes there can be multiple names. I pull a query and then run a report. However, the data entered is all on one line but I want the report to have it appear as a list. For example, the data in the table appears as Jane Doe - Spring 07 and John Doe - Spring 08. How do I get it to appear as follows in my report: Jane Doe - Spring 07 John Doe - Spring 08 My Access skills are basic so the more detailed the better. Thank you in advance. If all have a dash where you want to align on then you can use a calculation. Field_Title: Left(Left([YourField], Inst...

putting text into pictures once in outlook
Hi I frequently want to send photos from outlook. Is thee an easy way of insertng text into pictures, tried text box, but it will not work. thanks tim "Tim" <adslk4at@tpg.com.au> wrote in message news:4241e306@dnews.tpgi.com.au... > Hi I frequently want to send photos from outlook. Is thee an easy way of > insertng text into pictures, tried text box, but it will not work. thanks > tim > Try a good image editing program. Outlook isn't one of those. ...