Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting text that begins with "the" or "a"
I'm trying to sort a list in Excel and one of the columns is a list of
titles. When I sort I want it to ignore the words "The" and "A" at the beginning. (for example, I want "The Piano" to be with the Ps not the Ts). I'm about to throw my computer across the room because I can't figure it out and can't find anything in the Help sections that gives me the right answer. I'm not the most savvy with Excel, but this really shouldn't be that hard. Help me. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting text that begins with "the" or "a"
<I'm not the most savvy with Excel, but this really shouldn't be that hard.
Well, Excel wasn't exactly made to do this kind of job, but you are right; it can be done. Are you sure there will not be instances where two words should be skipped? Or "The" not skipped if the 3rd word is as special one? Anyway, make a list of all your skip words in a column. I had mine in H1:H3 (The, A and One) and the titles in A1:A7 In B1, I put this formula: =IF(ISERROR(FIND(" ",A1)),A1,IF(ISNA(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),$H$1:$H$3,1,FALSE)),A1,RIGHT(A1,LEN(A1)-FIND(" ",A1)))) And copied it down It skipped The, A and One, not other first words like All Now you can sort on column B -- Kind regards, Niek Otten "kcg727" wrote in message ... I'm trying to sort a list in Excel and one of the columns is a list of titles. When I sort I want it to ignore the words "The" and "A" at the beginning. (for example, I want "The Piano" to be with the Ps not the Ts). I'm about to throw my computer across the room because I can't figure it out and can't find anything in the Help sections that gives me the right answer. I'm not the most savvy with Excel, but this really shouldn't be that hard. Help me. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting text that begins with "the" or "a"
Here is one option. Assuming the titles are in column A, create a helper
column that you will sort by and drag down the formula =IF(LEFT(A1,2)="A ",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1,4)="The ",RIGHT(A1,LEN(A1)-4),A1)) Remaining in the helper column will be the titles stripped of The{plus space} or A{plus space} wherever they appear at the start of the title. Then do the sort on the helper column. Steve "kcg727" wrote in message ... I'm trying to sort a list in Excel and one of the columns is a list of titles. When I sort I want it to ignore the words "The" and "A" at the beginning. (for example, I want "The Piano" to be with the Ps not the Ts). I'm about to throw my computer across the room because I can't figure it out and can't find anything in the Help sections that gives me the right answer. I'm not the most savvy with Excel, but this really shouldn't be that hard. Help me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dates and text | Excel Discussion (Misc queries) | |||
Cell text based on 4 condition test | Excel Worksheet Functions | |||
Sumproduct if column begins with text | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |