Single quote problem!

[Sergei]

Hi everyone,

I have a problem with single quote in front of a value
wthat was exported from Access and viewed in Excel. Does
anyone you know how to remove a single quote? I have tried
some of Excel build-in functions but it does not help...

Millions of thanks!

Sergei

 

[Roger Govier]

Hi Sergei

Either use the Replace function
Edit=>Replace=> Replace what ' Replace with leave blank

or
=SUBSTITUTE(A1,"'","") (that is " ' " and "")
Change range to suit and copy down for range of cells with offending single
quote

 

[Dave Peterson]

A couple more ways:


Select an empty cell
copy it
Select your range to fix
Edit|paste special|check Add

But if you wanted to do it lots of times, maybe a macro would be more
convenient:

Option Explicit

Sub testme()

Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no text constants found"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = myCell.Value
Next myCell

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm