Sometimes when I enter a formula in Excel, it displays the text of the formula instead of the results. Once this happens I can't get it to display correctly. Is this a bug in Excel 2003, or have I inadvertently changed some obscure setting somewhere to display the actual formula instead of the result? If so, how do I change it back?

Simplified example:

Contents of cell A7: 000114 (Account number formatted as text)

Formula in cell b7: =A7

Contents of cell B7 displays "=A7" instead of "000114"

Simplified example:

Contents of cell A7: 000114 (Account number formatted as text)

Formula in cell b7: =A7

Contents of cell B7 displays "=A7" instead of "000114"

- Formula is displaying as text rather than result.
- Excel calculating formula which should be displayed as text!
- Formula displayed as formula not results
- Cell which displays the filter in use as text
- Formulas show as text
- Formulas showing as text
- Convert Formula Written as Text Into Formula
- RESOLVED>>>>Formula seen as text!!!! Arghhhh!!!
- How to get " to show up as text in macro?
- Format numbers to display as text
- Formula appears as text
- Referencing a formula (as text)
- Format a number to display as alternative text
- Equations display as text
- Define formula components as text
- Using Formula result as TEXT for other formulas.
- Date formula results as text
- Deleting Spaces Copying Value As Text . . .PLEASE HELP!
- Converting a Column of Numbers Displayed as Text to Numbers
- To hide the arrow for numbers as text
- Inserting page number as text VBA macro
- Exporting Data formatted as text
- Formulas showing as text
- Basic format problem

all cells but one - in which the formula displays as text. I've reset Tools,

Auditing, copied formatting from cells that display correctly, and copied the

formula from cells that display the result. Nothing is working.

However, the following formula is a problem:

="=COUNTIF('K:FinanceSHAREDDanCenturyManagement ExpensesYear end 31-3-"&IF(Month>3,TEXT(Year-1999,"00"),TEXT(Year-2000,"00"))&""&RIGHT(Year,4)&" "&TEXT(MONTH(Last_Day),"00")&"[Prepayment release - "&LEFT(Month,3)&" "&RIGHT(Year,2)&".xls]Prepayments'!$J$45:$J$54,">0")-1"

All this does is tell Excel where the file is located, based on the month and year I'm reporting on. It works fine using SUM, but COUNTIF doesn't seem to work.

When I paste the formula into the working sheet, I get an error message:

The formula you have typed contains an error, etc.

When I click OK, the 0" towards the end of the formula is highlighted, and the only way Excel will accept this is if I remove the quotes either side of the >0, but then my COUNTIF formula won't work.

I don't understand why this is happening. The =" at the beginning of the formula should tell Excel not to calculate the formula, just display it as text.

I'm tearing my hair out so any assistance would be appreciated!

Neil

Sold - to Ship - to

1100159 4141001 =A2&B2 5241160

1100159 4110050

1100159 4110050

1100159 4110050

Numbers in columns A and B imported from SAP.

Formula in cell C2 is =A2&B2. Yes, I want the concatenation.

Formula in cell D2 is =A2+B2. Yes, I want the addition.

I have checked and do not have Formulas clicked to display in Tools, Options, View.

The numbers in columns A and B were imported from SAP. When first brought into Excel, they were text. I have asked Excel to change to numbers. They are formatted as General.

Any idea how to show the result and not the formula? F2 F9 will do it for the one cell, but there are literally hundreds of cells with the concatenation formula.

And I have reviewed the messages "Formula Display" and "concatenate formula showing as formula, not text" in the archives and tried those solutions without success.

I have never seen this before so I don’t know if this is possible or not...

I require a formula that displays as text the filter criteria which has been applied to a data table.

For instance as an example in the attached I have applied the following filters:

Product:

Energikare and S4

Spacer:

Swiss

Thg/Ann:

Toughened and Annealed

Textured?:

Textured

Georgian:

Georgian and No Georgian

I would there like the following cells to display the following:

Cell C2: Energikare and S4

Cell C3: Swiss

Cell C4: Toughened and Annealed

Cell C5: Textured

Cell C6: Georgian and No Georgian

