Have formula treat value as text, not numeric

Question

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.

Solution 1

Brian Camire's answer explains why, but here's a worksheet function that will work for you. Note that you have to create that numeric array in it based on how long the longest string will be. It's an array formula, so when you first enter it you have to hit CTRL-SHIFT-ENTER, then you can click and drag it down the column.

=LEFT(A1, MATCH(FALSE, ISNUMBER(VALUE(MID(A1, {1,2,3,4,5}, 1))),0) - 1)

Solution 2

To insert a value into a cell and have it not be auto-formatted, and just treated as text, you can enter it like this:

=("cell data")

eg:

=("[phone]")

Solution 3

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:

1. Set the format for column A to Text.

2. In cell A1, enter the value 10-11.

3. 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).

4. In cell C1, enter the formula =A1.

5. 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).

6. 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.

Solution 4

When you use &"", Excel converts the results of a formula to text (like *1 would convert to numbers).

Thus, you can use this formula:

=TEXT(A1;"jj-mm")&""

Solution 5

If you put a single quote in front of the text in the cell, it should be represented as text on all references:

'10-11

View more solutions

Comments

[lcdservices]

this didn't work. the formula is still interpreting the cell as a date converted to a number.

[lcdservices]

this didn't work. it partially worked for the specific data, but the result was: "jj-10". the bigger issue is that it breaks other data. the data in this cell includes formats such as: "10-11" "104-2" "134-45" "45B" "6413-A". my need is pretty simple -- the formula I use works fine, so long as the data is interpreted as simple text. but Excel is annoyingly treating the 2 digit - dash - 2 digit data as a date, which breaks the formula.

[lcdservices]

that worked beautifully -- thanks! i'll need to read up on array formulas. i'm not familiar with them.

[Lance Roberts]

@Brian, I avoid array formulas whenever I can, but they can be critical some times. They basically let you use arrays (ranges) of values as parameters, and loop through all the values.

[Andrew]

but now there is a single quote in your cell data, how to get this to work without having the quote appear in your data?

[Chris Gregg]

On my version of Excel (mac:2011) it works fine, and there isn't any quote in the data.You can also try: ="10-11"