Tech Support Guy banner

sorting in excel

1692 Views 9 Replies 6 Participants Last post by  ckaas
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.
Not open for further replies.
1 - 10 of 10 Posts
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.

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.
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
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.
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.
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.

>> 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.

*(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
See less See more
cheers all! thats exactly what i needed!

Christian K.
1 - 10 of 10 Posts
Not open for further replies.