Business Intelligence Specialist

William Peck

<div>
Try this.<br />
<br />

<ol type="1">
<li>Select the cell.</li>
<li>Format the cell as &quot;General&quot;</li>
<li>Delete the &quot;=&quot; at the beginning of your formula, and hit Enter.</li>
<li>Insert the &quot;=&quot; back in the formula at the beginning.</li>
</ol>
</div>


Ryan Chong

<div>
If you have a bunch of formulas that are being displayed as text, you don't need to select each cell and convert it to a working formula--you can do all of them in one fell swoop.<br><br><ol><li>Make sure your formula cells are formatted as anything other than Text</li><li>While they are still selected, open the Home...Find &amp; Select...Replace menu item. Put = in both the "Find what" and "Replace with" fields.&nbsp;</li><li>Click Replace All</li></ol><br>The third step forces the conversion of formulas from text into working formulas.
</div>


<div>
Jackie - that did the trick (but I should have thought about that ... <br />
<br />
Martin - that worked!<br />
<br />
byundt - that worked too!<br />
<br />
Thanks all!
</div>


<div>
<div class="content wysiwyg-content">
Background:<br />
I tried this at home and at work, same result, this formula just shows up as text (2nd row)<br />
=IF(F4=&quot;&quot;,0,1)<br />
<a href="https://filedb.experts-exchange.com/incoming/2020/04_w15/1449174/Excel-formula-not-working.png" target="_blank" title="Excel formula not working (5 KB)"><img alt="Excel formula not working" class="file-block lazyload" style="max-width: 413px;" data-src="https://filedb.experts-exchange.com/incoming/2020/04_w15/1449174/Excel-formula-not-working.png" /></a><br />
When typing the formula, Excel gives the hint info for the formula but it just comes back as text (2nd row)<br />
<br />
The problem is with the file itself, which is exported from Qualtrics. All fields are text, even if blank. So if I COUNTA 200 rows, 100 of which appear blank, the result is &quot;200&quot;<br />
<br />
If I insert a column in the middle of the file, it's still all text, the formula doesn't work<br />
<br />
If I go beyond the last column of data, the formula works!<br />
<br />
NOW FOR MY QUESTIONS.<br />
1. How can I convert a column to be &quot;clean&quot;, such that the formula works? <br />
2. Why does it export this way (from Qualtrics)?<br />
<br />
This would be easier than going to beyond the last column, copy back, etc.<br />
<br />
I do have a workable solution, but would like to understand.<br />
<br />
It's a big thing because I'm always downloading from Qualtrics and then doing data analysis in Excel.
</div>
</div>


Excel-formula-not-working.png

Background:
I tried this at home and at work, same result, this formula just shows up as text (2nd row)
=IF(F4="",0,1)


When typing the formula, Excel gives the hint info for the formula but it just comes back as text (2nd row)

The problem is with the file itself, which is exported from Qualtrics. All fields are text, even if blank. So if I COUNTA 200 rows, 100 of which appear blank, the result is "200"

If I insert a column in the middle of the file, it's still all text, the formula doesn't work

If I go beyond the last column of data, the formula works!

NOW FOR MY QUESTIONS.
1. How can I convert a column to be "clean", such that the formula works? 
2. Why does it export this way (from Qualtrics)?

This would be easier than going to beyond the last column, copy back, etc.

I do have a workable solution, but would like to understand.

It's a big thing because I'm always downloading from Qualtrics and then doing data analysis in Excel.

Excel formulas not working, it's not reading it as a formula, only as text

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.