You could use the macro below to remove leading and trailing single quotes
from data.

In case you are not familiar with macros, here are some instructions to
insert the macro and run it.

NOTE: Make a backup copy of your workbook in case the macro does not do what
you expect.

Open the workbook where you want the single quotes removed.

Ensure that you have permitted macros to run. Click on the Microsoft Office
button (Large button top left of screen)

Select Excel Options (Towards bottom right of dialogue box)

Select Trust Centre-> Trust Centre Settings-> Macro Settings-> Set ‘Disable
all macros with notification’ and check the box ‘Trust access to the VBA
project object model’ and then close all of the options the dialogue boxes.

Alt/F11 will open the macro editor (VBA Editor).

Select menu item Insert. Then select Module. A large white area appears on
the right of the screen.

Select and copy the macro below. (Ensure you copy from the line:-

Sub Replace_Single_Quotes()    to     End Sub)

Paste the macro into the VBA Editor.

Edit the following line near the top of the macro and insert your worksheet
name between the dounble quotes:-

Worksheets("Sheet1").Select

The lines which appear in green in the VBA editor are comments just to let
you know what the macro is doing. I have tested for the leading and trailing
single quotes so that you do not have characters deleted if there just
happens to be some data which does not have the quotes.

Change windows back to the Excel workbook. (Alt/F11 toggles between
Worksheet and VBA Editor)

Press Alt/F8 to view the macro dialogue box. Select the macro and then
select Run.

Note that it is essential to format the cells as text.

To remove the macro from the workbook:-

Alt/F11 to bring up the VBA editor.

Right click on Module1 in the left side pane.
Select ‘Remove Module1’
Click on ‘No’ in the warning dialogue box.
Click on the X in top right corner to close the VBA editor.

Sub Replace_Single_Quotes()
Dim rng1 As Range
Dim c As Range

Worksheets("Sheet1").Select

'Apply variable to the used range
Set rng1 = ActiveSheet.UsedRange

'Format the used range as text
rng1.NumberFormat = "@"

'The following processes each cell in the used range
For Each c In rng1

   'Test that cell is not empty
   If Len(Trim(c)) > 0 Then
   
       'Test if leading single quote exists
       If Left(c, 1) = Chr(39) Then
           'Remove leading single quote
           c = Mid(c, 2)   'Replace from 2nd character
       End If
       
       'Test if trailing single quote exists
       If Right(c, 1) = Chr(39) Then
           'Remove trailing single quote
           c = Left(c, Len(c) - 1)
       End If
   End If
Next c
       
End Sub

Regards,

OssieMac

That's an interesting approach, and it will probably work.  It just seems
kind of klugey.

The fact that Excel tells me "12 changes made" and yet it doesn't change
anything, and if I do the change over and over, it will tell me "12 changes
made" over and over, and yet not change anything... this has to be a bug in
Excel 2007.

David