sorting in excel

1692 Views 9 Replies 6 Participants Last post by ckaas, Sep 27, 2003

ckaas

Discussion Starter · Sep 26, 2003

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.

Save

Status: Not open for further replies.

1 - 10 of 10 Posts

cybertech

· #2 · Sep 26, 2003

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.

Save

Bruce319

· #3 · Sep 26, 2003

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.

Save

cybertech

· #4 · Sep 26, 2003

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

Save

tCoop61

· #5 · Sep 26, 2003

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.

Save

cybertech

· #6 · Sep 26, 2003

Good job, tCoop61

Save

tCoop61

· #7 · Sep 26, 2003

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.

Save

Anne Troy

· #8 · Sep 26, 2003

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.

Save

XL Guru

· #9 · Sep 27, 2003

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

See less See more

Save

ckaas

Discussion Starter · #10 · Sep 27, 2003

cheers all! thats exactly what i needed!
fantastic.

Christian K.

Save

1 - 10 of 10 Posts

Status: Not open for further replies.

Top Contributors this Month