Function acts like text

[darleenesther]

Sometimes when I enter a funciton in an excel cell it treats it like text. This happens even when I use the "insert function" option and even after I clear contents. When using the "insert function" option the function box shows the correct value but then I hit 'enter' and my cell displays the function text and not the result.

This happens when I have exported data from Access or SAP or some other source into excel. It does not happen every time but does happen fairly often as in now... and I'm frustrated!

 

[schielrn]

Welcome to the Board!

It is probably the fact that the cell is set to text. Sometimes imports from other things do that because Excel is being sent a message that it is a text column. Clear contents will not change the formatting of the cell if it is set as text.

Hope that helps.

 

[darleenesther]

Wow, thanks for your quick response. I should have mentioned that I also changed the cell format to "general". And it still wouldn't work, it would revert back to text. That being said I just changed it again and now it is working... not sure what I did differently but thank you so much for your response.

 

[schielrn]

Just as a side note, if the formula is showing up as text and the cell is set to text and then changed to general it will not necessarily show the results. YOu would need to click in the cell or press F2 and then press enter to get it to calculate. That is typically how it is done or you can use text to columns if it is more than one cell.

Hope that helps.

 

[darleenesther]

That's a great help I didn't know about the F2 enter. In regards to Text to Columns I only use that to take a column and split the Text into different columns. I don't understand what you're saying in regards to using Text to Columns for multiple columns. Can you explain more?

 

[schielrn]

If you have say A1:A10 as text, as an example, highlight A1:A10 and enter '0005 and press control+enter.

Now that will put 0005 in A1:A10 and it will be text since the leading zeroes are still there.

Now say you want all those to actually be 5 and not have the leading 0.

You can highlight column A and go to data-->text-to-columns and then just press finish and that would change them all to numbers instead of text.

Hope that makes a little sense, but it allows you to take ONE column not multiple columns and change them all from text to a number or you can also do stuff with dates that is very handy.

Say you have 20091225 in a column because that is how it was brought in by SAP. You can click next twice click the date radio button and select the option for YMD and it will convert it to a date for you without using a formula.

 

[darleenesther]

Oh my goodness I am SO excited to try that with the SAP date!! That will save me hours over my lifetime. I had to take off to my sons baseball game so will try it when I get home. Hope this reply posts correctly as I'm sending from my Blackberry. What a great learning experience for my first post. Thank you again.