Remove ' from text columns

  • Thread starter jackBlack via OfficeKB.com
  • Start date
J

jackBlack via OfficeKB.com

There has to be a way to do this but I am missing it somewhere. I have
exported some info from Access and now every cell contains a ' at the
begining of the cell. I know how to remove that from cells with numbers in
it but can't figure out the text cells. Any help?
 
M

Mike H

Hi,

This will remove the first character
=MID(A1,2,LEN(A1))

Drag down

If this leaves you with leading spaces try this
=TRIM(MID(A1,2,LEN(A1)))

If you really mean a ' at the start of each cell try this
=MID(A1,FIND("'",A1,1)+1,LEN(A1))


Mike

Mike
 
T

Thomas [PBD]

JackBlack,

After a quick google search I have found the following VBA code, if you wish
to use it.

Sub DeathToApostrophe()
Dim s As Range, temp As String
If MsgBox("Are you sure you want to remove all leading apostrophes from
the entire sheet?", _
vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub
Application.ScreenUpdating = False
For Each s In ActiveSheet.UsedRange
If s.HasFormula = False Then
'Gets text and rewrites to same cell without the apostrophe.
s.Value = s.Text
End If
Next s
Application.ScreenUpdating = True
End Sub

Source:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=109
 
Top