Solved Sort cells with (almost) identical sentences alphabetically

Whitebox / MINE
November 12, 2015 at 02:12:26
Specs: Windows 7, e6850/4GB
I have a column which has sentences like the following:

This is a sentence (Small)
This is a sentence (Large)
This is a sentence (Medium)
This is another sentence (Small)
This is another sentence (Large)
This is another sentence (Medium)

...etc


I need to be able to highlight all the cells above, and sort them so they now look like this:

This is a sentence (Small)
This is a sentence (Medium)
This is a sentence (Large)
This is another sentence (Small)
This is another sentence (Medium)
This is another sentence (Large)

Without the sentence, it's easy. If a cell only said Small/Medium/Large, I could just use Excel's Sort By Custom List option, add the words manually, in the order I want... but as long as that sentence is in front of them, it doesn't work. The sentence has to stay though, I just need to order them according to the keyword at the end of the sentence.

Any ideas?


See More: Sort cells with (almost) identical sentences alphabetically

Report •


#1
November 12, 2015 at 07:03:32
✔ Best Answer
Use a Helper column and copy the last word, and sort on the helper column.

With your data like:

                 A
1) This is a sentence (Small)
2) This is a sentence (Medium)
3) This is a sentence (Large)
4) This is another sentence (Small)
5) This is another sentence (Medium)
6) This is another sentence (Large)

In cell B1 enter the formula:

=MID(A1,FIND("(",A1)+1,SUM(FIND({"(",")"},A1)*{-1,1})-1)

Drag down

Do your sort on both column A & B
Delete column B

MIKE

http://www.skeptic.com/


Report •

#2
November 12, 2015 at 11:06:37
This is just what I need, thanks!

However, the example I used covers most of what I use. There's still one or two exemptions to that rule I'm hoping you can help me with. Sometimes, there's words that come after the brackets.:

This is a sentence (Small)/and stuff
This is a sentence (Large)/and stuff
This is a sentence (Medium)/and stuff

Is there a formula that can simply give me the first word that comes after the first "(" bracket?


Report •

#3
November 12, 2015 at 11:12:44
I have a question


How do you make the Custom List create this sort order?

Small
Medium
Large
Small
Medium
Large

I can get:

Small
Small
Medium
Medium
Large
Large

or any other order for the three words, but I don't see how to separate them into the "groups" you show. Teach me something. ;-)

message edited by DerbyDad03


Report •

Related Solutions

#4
November 12, 2015 at 11:20:39
Try this formula to extract the characters between the parentheses:

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
November 12, 2015 at 11:22:57
Is there a formula that can simply give me the first word that comes after the first "(" bracket?

Did you even try the formula I suggested?

MIKE

http://www.skeptic.com/


Report •

Ask Question