Function acts like text

darleenesther

New Member
Joined
Mar 31, 2009
Messages
7
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!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

New Member
Joined
Mar 31, 2009
Messages
7
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

New Member
Joined
Mar 31, 2009
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

New Member
Joined
Mar 31, 2009
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,140
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top