I exported a querry in access to an excel worksheet. all text has a leading
apostrophy. I am unable to remove the apostrophy with replace function even
though i can manually edit it out. I am also unable to remove the apostrophy
with the lotus transition navigation toggle in the options menu. Sort also
functions incorectly in this worksheet. when I sort a list of these cells,
empty cells with leading apostrophies sort first then blank cells then cells
with text. text always sorts after blanks even if i manually delete the
leading apostrophies. Other spreadsheets seem to function normally. This is
driving me nutts please help.

Highlight the area you want to fix, then run this macro. SAVE YOUR WORKBOOK
FIRST!

'/================================?
Sub GetRidOfApostraphe()
Dim cell As Range
Dim rngTextCells As Range

'only look at cells with text in them
Set rngTextCells = _
Selection.SpecialCells(xlTextValues)

For Each cell In rngTextCells
cell.Value = _
Right(cell.Value, Len(cell.Value))
Next cell

Set rngTextCells = Nothing
End Sub
'/================================?


HTH,
Gary Brown


"MardiL" wrote:

I exported a querry in access to an excel worksheet. all text has a leading
apostrophy. I am unable to remove the apostrophy with replace function even
though i can manually edit it out. I am also unable to remove the apostrophy
with the lotus transition navigation toggle in the options menu. Sort also
functions incorectly in this worksheet. when I sort a list of these cells,
empty cells with leading apostrophies sort first then blank cells then cells
with text. text always sorts after blanks even if i manually delete the
leading apostrophies. Other spreadsheets seem to function normally. This is
driving me nutts please help.

Thanks GB, for taking the time to actually read my message and for
overlooking the screwed up subject line. Your macro worked very well it
solved the apostrophy problem and the sort order problem.

MardiL

"Gary Brown" wrote:

Highlight the area you want to fix, then run this macro. SAVE YOUR WORKBOOK
FIRST!

'/================================?
Sub GetRidOfApostraphe()
Dim cell As Range
Dim rngTextCells As Range

'only look at cells with text in them
Set rngTextCells = _
Selection.SpecialCells(xlTextValues)

For Each cell In rngTextCells
cell.Value = _
Right(cell.Value, Len(cell.Value))
Next cell

Set rngTextCells = Nothing
End Sub
'/================================?


HTH,
Gary Brown


"MardiL" wrote:

I exported a querry in access to an excel worksheet. all text has a leading
apostrophy. I am unable to remove the apostrophy with replace function even
though i can manually edit it out. I am also unable to remove the apostrophy
with the lotus transition navigation toggle in the options menu. Sort also
functions incorectly in this worksheet. when I sort a list of these cells,
empty cells with leading apostrophies sort first then blank cells then cells
with text. text always sorts after blanks even if i manually delete the
leading apostrophies. Other spreadsheets seem to function normally. This is
driving me nutts please help.