Filtering in excel by last two characters that are alphanumeric
So I have a large spread sheet of warehouse locations that I need to sort by the last two digits and I need to filter on the ones that are alpha numeric. Below is the example of what i'm looking at. I need everything that doesn't end with the characters D1,D2,D3 and D4
LOCID
1350010C3
1350010D1
1350010D4
1350011A3
1350011B1
1350011C1
1350011C2
1350011C4
1350011D1
1350011D3
1350011D5
1350012D5
1350013A4
microsoft-excel
edited Dec 13 at 14:47
Scott Craner
11.2k1815
asked Dec 13 at 14:45
Burton
1
add a comment |
So I have a large spread sheet of warehouse locations that I need to sort by the last two digits and I need to filter on the ones that are alpha numeric. Below is the example of what i'm looking at. I need everything that doesn't end with the characters D1,D2,D3 and D4
LOCID
1350010C3
1350010D1
1350010D4
1350011A3
1350011B1
1350011C1
1350011C2
1350011C4
1350011D1
1350011D3
1350011D5
1350012D5
1350013A4
microsoft-excel
edited Dec 13 at 14:47
Scott Craner
11.2k1815
asked Dec 13 at 14:45
Burton
1
1
create another column that has the last two characters, then use that column to do your sort and your filter.
– Scott Craner
Dec 13 at 14:48
add a comment |
So I have a large spread sheet of warehouse locations that I need to sort by the last two digits and I need to filter on the ones that are alpha numeric. Below is the example of what i'm looking at. I need everything that doesn't end with the characters D1,D2,D3 and D4
LOCID
1350010C3
1350010D1
1350010D4
1350011A3
1350011B1
1350011C1
1350011C2
1350011C4
1350011D1
1350011D3
1350011D5
1350012D5
1350013A4
microsoft-excel
edited Dec 13 at 14:47
Scott Craner
11.2k1815
asked Dec 13 at 14:45
Burton
1
So I have a large spread sheet of warehouse locations that I need to sort by the last two digits and I need to filter on the ones that are alpha numeric. Below is the example of what i'm looking at. I need everything that doesn't end with the characters D1,D2,D3 and D4
LOCID
1350010C3
1350010D1
1350010D4
1350011A3
1350011B1
1350011C1
1350011C2
1350011C4
1350011D1
1350011D3
1350011D5
1350012D5
1350013A4
microsoft-excel
microsoft-excel
edited Dec 13 at 14:47
Scott Craner
11.2k1815
asked Dec 13 at 14:45
Burton
1
edited Dec 13 at 14:47
Scott Craner
11.2k1815
asked Dec 13 at 14:45
Burton
1
edited Dec 13 at 14:47
Scott Craner
11.2k1815
edited Dec 13 at 14:47
Scott Craner
11.2k1815
edited Dec 13 at 14:47
Scott Craner
11.2k1815
11.2k1815
asked Dec 13 at 14:45
Burton
1
asked Dec 13 at 14:45
Burton
1
asked Dec 13 at 14:45
Burton
1
1
1
create another column that has the last two characters, then use that column to do your sort and your filter.
– Scott Craner
Dec 13 at 14:48
add a comment |
1
create another column that has the last two characters, then use that column to do your sort and your filter.
– Scott Craner
Dec 13 at 14:48
1
1
create another column that has the last two characters, then use that column to do your sort and your filter.
– Scott Craner
Dec 13 at 14:48
create another column that has the last two characters, then use that column to do your sort and your filter.
– Scott Craner
Dec 13 at 14:48
add a comment |
1 Answer
1
active
oldest
votes
Add a column to your spreadsheet with just the last two characters of LOCID.
For example, with the LOCID in cell A2, a formula to get the last two characters would be =RIGHT(A2,2).
Sort your spreadsheet on the new column.
answered Dec 13 at 14:57
William Jackson
7,23312843
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1383317%2ffiltering-in-excel-by-last-two-characters-that-are-alphanumeric%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Add a column to your spreadsheet with just the last two characters of LOCID.
For example, with the LOCID in cell A2, a formula to get the last two characters would be =RIGHT(A2,2).
Sort your spreadsheet on the new column.
answered Dec 13 at 14:57
William Jackson
7,23312843
add a comment |
Add a column to your spreadsheet with just the last two characters of LOCID.
For example, with the LOCID in cell A2, a formula to get the last two characters would be =RIGHT(A2,2).
Sort your spreadsheet on the new column.
answered Dec 13 at 14:57
William Jackson
7,23312843
add a comment |
Add a column to your spreadsheet with just the last two characters of LOCID.
For example, with the LOCID in cell A2, a formula to get the last two characters would be =RIGHT(A2,2).
Sort your spreadsheet on the new column.
answered Dec 13 at 14:57
William Jackson
7,23312843
Add a column to your spreadsheet with just the last two characters of LOCID.
For example, with the LOCID in cell A2, a formula to get the last two characters would be =RIGHT(A2,2).
Sort your spreadsheet on the new column.
answered Dec 13 at 14:57
William Jackson
7,23312843
answered Dec 13 at 14:57
William Jackson
7,23312843
answered Dec 13 at 14:57
William Jackson
7,23312843
answered Dec 13 at 14:57
William Jackson
7,23312843
7,23312843
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1383317%2ffiltering-in-excel-by-last-two-characters-that-are-alphanumeric%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
create another column that has the last two characters, then use that column to do your sort and your filter.
– Scott Craner
Dec 13 at 14:48