Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Formulas displaying as text

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"


Post your answer or comment

comments powered by Disqus
I'm using the "CountA" formula throught my spreadsheet, and it works fine in
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.

I have a macro which updates the files that some cells are linked to. It copies a formula from a workings sheet to the reporting sheet, then removing the = sign at the front, and the quote marks at the beginning and end. So far, so good.

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

Using Windows XP and Excel 2002. Importing data from SAP. I enter a formula and sometimes show the results of the formula, sometimes show the formula itself.

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.

Hi,
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?

Just sometimes, and especially if I have downloaded data from a website as xls, even if I re-format cells to "General" let's say, when I type in a formula this displays as the formual rather than working and providing the result. What do I need to change?

I enter a formula in a cell and the cell shows the formula as text. The formula is valid, the "view formulas" option is turned off. Don't know if there is some sort of hidden formatting that is causing this. Other formulas on the worksheet are functioning, but as soon as I enter the cell and make any modifications, the formula shows up as text again.

Thanks for any help.

Ok so I have rows of formula. In order to quickly edit the formula I did a find and replace to take out the = at the beginning of the cells so I have formula that is now listed as text like 'Q:/XXXX'. I want to turn this into a formula by adding an = at the start so it reads ='Q:XXXX' but do this in bulk without having to manually enter the = into each cell.

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?

I have a worksheet with 3 columns of data: description, price, qty. I want to output the Description data in 2 columns so as to use fewer sheets of paper like this:

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

Hi,

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

Is there a way to have excel display a number as text? For example: 5 as Five or 155 as One Hundred Fifty Five

Thanks in advance for any suggestions!

I am entering numbers in a spreadsheet but when I try and sum them the
destination cell shows the formula as text eg =+E1+E2+E3 instead of the
value. I have formatted the cells as numbers

Hi, can anyone help with the following?

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

Hello All

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

I am having trouble with a macro I have been using for a long time. One of the things this macro does is enter an equation into each row in a set of data. Just today, seemingly out of nowhere, these equations now show up like text (as if there was a ' preceding the equation, but the ' character is not there). If I then click to edit the cell, then exit the cell without even changing anything, it resolves correctly as an equation.

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!

hi all

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

Hello

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

I am using a command button to create a new worksheet and name it
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!!

Hi,

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

Hello,

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.

I want to leave say 2 numbers (such as 0002 or 0012) as text in two cells. I
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?

Hello,

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!

When I export data, in this case check detail, the check number is displayed as text. IE it is alligned on the left and is formatted as text. If I change the format of the cell to General, it still is viewed/display as text (alligned to the left). However if I edit the contents of the cell by selecting F2 and then press enter it will then display the correct format and align to the right.

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?

Hello,
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!!

This has driven me insane for years! I type =a14+a15 and the cell display is
=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.