Using TEXT and &TEXT - display numbers with commas, underline text

I'm familiar with the TEXT function, but sometimes a number will display 
incorrectly when I format numbers to display with a comma if 4 digits or 
more. For example, TEXT(F1,"0,0") will display 10 thru 999 correctly and 
displays 1000 correctly as 1,000, but displays 1 thru 9 as 01, 02, etc.  

Also wondering how to format part of a TEXT formula to underline, and bold 
or color selected words.
0
Utf
12/19/2009 11:45:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
908 Views

Similar Articles

[PageSpeed] 58

Maybe you want:

=text(f1,"#,###")
or
=text(f1,"#,##0")

If this doesn't help, maybe you could post the values and what you want to see
for a few examples.


Gary wrote:
> 
> I'm familiar with the TEXT function, but sometimes a number will display
> incorrectly when I format numbers to display with a comma if 4 digits or
> more. For example, TEXT(F1,"0,0") will display 10 thru 999 correctly and
> displays 1000 correctly as 1,000, but displays 1 thru 9 as 01, 02, etc.
> 
> Also wondering how to format part of a TEXT formula to underline, and bold
> or color selected words.

-- 

Dave Peterson
0
Dave
12/20/2009 12:07:00 AM
On Sat, 19 Dec 2009 15:45:01 -0800, Gary <Gary@discussions.microsoft.com>
wrote:

>I'm familiar with the TEXT function, but sometimes a number will display 
>incorrectly when I format numbers to display with a comma if 4 digits or 
>more. For example, TEXT(F1,"0,0") will display 10 thru 999 correctly and 
>displays 1000 correctly as 1,000, but displays 1 thru 9 as 01, 02, etc.  
>

See Dave's response

>Also wondering how to format part of a TEXT formula to underline, and bold 
>or color selected words.

That is not possible with the TEXT function.

That is only possible using a VBA macro to output a text string to the cell,
and format what you wish.  But more information is needed to help you implement
that
--ron
0
Ron
12/20/2009 12:52:29 AM
Reply:

Similar Artilces:

change color in many text box
I have a form which takes information from a query. The information is orizontally aranged in the many text boxes . I would like to know how can i change the background color of the text box on the row by just doubleclicking on that particular row. Give me some suggestions please! ANY IDEEA, "florin" <florin_serban@hotmail.com> wrote in message news:ercv$ytcIHA.5164@TK2MSFTNGP03.phx.gbl... >I have a form which takes information from a query. > The information is orizontally aranged in the many text boxes . > I would like to know how can i change the background colo...

Replacing cell with text
Hi, I want to know if it is possible to do the following: Imagine I have a legend in which I say which text corresponds to whic letters ex: John -> J Harry -> H Susan -> S and what I want to do is when entering J, H or S in a cell, it wil change to the corresponding text. Is there a way of doing it without having to run a macro ? Thanks in advance Pedr ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Go to Tools > AutoCorrect Options (XL2002) and insert new...

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <FrankWinston@discussions.microsoft.com> wrote in message news:7CAF0A61-E8A7-4D18-B035-0D475E9A858C@microsoft.com... > Is there a way to convert a column of text, date values (entered as 81096, > for example...

Could Excel be used as accounting software?
If so, any tutorials. (I don't want to learn or bky quick books) I jus want to keep tabs on business expenses and I would rather not do it o paper -- Freddy_Kruge ----------------------------------------------------------------------- Freddy_Kruger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2962 View this thread: http://www.excelforum.com/showthread.php?threadid=52382 Theoretically yes but practically no. There a lot of other softwares particularly databases that could do a lot than Excel. These include SAP,Oracle etc., Are you saying that excel wou...

OL2000 Text disappears for certain recipients
Hello, There's been a problem the last few days with the Outlook 2000 in the office I work. Certain users will reply to emails, but only most of the recipients will be to read the reply. Some just see the original message, with the reply text at the top completely gone. This is kind of nerve wrecking, since it's intermittent and not predictable. None of the Outlooks are formatted with rich text, and we use an ISP's email service. Any thoughts/suggestions would be greatly appreciated! Oh, and the OS's are either Win 2000 or XP. Thanks! ...

Add item using HTML Status Bar
I want to add a bunch of pictures of items to the HTML status bar and have the item added to the transaction when the cashier clicks on the item. Is that possible? I have the customization guide, I can modify the status.htm file to display the pictures, but I just need the code to add to the HTML file to trigger the adding of the item (again, if possible). P.S. I don't want to use the custom POS buttons (I already know how to add items that way). I almost have it figured out. I added this code to the Status.htm file: function addit() { qsBridge.FireEvent("PerformAddItem...

Change Display Units property with VBA
Hello, I would like to dynamically change the units on my graph within my powerpoint presentation using VBA. The Powerpoint group has site has been down for a couple of days so I have not been able to send my question in. I would think the code might be similar to that in excel so I'm hoping someone can help me here. Can someone help some code that allows me to change the "Display Units" property found under the "Format Axis --> Scale" menu in the graphs? thank You, Jenise In article <1168541545.394773.220220@o58g2000hsb.googlegroups.com>, jwilliam@alte...

XSLT Plain Text is Indented
Hi All, I'm having some trouble getting my XSLT to transform correctly. VS.NET addeds Indentations: <?xml version="1.0" encoding="utf-8" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="text" indent="no" /> <xsl:param name="Name"/> <xsl:template match="/"> Hello <xsl:value-of select="$Name" />, We have received your e-mail, and your question is in the process of being routed to the appropriat...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

User form Default value in text box
Hi all. Quick question. I have a user form in excel which has a text box which i want to have the value defaulted to the current date. I've looked at several posting on this site but haven't gotten it to work yet. Any ideas on how to set the text box with the current date as a default value? Any help or direction is appreciated. Thanks. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201006/1 Hi, Try this way Private Sub UserForm_Activate() TextBox1.Text = Date End Sub -- Mike When competing hypotheses are oth...

LED light box display--Only 11 mm LED slim light box in China! 05-24-07
Edlite is dedicate to develop comprehensive technology on advertising light-box including LED; CCFL; EEFL and FLUORESCENT series.we already sell numerous light boxes around the world for tradeshow booth or display purpose. More detail.please enter into www.edlite.hk,or send mail to jenny@edlite.hk,or add MSN:madamenihao@hotmail,then we would reply to you at the first time. ...

Filtered Rows using Mode Function?
Hi Excel Forum, I am using numeric "filtered" data and I need to find the most frequen / re-occurring values (1st, 2nd, 3rd, 4th, 5th etc.) from the visibl filtered rows. The Mode function seemed likely, but I cannot get it t work with filtered rows. Can you assist with working examples, please: Formula based input direct on worksheet. VBA Macro using Formula. VBA UserDefined Function. Thank you QT -- Message posted from http://www.ExcelForum.com I would use one of 2 methods :- 1. Formula in another column and sort descending :- =COUNTIF($A$1:$A$20,A16) Need to sort out dupl...

problem with fonts using Word as editor #3
All new messages come up with a superscript font. I remove the check mark and go into default, but new msgs keep coming up in superscrip.What is one to do? Leo Leo Kerner <l.kerner@sympatico.ca> wrote ... > All new messages come up with a superscript font. I remove the check > mark and go into default, but new msgs keep coming up in superscrip.What > is one to do? Since you keep asking, I'll reply with my first reaction to all word-as-email-editor problems: don't use Word as your email editor. It's a simple and 100% effective solution to this and a whole host of ot...

Change a cell's value using a button
Hi - I know its possible but have no idea how to do it - How do I set up a cell with two buttons attached to it that increase and decrease the value in the cell? I.e. if I have a cell with 10% in it and hit the "up" button, the number goes up by 0.5% and down by the same amount if I hit the "down" button? I am pretty good in Excel but dont know anything about macros - but Im really not sure where you find this function. Thanks for your help in advance anyone! -- Ashley No macros required. Use a spin button control rather than 2 buttons. Follow these steps: 1. Go to...

Frogs in the pond
Hi, I have this little charting problem. I'm sure someone will have the solution. It's difficcult to explain so I 've posted a picture here www.fransysco.net/frogs/frogs.gif You may have to zoom on your browser to see it perfectly. I have some data in a table and it's figures about frogs in a a pond and about how many arrive in each batch per quarter and how long they survive. The length of survival is measured in Quarters, too. So each quarter there's a batch of new frogs that arrive - usually 2000 in a a batch. And I measure the survival rate of each batch. Now, if I al...

Trouble with contacts backup using Windows Mobile Device Center.
(Also posted in old thread relating to the subject) Hi, I joined the forum specifically for this problem and this is my first post. I'm just going to spare you all the introductory bs and get down to my concerns. And I didn't bother reading past the first page of this thread even though it is apparently like four years old and a solution has probably already been found. Forgive me, but I'm really frustrated because I'm pretty good with computers and I've been at a standstill trying to fix this for about 5 hours now. I'm running Windows Vista Home Editi...

Problem with Executable File generated using Debug Build
Hi All, I am facing a really wierd problem. I am working on a project in which I have implemeted a GUI for controlling laser microscopes. Everything was going fine till I added support for Filter Wheels. The program compiles perfectly fine. Runs perfectly fine if I run it using Microsoft Visual Studios. But When I try to run the program by double clicking on its icon in the debug folder, the program shows that main frame and then hangs. The problem was not there before I added the code for filter wheels. I am placed all the DLL files in the debug folder. Could any body help me out. Regard...

Number formats and fields
Hi All! I have a list of equations in a word document that I need to add a reference (the first equation in the document will be equation A, the second will be equation B, etc). So I need a numbering field. However, when I get to equation 28 I want the format to be AB, then 29 to be AC, not BB, CC like the default A, B, C… numbering gives you. However, I will be adding other equations randomly throughout the document and referring to the equation reference in text paragraphs, so I would like them to auto update when I add another equation in the middle of the document. Doe...

Import Text Wizard
I'm trying to open a .txt file from Windows that starts the Import Text Wizard automatically. All I've managed so far is to actually open it, yet if you open a text file in Excel it starts the wizard itself. Gary ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ I believe that the Text Import Wizard runs automatically from the user interface. You can run it from Data, Text to Columns after the file is opened. -- Jim Rech Excel MVP ...

Images not displaying on page
Hi, Apologies if this is a frequently asked question... PC has Publisher 2003 + SP1 (11.6255.6408) on WinXPPro + SP2, 4Gb RAM Problem I'm having is when I try to open a Publisher document which was not created on the above PC i.e. sent to me from someone else, or created by me on my previous PC. Specifically, any images do not display on the page, or in Print Preview. However, they do appear in the Graphics Manager if I click the Show Thumbnail checkbox, and they do print correctly. If I click View, Pictures on the menubar, the dialog is set to Detailed Display. If I change this, ...

How do I wrap text for formulaes
I have a worksheet with a lot of formulaes - need to print on a single sheet but cannot seem to wrap text them - any ideas will be welcomed. TIA, Naz --- Message posted from http://www.ExcelForum.com/ I'm not sure if this works for formulas, but it certainly works for text. Highlight the cells you need to wrap, in the menu, click "format", "cells", click the "Alignment" tab, then put a checkmark in the box beside "Wrap text". If that doesn't work, someone else will have to come with a different fix. You might have to change your row h...

Transform using DoCmd.RunSQL fails
Hi, my transform is failing with ERROR 2342 , How can I redo this... basically I want my "detect" to be the column headers and "values" as rows.. This run fine in query window but I am trying to automate the transform process DoCmd.RunSQL "TRANSFORM First(test.value) AS FirstOfct_num SELECT test.Sample, test.user FROM test GROUP BY test.Sample, test.user PIVOT test.detect;" data below.. Sample User Detect Value 5442 img Test 1 Undetermined 5442 img Test 2 27.7402 5442 img Test 3 33.0166 Thank you -- Message posted via AccessMonster....

Re: Text fomula
sorry. this is not working on subsequent experiments apologise. R.VENKATARAMAN <$$$$vram26@vsnl$$$.net$$$> wrote in message news:... > try type in H1 > > =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pears"),COUNTIF(D5:D16,"cherr > y"))>=1,TRUE,FALSE) > > your data is in d5 to d16. > > do you get what you want. > > may be tortuous to write .may be bugs in it. try the experiment > > ==================================== > Jack Sons <j.sons@planet.nl> wrote in message > news:#FmG41mEFHA.464@TK2MSFTNGP15.phx.g...

Why use GetSafeHwnd?
Hi I have a pointer to a window. I need to get the HWND of this window. I can do both: HWND hWnd = pWnd->GetSafeHwnd(); ASSERT(hWnd); or simply: HWND hWnd = pWnd->m_hWnd; ASSERT(hWnd); What's the difference? GetSafeHwnd() returns NULL if m_hWnd is not initialized, while accessing m_hWnd directely on an unattached window would be undefined? Surely this is not the case, because a constructor of a window class initialized m_hWnd = 0 I'd think. So what's the difference then? Lisa "Lisa Pearlson" <no@spam.plz> wrote in message news:O7hfidKtEHA.3152@TK2MSFTN...

In MDI, how to display a restored view on a maxmized view?
In MDI, if a view is maxmized, all other views are hide. But I want to display a restored view on the top of the maxmized view, how can I do that? I am not sure if thats possible. You can try calling activateframe on the mdichild window that contains the view. I suspect that the new frame will come in as maximized as well. You can try by resizing the view to cover entire area of mainframe and then activating second frame. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "flyingxu" <flyingxu@discussions.microsoft.com> wrote in message news:20D25211-AC04-4E9A-A034-34F41A60B0F6...