Hi - I am updating a workbook sent to me by someone else and the formulas I enter do not calculate but appear as text. It doesn't help if I change FORMAT CELLS from Text. I have checked TOOLS, OPTIONS, CALCULATION and that is set to automatic so I am at a bit of a loss as to why it won't calculate... Can anyone suggest anything else I ought to try...?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
EXCEL - Formulas appear as text and do not calculate
- Thread starter RachieD
- Start date
AnotherHiggins
Technical User
After you've changed the format from Text to General, press [F2] to get into one of the formula cells. Now press enter. Does that change it?
If not, press [Ctrl]+[~] (The key at the top left of an American keyboard - to the left of the "1/!" key).
That is the keyboard shortcut to toggle Tools > Options > View > Formulas.
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
If not, press [Ctrl]+[~] (The key at the top left of an American keyboard - to the left of the "1/!" key).
That is the keyboard shortcut to toggle Tools > Options > View > Formulas.
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
This is your clue:
"It doesn't help if I change FORMAT CELLS from Text"
If they are already formatted as text, changing them to something else won't do anything
Format them as general and then run the following macro
Sub Convert()
For Each c In ActiveSheet.UsedRange.Cells
If Left(c.Value, 1) = "=" Then
c.Value = c.Value
End If
Next
End Sub
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
"It doesn't help if I change FORMAT CELLS from Text"
If they are already formatted as text, changing them to something else won't do anything
Format them as general and then run the following macro
Sub Convert()
For Each c In ActiveSheet.UsedRange.Cells
If Left(c.Value, 1) = "=" Then
c.Value = c.Value
End If
Next
End Sub
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
- Thread starter
- #4
The macro does the same as F2 > Enter, it just does it for all the cells without you having to manually change them all one by one
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
AnotherHiggins
Technical User
I use this macro often enough that I have it mapped to a button on a custom toolbar:
I think that should be faster because there's no looping.
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
Code:
Sub ConvertTextAndFormulasToNumbers()
Selection.Value = Selection.Value
End Sub[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
A macro isn't necessary.
I frequently get that, and there are three methods I have had success with. Pick one.
1. Highlight the column of "formulas that show as text". Do the Data>Text-To-Columns, choose "General" when you get that option, then "Finish". It sees a formula, and guesses that it's a formula, and evaluates it as a formula!
2. Change the formatting to "General". Hit F2 to go into Edit mode. Hit the "Home" key to go to the beginning, and erase the single-quote. Hit Enter.
3. Pull down Edit>Clear>Formats. Then press F2 and continue as above.
I frequently get that, and there are three methods I have had success with. Pick one.
1. Highlight the column of "formulas that show as text". Do the Data>Text-To-Columns, choose "General" when you get that option, then "Finish". It sees a formula, and guesses that it's a formula, and evaluates it as a formula!
2. Change the formatting to "General". Hit F2 to go into Edit mode. Hit the "Home" key to go to the beginning, and erase the single-quote. Hit Enter.
3. Pull down Edit>Clear>Formats. Then press F2 and continue as above.
All of the above (except no.1) would be pretty inefficient for large amounts of data
A macro requires 1 click whereas text to columns requires several.
For regular issues with this, a macro can save a lot of time...
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
A macro requires 1 click whereas text to columns requires several.
For regular issues with this, a macro can save a lot of time...
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
- Status