I need to select a group of records from a huge worksheet of data by the suffix of the cell.
An example of the data would be: 108261-2-CO
with "CO" being the item identifier that I need to identify.
A normal record would be like: 108261-2.
Is there a clever way to do this?
Thanks
You could use Text to Columns to separate your entries using delimited and - as your delimiter. If you want to keep the original data intact first copy your data to a new column and then separate that data. Then you can sort on the two letter suffix. Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Seems to have worked CB (Thanks!) but I ran into another gotcha when encountering a data cell like: 108456-1-CO
It text-to-columns it into 3 columns: col 1: 108456 / col 2: 1 / col 3: CO
So now I have two columns of CO data instead of one. Any ideas to ignore that first "-1" dash text-to-column sorting breakout?
So can you give examples of the types of values you might see and what you'd want returned?
108456-1-CO > 108456-1, CO
108456-CO > 108456, CO
108456-2 > 108456-2 ?
Are all suffixes 2 letters? Sounds like Column to Text won't work for you. Once you give me the range of possiblities, we can come up with a formula.
CB:
108456-1-CO > 108456-1, CO YES
108456-CO > 108456, CO YES
108456-2 > 108456-2 ? YES
All suffixes I need to sort on are two digits: CO
Okay, based on what you told me, in Column B
=IF(ISNUMBER(RIGHT(A2,1)+0),A2,MID(A2,1,LEN(A2)-3)) dragged down
and in column C
=IF(ISNUMBER(RIGHT(A2,1)+0),"",RIGHT(A2,2))
Does that work for you?
CB:
You are a pretty smart feller or is it . . . ?
Here are the results that your fomulas returned:
Data: Col A Col B Col C
108261-2-CO 108261-2 CO
108456-CO 108261-2 CO
Thanks a lot!
When you dragged your formulas down, the reference to A2 should have changed to A3, etc. Did it? If it did and you're still seeing
108456-CO 108261-2 CO
try hitting F9
CB:
When you say "dragged" did you mean copied? If so, yes, they changed correctly.
So if 108456-CO is in A3 and the formulas reference A3, how is it coming back with 108261-2 ???? Did you try F9?
CB:
I don't know and F9 does nothing?
Hope you don't mind me chipping in but, if you want to group rather than sort, couldn't you just use an AutoFilter and select "contains" "CO"?
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
TM: That works really nicely too, thanks!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks