 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006Format>Cells>Number>Text is very tricky!
Hi all, This is something basic and I have only found out today. Oh well, I am still a newbie. Scenario 1 If I format a brand new blank cell as TEXT and key in a number say 2006, the format is text. ISTEXT( ) returns TRUE. Scenario 2 If I key in 2006 to a brand new blank cell which has a default format of general and then format the cell as TEXT, the format is NOT text. ISTEXT( ) returns FALSE. I have always been under the impression that by formatting a cell to text regardless of when, I have got text. This revelation is important to formula writing. For example, in my SUMPRODUCT formula I have to check for "2006" (with quotes) for scenario 1 if I want a match; I have to check for 2006 (without quotes) for scenario 2. COUNTIF doesn't care if I have quotes or not for both scenarios. So, formatting a cell to text *after* I have keyed in a number doesn't make it text? Experts, I appreciate your comments on my "discovery" as I feel that I may still have missed a piece of the puzzle. Also, please let me know if you think of any other similar subtleties. Thanks. Epinn That is because the format as Text only asserts once data has been entered. Try the following: 1. in an un-formatted cell (say A1) enter 1 2. format A1 as Text 3. =ISTEXT(A1) will still show FALSE ! 4. now enter 2 in cell A1 5 the 2 appears as text and ISTEXT now shows TRUE !! Strange & marvelous are the ways of Excel  SignatureGary's Student > Hi all, > [quoted text clipped - 23 lines] > > Thank you for your response. This is more confusing than I have expected ...... Any number that is left aligned is not necessarily text as we can always click the "Align Left" button. Now, I have found another layer. I think the green triangle is more telling i.e. if I switch the feature on. Epinn That is because the format as Text only asserts once data has been entered. Try the following: 1. in an un-formatted cell (say A1) enter 1 2. format A1 as Text 3. =ISTEXT(A1) will still show FALSE ! 4. now enter 2 in cell A1 5 the 2 appears as text and ISTEXT now shows TRUE !! Strange & marvelous are the ways of Excel SignatureGary's Student "Epinn" wrote: > Hi all, > [quoted text clipped - 23 lines] > > The green triangle is not reliable - only good for integers and decimals. A fraction like 1 1/4 will show the triangle while a fraction like 1/4 won't show it. Is there a visual way to tell if the value is text? Thank you for your response. This is more confusing than I have expected ...... Any number that is left aligned is not necessarily text as we can always click the "Align Left" button. Now, I have found another layer. I think the green triangle is more telling i.e. if I switch the feature on. Epinn "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message news:0893A9B8-3985-4348-A4A4-7A098FEFE328@microsoft.com... That is because the format as Text only asserts once data has been entered. Try the following: 1. in an un-formatted cell (say A1) enter 1 2. format A1 as Text 3. =ISTEXT(A1) will still show FALSE ! 4. now enter 2 in cell A1 5 the 2 appears as text and ISTEXT now shows TRUE !! Strange & marvelous are the ways of Excel SignatureGary's Student > Hi all, > [quoted text clipped - 23 lines] > > If you use Format | Cells | Alignment then choose General for horizontal alignment, a numerical 2006 will align to the right, whereas a text 2006 will appear left aligned. This is easier to see if you widen the column, and the technique is particularly useful when importing dates. You can make your SP formula accept both 2006 and "2006" by multiplying by 1 or incorporating VALUE( ). Hope this helps. > The green triangle is not reliable - only good for integers and decimals. A fraction like 1 1/4 will show the triangle while a fraction like 1/4 won't show it. > [quoted text clipped - 49 lines] > > > > Hi Pete, Thank you for dropping by. I was thinking the other day that I haven't "run into" you for a while. Yes, I think I have to add 0 to 2006 or use VALUE( ) so that SP can work consistently. Epinn If you use Format | Cells | Alignment then choose General for horizontal alignment, a numerical 2006 will align to the right, whereas a text 2006 will appear left aligned. This is easier to see if you widen the column, and the technique is particularly useful when importing dates. You can make your SP formula accept both 2006 and "2006" by multiplying by 1 or incorporating VALUE( ). Hope this helps. Epinn wrote: > The green triangle is not reliable - only good for integers and decimals. A fraction like 1 1/4 will show the triangle while a fraction like 1/4 won't show it. > [quoted text clipped - 52 lines] > > > > Hi Epinn, I was on holiday in Canada for a couple of weeks, and since then most of the threads seem to be answered by the time they are displayed in Google Groups, so I've not posted very many this month. Nice to "talk" to you again. By the way, I use XL2000 which doesn't have green triangles, so that is not a very reliable method for checking for text !! <bg> Pete > Hi Pete, > [quoted text clipped - 73 lines] > > > > > > |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.