no functions resolve when format of cells is text

[laura_in_abq]

The purpose of this worksheet is analyzing content of strings of telemetry
displayed as hex characters,in particular parsing and comparing . So I've
set the cell format for each cell to "text". Because otherwise hex string
012e3, will be displayed by Excel as 1.20E+4.

The problem: If
content of cell A1 is DEF987
and
content of cell A2 is =A1
then
A2 displays exactly the three characters, =A1 ... rather than the expected
DEF987. In other words the function in A2 is not resolving, but just
displaying the function. Ditto with other functions, e.g., =MID(A1,1,2); or
=SUM(1,100); or TODAY(). I have Excel 2003 SP3.
thanks.

 

[RagDyer]

You're having this problem because the cells you're entering your formulas
into were *PRE*-formatted to Text.

You can fix it several different ways, here's a couple:

If the "bad" formula cells are in a column, you can simply open and close
TTC.
Select the cells, then, from the Menu Bar:
<Data> <TextToCoklumns> <Finish>

If the cells are random, use "Edit & Replace".
Select the cells, then, from the Menu Bar:
<Edit> <Replace>
In the "Find What" box, enter an equal sign ( = ),
In the "Replace With" box, enter an equal sign ( = ) -YES, same = in both,
Then click <Replace All>.

 

[Harlan Grove]

The purpose of this worksheet is analyzing content of strings of telemetry
displayed as hex characters,in particular parsing and comparing .  So I've
set the cell format for each cell to "text".  Because otherwise hex string
012e3, will be displayed by Excel as 1.20E+4.

The problem: If
content of cell A1 is DEF987
and
content of cell A2 is =A1
then
A2 displays exactly the three characters, =A1 ... rather than the expected
DEF987.  . . .

This is EXACTLY how the Text number format is intended to work, AS
STATED IN ONLINE HELP. So Excel is functioning in this respect EXACTLY
as it should.

If A1 were hex, then leave A2's number format General or 0 or any
other sensible number format. The formula =A1 in cell A2 would then
evaluate as DEF987 as text, and if A1 contained the text 12E4, this
formula in A2 would evaluate to 12E4 as text. Excel only tokens like
12E4 to 12*10^4 when you enter 12E4 as a constant or when you use
"1eE4" as an arithmetic operand. You only need to format the cells
containing telemetry data with number format Text.

 

[ShaneDevenshire]

Hi,

I am probably misreading your question, but if A2 is displaying =A1 then you
might try choosing Tools, Options, View, and uncheck Formulas. Or you can
press Ctrl ` (hold down Ctrl and press the key to the left of the 1 key near
the top left of your screen, this is a single quite character)

 

[Dave Peterson]

My bet is that the cell with the formula evaluated correctly at least once. But
then you changed the formula and the cell's format became text.

Saved from a previous post.

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type: =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted). I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.

 

[laura_in_abq]

Didn't understand the find/replace suggestion... what do you put in the
"replace" field after the equal sign.

 

[laura_in_abq]

Dave's suggestion seems just right for my application... a two step solution:
1) set the format of the offending cell to General and 2) press F2 and then
enter to recompute. thanks to all.

 

[RagDyer]

Nothing !

= sign replaced with = sign ... then click "Replace All".