Remove ' from text columns
 

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?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200809/1

Remove ' from text columns
 

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

"jackBlack via OfficeKB.com" wrote:

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?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200809/1

Remove ' from text columns
 

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

--
--Thomas [PBD]
Working hard to make working easy.


"jackBlack via OfficeKB.com" wrote:

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?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200809/1