HELP!!!!:How do I sort by a word in a phrase

L

Legacy 55384

Guest
Mr. Excel
I have a set of varieties that I want to sort by a trait of that variety for example
25550 flat seed corn
2920 plateless seed corn with poncho 1250
25550 flat seed corn with poncho 250
8396 Roundup Ready plateless seed corn
2920 plateless seed corn Yieldgard plus

with a couple of other variations, what I want to do is group together
all varieties with poncho 1250, Roundup ready, yieldgard plus, poncho 250, etc. no matter what else is in the cell
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
hi - welcome to the board!

in outline, the approach I would take is as follows:

1) Set up a 2 column table that relates each search target with a category vaule:
STRING | CATEGORY
poncho 1250 | 1
Roundup ready | 1
yieldgard plus | 1
poncho 250 | 1
KP Fertaliser | 2
Misc Product | 2

...etc

2) Set up a formula that searches for the strings & returns the category as a new value in your source table

3) Sort on the new vlaues.

...for specifics, tell us more about your data (how much, where in the file...), how many categories there are etc...
 
L

Legacy 55384

Guest
This is what I've got so far but I can't get the trait out =MID(N5,IF(CODE(MID(N5,1,1))>57,1,IF(CODE(MID(N5,2,1))>57,2,IF(CODE(MID(N5,3,1))>57,3,4))),2) =LEFT(N5,FIND(O5,N5)-1) =RIGHT(N5,LEN(N5)-FIND(O5,N5)-1)

This pulls out all of the words from the numbers, however it doesn't take words away from words
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Can't you use "text to columns"?
Highlight the cell you want the text separated then In the menu bar select Data>TextToColumns


This will separate all text in cell into separate columns so you can sort how you wish.

Michael
 

WrongClick

New Member
Joined
Jan 3, 2006
Messages
41

ADVERTISEMENT

In VB you can use the Instr() function to find one string within another.
 
L

Legacy 55384

Guest
SOrry, kind of a rookie to the seperation of words, i'm not sure what you meanby VB, and the Text to column doesn't completly work because the phrases in the cells are many different lengths, I want to seperate at each space, but it varies per cell, the whole row is not the same length.

Thank you for all the help, it is getting me closer
 
L

Legacy 55384

Guest
Ok, Text to columns works somewhat, but would prefer to be able to pull out a phase, not each individual space
 

Watch MrExcel Video

Forum statistics

Threads
1,119,270
Messages
5,577,114
Members
412,768
Latest member
klig
Top