#### billfinn

##### Board Regular
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### shg

##### MrExcel MVP
Replace '= with =

#### JoeMo

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

#### billfinn

##### Board Regular
SHG, I tried using Replace - Replace comes back with a message - Moscosoft Excel cannot find a match
Bill

#### billfinn

##### Board Regular
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
 =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, "")) =IF('FRONT END'!A185="","",'FRONT END'!A185) =IF(B135=0,0,'FRONT END'!B185) ='FRONT END'!A197 =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))) =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))))

<tbody>
</tbody>

#### billfinn

##### Board Regular
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.

Last edited:

#### billfinn

##### Board Regular
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.
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.

Last edited:

Replies
11
Views
2K
Replies
6
Views
302
Replies
2
Views
286
Replies
13
Views
498
Replies
4
Views
981

1,195,654
Messages
6,010,935
Members
441,575
Latest member
JOHNNY18031

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back