I have '234 '005 432 '01 in cells.
Mid(__,1,1), left(__,1) do not work to remove apostrophe. Can only delete apostrophe if I edit cell directly.
Can I edit formula line in a macro.
I believe one could have edited formula in earlier versions of Excel.
Please help thanks

sgool

I assume you mean that you have '234 in one cell, '005 in another, etc.

The ' tells Excel that the characters in the cell should be considered text.

There are a multitude of ways to remove the '. I'll offer 2.

Getting 234 in B1 from '234 in A1

Enter =A1+0 in B1.

Since Excel loves numbers, if you try to perform a mathematical operation on text that Excel can interpret as a number, it will convert the text to a number and perform the operation.

Getting 234 in A1 from '234 in A1

Right-Click any blank cell and chose Copy.
Right-Click any one (or more) of the cells with an ' and choose
Paste Special...Operation - Add.

Same explanation as before - Excel will add 0 to the "text", converting it to a number. What's interesting is that this method will also remove the ' from real text, such as 'Hello, although obviously it won't convert it into a number.

Thank you - it works.
kind regards

sgool