Removing Leading apostrophe in excel formula solution.

[Celticshadow]

Hello

May I ask forum if they could provide a formula to solve the below problem concerning leading apostrophe.


I download data from a horseracing site and part of it contains the horses form figures and some but not all contain an apostrophe before the first number as in the below examples.

'040
'0
'00
'U3P
'P17
'060

If I use control F, Find Replace, Replace ' Replace with then the result is that I lose the first zero so for example the '040 becomes 40 instead of 040. Can anyone suggest a formula to rid the apostrophe and leave the remaining three numbers. If I try to remove them manually I still get the same result with the zero disappearing, so any pointers or help in removing the offending apostrophe and leaving me with the three remaing numbers and/or figures would be much appreciated. Thanks in advance.

Regards

 

[Aladin Akyurek]

Something like...

=REPLACE(A2,1,1,"")

 

[Peter_SSs]

H.. some but not all contain an apostrophe before the first number ..

Try

=IF(LEFT(A2,1)="'",REPLACE(A2,1,1,""),A2)

 

[Rick Rothstein]

Here is another one for you to try...

=REPLACE(A1,1,LEFT(A1)="'","")

 

[Peter_SSs]

If you are looking for a manual method (like you were trying with Find/Replace) ..

1. If the column to the right is not vacant, insert a new column to the right
2. Select your column of data by clicking its heading label.
3. Data ribbon tab -> Text to Columns (in the Data Tools group) -> Delimited -> Next -> Clear all boxes but mark 'Other:' and put ' in the Other box -> Next -> In the Data Preview window at the bottom click in the second column then at the top of that dialog choose 'Text' -> Finish
4. With the original column still selected, press F5 -> Special ... -> Blanks -> OK
5. Right-click on any of the selected cells -> Delete... -> Shift cells left -> OK
Done