Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
no functions resolve when format of cells is text
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
no functions resolve when format of cells is text
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "laura_in_abq" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
no functions resolve when format of cells is text
laura_in_abq wrote...
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
no functions resolve when format of cells is text
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) -- Thanks, Shane Devenshire "laura_in_abq" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
no functions resolve when format of cells is text
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 wrote: 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. -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
no functions resolve when format of cells is text
Didn't understand the find/replace suggestion... what do you put in the
"replace" field after the equal sign. "RagDyer" wrote: 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "laura_in_abq" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
no functions resolve when format of cells is text
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
no functions resolve when format of cells is text
Nothing !
= sign replaced with = sign ... then click "Replace All". -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "laura_in_abq" wrote in message ... Didn't understand the find/replace suggestion... what do you put in the "replace" field after the equal sign. "RagDyer" wrote: 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "laura_in_abq" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text format in spreadsheet cells | Excel Discussion (Misc queries) | |||
Performing arithmetic functions to cells with number and text | Excel Discussion (Misc queries) | |||
format cells when containing specified text | Excel Discussion (Misc queries) | |||
format cells having text | Excel Discussion (Misc queries) | |||
Format text across cells | New Users to Excel |