Hi,
I am creating a 'Data Entry' I am cataloging my record collection and want to sort by artist. The problem is when I have a band name that starts with the word 'The'. When I sort 'The' will start at 'T'.
I need to bee able to ignore the first word in these cells as and when it appears.
Thank you,
Rob
Try this way:
The band name ==>> Band name, The
most of services doing it just because of "The"
Hi Sandy,
This is an alternative if there is no solution.
Many of the site that I belong to have "The" first and they sort as intended, so there must be a solution somewhere.
Thank you for you support,
Rob
Create helper, hidden column with: Band name, The and sort by this column
so there will be three columns, two visible as you want and one hidden to sort
but of course you can use any formula to do this
would be fine if you will attach example excel file
Last edited by sandy666; 10-07-2017 at 07:17 AM.
Hi Sandy,
So how can I add a sample to this thread?
Create a helper column:
=IF(LEFT(A1,4)="The ",MID(A1,5,255),A1)
Then sort the data using this helper column. Finally, delete the helper column.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
But do try the solution at post 5, first...
Hi Glen,
Tried that but I wasn't successful.
What are you talking about - attaching a sample file, or my suggestion?
I've solution but with your version of Excel it doesn't work becaue Ex2007 doesn't support PowerQuery
but maybe you can see result if it is that what you want to achieve
Thank you for your support Sandy
To attach an Excel file to your post,
- click Go Advanced,
- scroll down until you see Manage Attachments,
- click that and select Browse,
- select your file and click Open,
- click Upload and you will see your attachment below Upload Files from a website
- click Close this window,
- click Submit reply
After that you should see attachment in your post
Here is a sample of mine, showing the various steps to go through.
It's always easier with examples.
You are welcome
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
@Robert_Ham
use @[screenname] because we don't know to whom you are type
Thanks for rep but IMHO Glenn was more helpfull
Thanks for the Rep, Robert... I thought that I was invisible today, as there were no comments from you. I guess I was "seen", though.
Hi,
If I wanted to add another word to "The", how would this be done?
I want to add "Mr." so that I can still organise by first name.
The name written is 'Mr. Herbert Payne' and I want to sort it as 'Herbert Payne' leaving the "Mr." in front of 'Herbert'
Yep. use this instead:
=IF(LEFT(C2,4)="The ",MID(C2,5,255),IF(LEFT(C2,4)="Mr. ",MID(C2,5,255),C2))
Perfect
Thank you Glenn.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks