Edit / Compute over a large range...

[Al Gillis]

My spreadsheet has hundreds of formulas (lots of
concatenates and lots of vlookups) I want to convert to
fixed values. I'd like to do something easy like select
the range, press F2, Edit and then F9, compute and Volia! -
hundreds of fixed values! Unfortunately the F2/F9
sequence only works on single cells, one at a time.

I've tried "Edit, Copy" followed by "Edit, Paste Special,
Values" and then "Edit, Paste" but that drops an
apostrophe ("'" for text) in front of every cell
containing text (numerics are OK) which won't work in the
downstream use of this spreadsheet. (This effect doesn't
happen in other spreadsheets - I don't know why it happens
in this one!)

So how do I convert these formulas to values easily,
hopefully all in one swell swoop?

Thanks!

Al

 

[Norman Jones]

Hi Al,

Try:

Tools | Options | Transition | Uncheck Transition Navigation keys

Then do your Copy | Paste Special.| Values.

 

[Al Gillis]

Thanks for your quick reply!

but my Transition Keys box was already unchecked!

Al

 

[Norman Jones]

Hi Al,

but my Transition Keys box was already unchecked!

In that case, select all the cells that you want to convert (Ctrl-A for all
cells):

Edit | Copy | Edit | Paste Special | Values | OK

 

[Al Gillis]

Well, that key/mouse sequence (Edit,Copy; Edit, Paste
Special, Values) certainly converts the formulas to fixed
values, but it also inserts the apostrophe into cells
containing text. And that's what I'm trying to get rid
of - that apostrophe!

Thanks for your contined interest!

Al

 

[Norman Jones]

Hi Al,

but it also inserts the apostrophe into cells
containing text. And that's what I'm trying to get rid
of - that apostrophe!

If the Transition Keys option is not selected, you can remove the
apostrophes using :

Edit | Replace | In the Find What box type an apostrophe |

Leave the Replace with box blank | Select Replace All

 

[Guest]

Hi Norman...

Thanks for your help on this vexing problem. The idea to
replace the offending apostrophe with a null character was
a good one! I've disconvered, however, that my apostrophe
is something different than simply an ASCII character in
the cell - it must be related to something in the
worksheet formatting. And in other worksheets I've used
to simplify the problem is sometimes occurs and sometimes
doesn't! I'll keep digging in that "paste, special,
values" stuff. Thanks! Al