I have an Excel formula reading data from a column. The data in that column is sometimes a date-like format, such as '10-11'.
Despite the fact that I've ensured that column is text formatted - and all values display correctly as plain text, not reinterpreted as dates - the formula is basically reinterpreting them as dates in the reference. I need a way to force the formula's cell reference to interpret the cell as text. I tried TEXT(A1, '@') but it doesn't work - it gives the numeric value of the date. Short answer: When referring to number-like (or date-like) text values in a formula, don't use them in a place in the formula where Excel is expecting a number. Long answer: Even if the source column is formatted as text and the values in the source column are truly entered as text (and not numbers, including dates), Excel may automatically convert text values to numbers (including dates) when you reference them in a formula if you use them in a place where Excel is expecting a number (or date). For example (assuming US date formats), in a blank worksheet:.
Conquer some of the most daunting features in Microsoft Excel once and for all. In this version of his popular course, designed specifically for Excel for Mac users, author Dennis Taylor demystifies some of the most challenging of the 300+ formulas and functions in Excel, and shows how to put them to their best use. Hide Zero Values in Excel for Mac 2011. This article will hide any zero values in your spreadsheet, regardless of whether you entered a value of zero into a cell, or a formula that was being calculated in the cell resulted in a value of zero. The only exception would be if you format a cell as “Text”.
Set the format for column A to Text. In cell A1, enter the value 10-11. In cell B1, enter the formula =T(A1). The T worksheet function returns the supplied value if it is text. Otherwise, it returns an empty string.
The result of the formula in cell B1 should be 10-11, indicating that the value of A1 is text, not a number or date (in which case the result would be an empty string). In cell C1, enter the formula =A1. In cell D1, enter the formula =T(C1). The result should also be 10-11, indicating that the value of the formula in C1 is text, not a number or date.
This shows that you can (sometimes) use a text value that looks like a number (or date) in a formula and have Excel treat it as text (which is what you want). In cell E1, enter the formula =A1+0. The result will be 40827. This is the numeric value of the date October 11, 2011.
This shows that you can (sometimes) use a text value that looks like a number (or date) in a formula and Excel will automatically convert it to a number (which is what you observed) if you use it in a place (like on either side of the + operator) where Excel is expecting a number. Just add zero to the input! I was having a similar problem where I had a list of numbers with a text prefix (like FOO-1, FOO-25, FOO-979) but I just wanted the number part (1, 25, 979) so I could run another formula off of that. I was using SUBSTITUTE to replace the text portion with blank, but my other formula using these numbers was coming up with bogus results. I ended up making my formula like this: =SUBSTITUTE(B1:B10,'FOO-',')+0, and now the ISNUMBER is saying TRUE where before it was saying FALSE. In my case, I have a form worksheet that is used by dealers to ad parts and have it calculate the final cost; it references a locked 'products' sheet.
The problem is that I had no way of controlling what they entered. Products can be like: 101 = A true number 7-2009 = Reads as date 7-5601-RT = TEXT/NUMBER reads as both number or text (NOT SOLVED YET) CP6072CD = reads as plain text I have most of this figured out; the only one that isn't is the one that reads as both text/Number. In case anyone is looking for a similar solution, i did the following: I created three additional columns to test and display the three different cases: 'NUMBER', 'DATE', 'TEXT'.
What is the keyboard shortcut or menu icon that allows me to see the formulas for a worksheet instead of the values in the cells for a worksheet for Excel for Mac 2008. I have spent 20 minutes on google, macRumors, and Mactopia and can not find the answer. Perhaps this feature does not exist for the mac version. I wouldn't be surprised as I am growing very frustrated with Office for Mac. Excel in my opinion is very inefficient. (Enough of that).
I desperately need to know how to toggle between values and formulas for a worksheet. In Microsoft Excel 2007 (for vista or xp or earlier versions) the keystroke is CTRL `. What is it for Excel for Mac 2008.
Please help as I am pretty frustrated and need to find this.