I needed to create a text string which is a formula. I did it and now have
400 entries like the following:
=DATE(2003,10,04)
Each of these entries is a unique date which was converted from the original
Lotus file. But each entry is a text string - not a formula. So the actual
value in the cell is just as shown above - it displays as =DATE(YYYY,MM,DD).
There is an apostrophe at the beginning. I can manually edit each entry and
delete the apostrophe, but that's a pain in the you know what. I can't do a
replace all - replacing apostrophe with nothing - that doesn't work. I need
these entries to be formulas so that they display as the actual Date - and in
addition I want to keep the formula - in other words I don't want to do a
paste special values. Any suggestions?

Enter and run this small macro:

Sub tic_killer()
Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants, xlTextValues)
For Each r In rr
If r.PrefixCharacter = "'" Then
r.Value = r.Value
End If
Next
End Sub

--
Gary''s Student - gsnu200789


"dhstein" wrote:

I needed to create a text string which is a formula. I did it and now have
400 entries like the following:
=DATE(2003,10,04)
Each of these entries is a unique date which was converted from the original
Lotus file. But each entry is a text string - not a formula. So the actual
value in the cell is just as shown above - it displays as =DATE(YYYY,MM,DD).
There is an apostrophe at the beginning. I can manually edit each entry and
delete the apostrophe, but that's a pain in the you know what. I can't do a
replace all - replacing apostrophe with nothing - that doesn't work. I need
these entries to be formulas so that they display as the actual Date - and in
addition I want to keep the formula - in other words I don't want to do a
paste special values. Any suggestions?

Copy, then Paste Special: Values will get rid of the apostrophe. Then do a
Find = Replace = (with the equal sign as the Find and the Replace) to force
it into a formula.

"dhstein" wrote:

I needed to create a text string which is a formula. I did it and now have
400 entries like the following:
=DATE(2003,10,04)
Each of these entries is a unique date which was converted from the original
Lotus file. But each entry is a text string - not a formula. So the actual
value in the cell is just as shown above - it displays as =DATE(YYYY,MM,DD).
There is an apostrophe at the beginning. I can manually edit each entry and
delete the apostrophe, but that's a pain in the you know what. I can't do a
replace all - replacing apostrophe with nothing - that doesn't work. I need
these entries to be formulas so that they display as the actual Date - and in
addition I want to keep the formula - in other words I don't want to do a
paste special values. Any suggestions?

Thanks so much to both of you. Thanks Gary for creating that macro - I'll
put it in my list of macros for future reference.

sb1920alk - Thank you - that did the trick - I don't know how you knew that,
but replacing "=" with "=" converted to a formula. You both have my
permission to take the rest of the day off.

"sb1920alk" wrote:

Copy, then Paste Special: Values will get rid of the apostrophe. Then do a
Find = Replace = (with the equal sign as the Find and the Replace) to force
it into a formula.

"dhstein" wrote:

I needed to create a text string which is a formula. I did it and now have
400 entries like the following:
=DATE(2003,10,04)
Each of these entries is a unique date which was converted from the original
Lotus file. But each entry is a text string - not a formula. So the actual
value in the cell is just as shown above - it displays as =DATE(YYYY,MM,DD).
There is an apostrophe at the beginning. I can manually edit each entry and
delete the apostrophe, but that's a pain in the you know what. I can't do a
replace all - replacing apostrophe with nothing - that doesn't work. I need
these entries to be formulas so that they display as the actual Date - and in
addition I want to keep the formula - in other words I don't want to do a
paste special values. Any suggestions?

Have a look at the INDIRECT function. It takes a text value and converts it
into a reference.

Rob


"dhstein" wrote in message
...
I needed to create a text string which is a formula. I did it and now have
400 entries like the following:
=DATE(2003,10,04)
Each of these entries is a unique date which was converted from the
original
Lotus file. But each entry is a text string - not a formula. So the
actual
value in the cell is just as shown above - it displays as
=DATE(YYYY,MM,DD).
There is an apostrophe at the beginning. I can manually edit each entry
and
delete the apostrophe, but that's a pain in the you know what. I can't do
a
replace all - replacing apostrophe with nothing - that doesn't work. I
need
these entries to be formulas so that they display as the actual Date - and
in
addition I want to keep the formula - in other words I don't want to do a
paste special values. Any suggestions?

That's a really ingenious trick!
I'm currently seeking for something like that for 2 hours!!!
Thanks a lot!!!



non wrote:

Convert Text to a Formula
02-Jun-08

Copy, then Paste Special: Values will get rid of the apostrophe. Then do a
Find = Replace = (with the equal sign as the Find and the Replace) to force
it into a formula.

"dhstein" wrote:

EggHeadCafe - Software Developer Portal of Choice
A Down and Dirty FileSystem Object Page Counter
http://www.eggheadcafe.com/tutorials...-filesyst.aspx