sorting in excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

ckaas

Thread Starter
Joined
Feb 3, 2003
Messages
133
in excel 2002, if i have a list of names and titles, such as:

John Doe Mailings
Sarah Jones Bazaar
Terrence McLeod Mailings
...

is there a way to have excel sort according to what title the people are associated with? thanks,

Christian K.
 

cybertech

Retired Moderator
Joined
Apr 16, 2002
Messages
72,115
So you want to sort by the last notation in the line, like Mailings and Bazaar?

Here's what I'd do. Using the next column over put the words Mailings or Bazaar, remove the existing Mailings and Bazaar then sort by the new column.

I'm sure there is an Excel guru who could make a fancy macro to get you there too.

:D
 
Joined
May 14, 2003
Messages
379
All you have to do is highlight the columns, may it be two or three depending if you used one column for First name and another column for their Last name and the third as their title. Once highlighted, click on Data then Sort. In the Sort by drop down menu select the column letter that corresponds to the location of the Title information.
 

cybertech

Retired Moderator
Joined
Apr 16, 2002
Messages
72,115
It looked to me like there was only one column containing the entire string.

I could be wrong, I was once, but it's been so long ago I can't remember what I was wrong about

:D :) :D
 
Joined
Aug 5, 2003
Messages
47
It ain't pretty, but here's a formula to retrieve the title, assuming its always the 3rd word (followed by the second space):

=RIGHT(B4,LEN(B4)-(FIND(" ",B4,(FIND(" ",B4)+1))))

where B4 is the name/title data.

Hope this helps.
 
Joined
Aug 5, 2003
Messages
47
Continued: - sorry, just re-read the initial post:

Then, copy that formula down beside your list of names, select the data column and the new title column, and do a Sort based on the titles coulmn.
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
For Coop:

=RIGHT(B4,LEN(B4)-(FIND(" ",B4,(FIND(" ",B4)+1))))

There. Now it's pretty. Tho I personally thought it was gorgeous just the way it was.

:D
 
Joined
Aug 30, 2003
Messages
2,702
>> assuming its always the 3rd word
>> (followed by the second space):

Darn! Used to have something that used a built-in function to reverse a text string ; but seems to be lost, so I had to go looking again.

=RIGHT(A1,LEN(A1)-MAX(ROW(INDIRECT("1:"&LEN(A1)))
*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")))

will give you everything after the last space, regardless of # of spaces.

NB - array formula ; use CTRL+Shift+Enter, *not* plain Enter.

If you're interested in the reverse thing, try this UDF from John Walkenbach (enter in a plain VBA module, then access via Paste Function) -

Function Reverse(InString) As String
Dim i As Integer
Dim StringLength As Integer
Reverse = ""
StringLength = Len(InString)
For i = StringLength To 1 Step -1
Reverse = Reverse & Mid(InString, i, 1)
Next i
End Function
 

ckaas

Thread Starter
Joined
Feb 3, 2003
Messages
133
cheers all! thats exactly what i needed!
fantastic.

Christian K.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top