how do I reset the default sort order in excel xp back to blanks .
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. |
All times are GMT +1. The time now is 05:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com