Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MardiL
 
Posts: n/a
Default 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.
  #2   Report Post  
Gary Brown
 
Posts: n/a
Default

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.

  #3   Report Post  
MardiL
 
Posts: n/a
Default

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintaining a default worksheet order Graham Excel Discussion (Misc queries) 4 February 21st 05 09:52 PM
Excel doesn't sort zip codes properly [email protected] Excel Discussion (Misc queries) 4 February 4th 05 01:30 AM
Need to sort dates before 1900 in proper order sandage_2000 Excel Discussion (Misc queries) 3 January 8th 05 04:31 AM
How do I reference and sort a range of numbers in Excel 97? Old Northern Excel Worksheet Functions 3 December 14th 04 04:15 PM
how would i change default user name all excel files learner Excel Discussion (Misc queries) 1 November 29th 04 11:12 PM


All times are GMT +1. The time now is 03:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"