Basicallly, I would need the cell to display the name if the box is ticked from the drop down filter.

If all products are ticked I would like it to display All Products rather than EnergiKare and Laminated and Other and S4 and Tripple Glazed.

I have uploaded an example file, the formula is needed in the yellow cells.

Can anyone help please?

Thanks for any help.

I tried find and replace 'Q with ='Q but that doesnt work since Excel doesnt recognise the first ' as a character. I then tried using ="=" & CELL and all Excel then did was give me a text output, where as I need the output to be the formula result. Any ideas?

Desc Price Qty (Empty column) Desc Price Qty

Note that the Qty column is empty...the customer fills in values for that on a paper form. Price is filled in by hand by the user. There is also a "separator column" D just for looks.

And so, on the "Output" worksheet, I copy into A1 of Output the value in A1 of the Product worksheet, using =Product!A1, and copy this formula 49 more times down the A column. It works: the output sheet picks up the values from the Product worksheet.

Then, in E1 (that is to say, the 5th column over, to repeat what I had in cols A,B &C and keep a 4th separator column) of the Output worksheet, I enter the formula =product!A50. That would be the next value down from the Product worksheet.

In other words, take the value in A50 from the Product worksheet and put it in cell E1 of the Output sheet. Then, if I copy that 49 times, I should have 2 columns covering 100 items.

Now back in A51 of the Output sheet, I'll copy the value from A101 of the Product sheet...etc. Back and forth until everything is copied

Excel, however, doesn't see the formula as a formula in the "2nd" column of the output sheet....it puts the formula in the cell as though I entered it as straight text and obviously doesn't do the copy. I try formatting the column as "general" and sometimes it works (making the formula act like one) and sometimes it doesn't -it just sits there as a text value in the cell.

What is very strange is that if I copy and paste the formula on the output sheet left-to-right (which defaults to columns 4 to the right of A on the product sheet) Excel doesn't screw it up...it grabs the value from the D column of the product sheet (which of course is empty -Excel returns a 0). When I edit the formula to refer to the correct source column (A), then Excel simply reverts the formula in the cell as text. Crazy!

This particular workbook has about 20 identical product sheets which were made by copying one worksheet multiple times to get the 20. The 20th is the "product" sheet, which sums the values in the qty column of the preceding 19 sheets. All 20 sheets have the products as text in them (they are not derived as formulas). The 21st worksheet is the output sheet.

This has happened occasionally on just one worksheet -Excel just shows a formula as text, even when properly entered with a leading = or + sign. But formatting the formula column as "general" has always resolved the problem....until NOW.

Wassup?

Thanks!

-S

I want to get " to show up as it is in my macro formula.

Eg.

Cells(r + 1, 9).Formula = "=IF(H1=H2,"Yes","No")"

The " in the "Yes" and "No" will not show.

How do I get it to display as text?

thanks

Thanks in advance for any suggestions!

destination cell shows the formula as text eg =+E1+E2+E3 instead of the

value. I have formatted the cells as numbers

I want to pick up a formula (written as text) from one cell so it can be

used in another function? This will then be applied to a range of cells by

filling-down.

For example if I write a function as VLOOKUP(X,Table,3,false) without the =

sign and therefore store it in a cell as text, I want to be able to

reference that cell and apply the function.

Is this possible?

Nelson

I am trying to format a set of cells, A1:A4 that have the following values.

A1=1; A2=2; A3=3; A4=4

I however want the displayed information to show up as "Very Low", "Low",

"High" and "Very High"

If I just type the text in, I cannot find a way to sort them in the

numerical order I like because it sorts alphabetically, so I have to put them

in as numbers to get the sort working. Is there anyway I can have them

display as text or can I leave them as text and build a custom sort list so

that they will sort in the order as above.

Thanks

Any idea why these equations would suddenly be showing up as text? Is there a setting, or some sort of formatting somewhere that I am overlooking?

Here is a snippet of the code that is inserting the equations:

Where TAR increments through many rows. The result I now get, for example, would be a cell displaying the text:

=IF(B107=1,H107*F107,"")

Rather than displaying the result of the equation. Again, this used to work correctly, but all of a sudden this is happening. Any help or tips greatly appreciated!

