Automatic Calculations of Text Material

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

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?
 
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?
 
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
 
Back
Top