Remove leading character

[billfinn]

Good afternoon!
I pulled several thousand formulas into a spreadsheet and brought them in with the ' front of the =. Now that I have modified the formulas and am ready to calculate them all I need to get rid of the '. I've tried a dozen or more methods of doing this and have failed every time. I would really appreciate it if someone could point me in the right direction to take care of this
I can't replace the ' with nothing because most of those formulas reference other worksheets and that would really make a mess. I tried adding a separate column to remove the character and failed there too.
Any and all input is very much appreciated!
thanks,
Bill

 

[shg]

Replace '= with =

 

[JoeMo]

Can you post some examples that illustrate the spectrum of formulas that are preceded by '?

 

[billfinn]

SHG, I tried using Replace - Replace comes back with a message - Moscosoft Excel cannot find a match
Thanks much for your response!
Bill

 

[billfinn]

JoeMo,
Here are some examples. Interestingly when I paste them here there is no leading '. Maybe a clue of some kind. I appreciate any suggestions you can offer
[TABLE="width: 1790"]
<tbody>[TR]
[TD]=IF(AND('Quick Quote'!C22="NO",'Quick Quote'!C25="NO",'Quick Quote'!C28="NO"),0,IF('Quick Quote'!E134="Grace Vycor 6-Inch",'Panelized Walls'!O78, ""))[/TD]
[/TR]
[TR]
[TD]=IF('FRONT END'!A185="","",'FRONT END'!A185)[/TD]
[/TR]
[TR]
[TD]=IF(B135=0,0,'FRONT END'!B185)[/TD]
[/TR]
[TR]
[TD]='FRONT END'!A197[/TD]
[/TR]
[TR]
[TD]=IF(AND('Quick Quote'!F32="8'",'Quick Quote'! C148="2x4"), 'Quick Quote'!F97, IF(AND('Quick Quote'F32="8'", C148="2x6"),'Quick Quote'! F98, IF(AND('Quick Quote'!F32="9'", C148="2x4"), 'Quick Quote'!G97, IF(AND('Quick Quote'!F32="9'", C148="2x6"), 'Quick Quote'!G98))))+If('Quick Quote'I32="3 1/2 FJP", 'Quick Quote'!F111, If('Quick Quote'I32="5 1/4 Speed Base", F112, If('Quick Quote'I32="1x6 FJP", 'Quick Quote'!F113)))[/TD]
[/TR]
[TR]
[TD]=IF('Quick Quote'!C21="Ranch",0,IF(AND('Quick Quote'!F32="8'", 'Quick Quote'!C149="2x4"),'Quick Quote'!F97, IF(AND('Quick Quote'!F32="8'", 'Quick Quote'!C149="2x6"), 'Quick Quote'!F98, IF(AND('Quick Quote'!F32="9'", 'Quick Quote'!C148="2x4"), 'Quick Quote'!G97, IF(AND('Quick Quote'!F32="9'", 'Quick Quote'!C149="2x6"), 'Quick Quote'!G98))))+If('Quick Quote'I32="3 1/2 FJP",'Quick Quote'! G111, If('Quick Quote'I32="5 1/4 Speed Base", 'Quick Quote'!G112, If('Quick Quote'I32="1x6 FJP", 'Quick Quote'!G113))))[/TD]
[/TR]
</tbody>[/TABLE]

 

[billfinn]

Update - I tried copying a dozen or so formulas into a word doc, then copy pasted them back in my sheet and no more leading'. Copy Pasting into a different spreadsheet doesn't work. I at least np have a way to do a bunch at one time instead of one by one.
Thanks,
Bill

 

[Joe4]

Let's check to see if that ' is really there.
Let's say that the first example of this is in cell D2. Then enter this formula in any blank cell and see what it returns:
=LEFT(D2,1)

If it returns "=", then it means the ' is not really there, and the formulas were probably entered in as Text.
It is really easy to fix that column. Just select the whole column, go to the Data menu, select "Text to Columns", and click "Finish".
This should make all these formulas to work as formulas, and not text.

So this is a qyuick and easy way to fix all of that. Just note that you can only apply Text to Columns one column at a time. So if you have multiple columns like this, you will need to do each one indivudually. If you have a LOT of columns like this, you could set up VBA code to do the Text to Columns on all of them.

 

[billfinn]

Joe4,
Thanks much for your reply!
I tried the LEFT command and it gave me the =. I tried Text to columns and it caused several formulas that were working OK to stop working. I truly don't have time to chase that one down today but it is on the agenda for Monday. I've been doing big swaths of formulas with the copy to word and back to excel and that is working ok for now.
Thanks,
Bill

 

[Joe4]

I tried the LEFT command and it gave me the =. I tried Text to columns and it caused several formulas that were working OK to stop working. I truly don't have time to chase that one down today but it is on the agenda for Monday. I've been doing big swaths of formulas with the copy to word and back to excel and that is working ok for now.

It shouldn't stop formulas from work. I wonder if it is accidentally delimiting some of those formula.
Try the same steps, but instead of going directly to "Finish", choose the "Delimited" option and hit "Next".
Then on the next tab, unselect all the delimit selections, pick the "Other" option, and pick some value that is not being used in any formula (maybe a tilde or a pipe symbol).
Then click Finish, and see if that works.

If not, please post the formula that was working that stopping working, so we can see what it looks like and try to determine what might be going on.