Automatic Calculations of Text Material

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rebecca - 23 Feb 2008 08:23 GMT

I have a problem that I am unable to solve by reading the help file. I have
text material in a column that contain "-", "?", "{", "*", "/", and so on (I
formatted the column as text). Excel 2007 occasionally automatically tries to
calculate the contents of this column and / or cells (for example, when I
make some changes to the text in certain cells), which is unnecessary because
as I wrote above the contents of the cells are pure text. How can I turn off
these calculations for the entire sheet (or at least certain columns)? Yes, I
know this is Excel and that is what a spreadsheet is primarily used for, but
is it possible to turn this feature off?

Reply to this Message

Gary''s Student - 23 Feb 2008 12:13 GMT

Precede each entry in the column with an apostrophe (single quote).

Signature

Gary''s Student - gsnu200770

> I have a problem that I am unable to solve by reading the help file. I have
> text material in a column that contain "-", "?", "{", "*", "/", and so on (I
[quoted text clipped - 5 lines]
> know this is Excel and that is what a spreadsheet is primarily used for, but
> is it possible to turn this feature off?

Reply to this Message

Rebecca - 23 Feb 2008 15:32 GMT

OK, and thanks, Gary's Student. However, this will involve a lot of tedious
insertions (though possible with search and replace), because there are many
entries in certain columns that contain only text. Is there any way to
automatic this apostrophe insertion? And isn't there an option that I can
click or unclick rendering the entire column "calculation free," so to speak?

> Precede each entry in the column with an apostrophe (single quote).
>
[quoted text clipped - 7 lines]
> > know this is Excel and that is what a spreadsheet is primarily used for, but
> > is it possible to turn this feature off?

Reply to this Message

Gary''s Student - 23 Feb 2008 17:21 GMT

Select the cells (or an entire column and run the macro:

Sub becky()
Set r = Intersect(Selection, ActiveSheet.UsedRange)
For Each rr In r
rr.Value = "'" & rr.Value
Next
End Sub

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1.    ALT-F8
2.    Select the macro
3.    Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Signature

Gary''s Student - gsnu200770

> OK, and thanks, Gary's Student. However, this will involve a lot of tedious
> insertions (though possible with search and replace), because there are many
[quoted text clipped - 13 lines]
> > > know this is Excel and that is what a spreadsheet is primarily used for, but
> > > is it possible to turn this feature off?

Reply to this Message