i have some very long and complex formulas in my Excel workbook and it's extremely difficult to track and debug some of them.

is there a way to define components of these formulas, such as "VLOOKUP(**,***,**,0) as text so that they appear as text in the formula bar but still serve their purpose?

thanks

I have searched many times on google to solve this problem and was unable to find what i am looking for.

I have 2 Sheets of data, and at certain point one cell dependes on the result of another.

The first one is:

=IF( INPUT!$P24=0;"";INPUT!$P2)

With result:

ADOLFO MANUEL GONCALVES DA SILVA

Then... i want the result above, and do a VlookUP and search for another information, in this case de ID number of the coWorker.

The table i use has exactly the same information but instead there is an error.

I wont give you the error as i know its not the formula.

One other thing-

When i wan to use the result as NUMBER, i put "+0".

How do i use the result as TEXT?

I have tryed "+TEXT", "&TEXT". I dont know what to do.

Thank you

Best Regards

with the date it was created. The problem is, I am trying to use a cell (J1)

which contains a date formula. Is there anyway to have a date formula input

the text results in another cell so I can refer to that cell in my code?

J1 = Range("J1")

ActiveSheet.Name = J1

Thanks in advance!!

I am in the middle of trying to convert city names with multiple spaces to only the first 6 characters of the city name.

e.g. Elk Grove Village to be converted to ElkGro

I have been successful in eliminating the spaces in the name using the following formula:

=LEFT(A15,FIND(" ",A15,1)-1)&MID(A15,FIND(" ",A15,1)+1, 255)

The problem I encounter occurs after I copy the value generated by the formula above as text and try to apply the LEFT function to use only the first six letters. Every time I type the LEFT function, only the formula appears and not the value I need.

Any input as to what I can do would be greatly appreciated.

Regards, Gordon

I have a situation where I think an easy macro would do the trick,

however, I am somewhat new to macros. I have a set of numbers that I

import from a different accounting program. The numbers that are

imported, are the accounting codes and they are all in column A and

are seen by Excel as text. I can convert them to a numbers, but the

process is time consuming. Is there a macro that will convert the text

numbers in Column A to actual numbers?

Any help would be greatly appreciated. Thank you in advance.

know I can do in at least 2 ways:

1) type an apostrophe before typing 0002 or 0012, or

2) set the format of the 2 cells as text, then simply enter 0002 and 0012

Problem is I hate to see the green arrow at the top left corner of each of

these 2 cells. When the cell is selected, a small box with a little

exclamation mark and a drop down box appears. If the cursor is placed on the

drop down arrow, it displays a text message: the number of this cells is

formatted as text or preceded by an apostrophe. It is too much, thank you.

I know what I am doing. I have hundreds of such cells that contain numbers

and I want them to display as text. The feature (seems to apply for Excel

2000) is not useful for me.

What should I do to turn it off, that is, to remove the hundreds of green

flag now appearing in my spreadsheet?

I'm making a table of contents, and I would like to make a macro that would insert the page number of where a criteria is met.

i.e: If the criteria (Cell A130 = B131) is met, the page number of where that criteria was met will be displayed as text in cell H6 for example. I already have the macro written for the whole table of contents and such. I'm just trying to look for/figure out what function/terminology is used for that instance (basically something along the lines of; if A130 = B131, then H6 = CurrentPage.InsertPageNumber, which of course isn't the right formatting)

Thank you all!

I have lots of numbers and I do not want to hit F2 and enter for hundreds of cells. Is there an easier way to do this?

I am trying to link a form to a spreadsheet that will be used as an import into Access.

When I tried to create the form however, all my links are showing as Text and not the information that is in the linked cells. How do I get rid of this?

I am trying to set up some combofields on the form to make entry easier.

Here is what I am working with.

Thanks that worked, how I would have ever figured that out by myself is beyond me!!

=a14+a15. Instead of treating it as a function and providing a result I only

see the formula displayed as text within the cell. The above calc was just a

sample and I usually find a way around it but this happens to me more and

more often. Any help would be appreciated..

--

Ray

No luck finding an answer? You could always try Google.