MS EXCEL: Search and Extract Text from a String Using Wildcards in a Column Range
Remove or Extract Dates from String - Using Wildcards in a Range Column
Using a MS Excel Array Formula, I would like a formula that will allow me to search within a string of text located in Cell A and extract only matching substring text that matches a unique wildcard pattern in Column Range: $D$2:$D$10.
OR any formula that will help me achieve either extract on the matching date from the string or remove matching date from the string using while using a column range.
COLUMN A (TEXT)
A2: Sally was employed from January 2016 - August 2018 and now works at home
A3: April 2013 through January 2014 was a good year
A4: Here are dates they worked for us 01/2011 til 09/2013
COLUMN C (OUTPUT RESULTS)
C2: Sally was employed from and now works at home
C3: was good
C4: Here's when they worked
COLUMN D (RANGE FIND WHAT)
D2: * 20?? through * 20??
D3: ??/20?? til ??/20??
D4: * ???? - * ????
D5: * 2017 - Present
D6: * 20?? - * 20??
D7: * 20?? * 20??
excel substring extract
asked Nov 20 at 19:25
Miaka3
84414
add a comment |
Remove or Extract Dates from String - Using Wildcards in a Range Column
Using a MS Excel Array Formula, I would like a formula that will allow me to search within a string of text located in Cell A and extract only matching substring text that matches a unique wildcard pattern in Column Range: $D$2:$D$10.
OR any formula that will help me achieve either extract on the matching date from the string or remove matching date from the string using while using a column range.
COLUMN A (TEXT)
A2: Sally was employed from January 2016 - August 2018 and now works at home
A3: April 2013 through January 2014 was a good year
A4: Here are dates they worked for us 01/2011 til 09/2013
COLUMN C (OUTPUT RESULTS)
C2: Sally was employed from and now works at home
C3: was good
C4: Here's when they worked
COLUMN D (RANGE FIND WHAT)
D2: * 20?? through * 20??
D3: ??/20?? til ??/20??
D4: * ???? - * ????
D5: * 2017 - Present
D6: * 20?? - * 20??
D7: * 20?? * 20??
excel substring extract
asked Nov 20 at 19:25
Miaka3
84414
3
Why are you SHOUTING? This is very annoying.
– teylyn
Nov 20 at 19:28
My apologies, thanks for the heads up... I retyped to be a little more subtle.
– Miaka3
Nov 20 at 19:32
Can you please edit you question and let us know what you have tried so far?
– cybernetic.nomad
Nov 20 at 19:39
I've attempted several formulas
– Miaka3
Nov 20 at 19:43
I've attempted several formulas: My use of the Index, Match and Search formula was able to find and return the corresponding value of the text pattern identified within the string. =IFERROR(INDEX($D$2:$D$25,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$25,A2)),0))&"","")
– Miaka3
Nov 20 at 19:49
add a comment |
Remove or Extract Dates from String - Using Wildcards in a Range Column
Using a MS Excel Array Formula, I would like a formula that will allow me to search within a string of text located in Cell A and extract only matching substring text that matches a unique wildcard pattern in Column Range: $D$2:$D$10.
OR any formula that will help me achieve either extract on the matching date from the string or remove matching date from the string using while using a column range.
COLUMN A (TEXT)
A2: Sally was employed from January 2016 - August 2018 and now works at home
A3: April 2013 through January 2014 was a good year
A4: Here are dates they worked for us 01/2011 til 09/2013
COLUMN C (OUTPUT RESULTS)
C2: Sally was employed from and now works at home
C3: was good
C4: Here's when they worked
COLUMN D (RANGE FIND WHAT)
D2: * 20?? through * 20??
D3: ??/20?? til ??/20??
D4: * ???? - * ????
D5: * 2017 - Present
D6: * 20?? - * 20??
D7: * 20?? * 20??
excel substring extract
asked Nov 20 at 19:25
Miaka3
84414
Remove or Extract Dates from String - Using Wildcards in a Range Column
Using a MS Excel Array Formula, I would like a formula that will allow me to search within a string of text located in Cell A and extract only matching substring text that matches a unique wildcard pattern in Column Range: $D$2:$D$10.
OR any formula that will help me achieve either extract on the matching date from the string or remove matching date from the string using while using a column range.
COLUMN A (TEXT)
A2: Sally was employed from January 2016 - August 2018 and now works at home
A3: April 2013 through January 2014 was a good year
A4: Here are dates they worked for us 01/2011 til 09/2013
COLUMN C (OUTPUT RESULTS)
C2: Sally was employed from and now works at home
C3: was good
C4: Here's when they worked
COLUMN D (RANGE FIND WHAT)
D2: * 20?? through * 20??
D3: ??/20?? til ??/20??
D4: * ???? - * ????
D5: * 2017 - Present
D6: * 20?? - * 20??
D7: * 20?? * 20??
excel substring extract
excel substring extract
asked Nov 20 at 19:25
Miaka3
84414
asked Nov 20 at 19:25
Miaka3
84414
edited Nov 20 at 20:28
asked Nov 20 at 19:25
Miaka3
84414
asked Nov 20 at 19:25
Miaka3
84414
asked Nov 20 at 19:25
Miaka3
84414
84414
3
Why are you SHOUTING? This is very annoying.
– teylyn
Nov 20 at 19:28
My apologies, thanks for the heads up... I retyped to be a little more subtle.
– Miaka3
Nov 20 at 19:32
Can you please edit you question and let us know what you have tried so far?
– cybernetic.nomad
Nov 20 at 19:39
I've attempted several formulas
– Miaka3
Nov 20 at 19:43
I've attempted several formulas: My use of the Index, Match and Search formula was able to find and return the corresponding value of the text pattern identified within the string. =IFERROR(INDEX($D$2:$D$25,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$25,A2)),0))&"","")
– Miaka3
Nov 20 at 19:49
add a comment |
3
Why are you SHOUTING? This is very annoying.
– teylyn
Nov 20 at 19:28
My apologies, thanks for the heads up... I retyped to be a little more subtle.
– Miaka3
Nov 20 at 19:32
Can you please edit you question and let us know what you have tried so far?
– cybernetic.nomad
Nov 20 at 19:39
I've attempted several formulas
– Miaka3
Nov 20 at 19:43
I've attempted several formulas: My use of the Index, Match and Search formula was able to find and return the corresponding value of the text pattern identified within the string. =IFERROR(INDEX($D$2:$D$25,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$25,A2)),0))&"","")
– Miaka3
Nov 20 at 19:49
3
3
Why are you SHOUTING? This is very annoying.
– teylyn
Nov 20 at 19:28
Why are you SHOUTING? This is very annoying.
– teylyn
Nov 20 at 19:28
My apologies, thanks for the heads up... I retyped to be a little more subtle.
– Miaka3
Nov 20 at 19:32
My apologies, thanks for the heads up... I retyped to be a little more subtle.
– Miaka3
Nov 20 at 19:32
Can you please edit you question and let us know what you have tried so far?
– cybernetic.nomad
Nov 20 at 19:39
Can you please edit you question and let us know what you have tried so far?
– cybernetic.nomad
Nov 20 at 19:39
I've attempted several formulas
– Miaka3
Nov 20 at 19:43
I've attempted several formulas
– Miaka3
Nov 20 at 19:43
I've attempted several formulas: My use of the Index, Match and Search formula was able to find and return the corresponding value of the text pattern identified within the string. =IFERROR(INDEX($D$2:$D$25,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$25,A2)),0))&"","")
– Miaka3
Nov 20 at 19:49
I've attempted several formulas: My use of the Index, Match and Search formula was able to find and return the corresponding value of the text pattern identified within the string. =IFERROR(INDEX($D$2:$D$25,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$25,A2)),0))&"","")
– Miaka3
Nov 20 at 19:49
add a comment |
1 Answer
1
active
oldest
votes
I have a partial solution for you and I would love to know as well if it's possible to answer your question completely without using RegEx or a custom function.
Finding partial matches with wildcards, their relative positions in a list and where they start is really trivial and has been covered dozens of times on this website alone.
The one problem I cannot solve myself is removing the matched text with wildcards as obviously it can has differing lengths. If the wildcards replace single characters only (?) it's quite simple and you should have no problems doing it yourself based on my formulas below. Unfortunately I am at a loss how to solve the problem when * wildcards are used.
I don't know of a solution and would love to learn myself.
String found:
=INDEX(list,MAX(IF(ISERROR(SEARCH(list,$A2)),-1,1)*(ROW(list)-ROW($A$7)+1)))
Row found:
=MAX(IF(ISERROR(SEARCH(list,A2)),-1,1)*ROW(list))
Position found:
=AGGREGATE(14, 6, SEARCH(list,A2), 1)
answered Nov 20 at 23:32
Michal Rosa
1,3061714
Michal thank you, the provided formula does work, and will need a little tweaking but you're definitely on the right track, as to what I'm in need of. Again thank you
– Miaka3
Nov 21 at 1:05
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53400153%2fms-excel-search-and-extract-text-from-a-string-using-wildcards-in-a-column-rang%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
I have a partial solution for you and I would love to know as well if it's possible to answer your question completely without using RegEx or a custom function.
Finding partial matches with wildcards, their relative positions in a list and where they start is really trivial and has been covered dozens of times on this website alone.
The one problem I cannot solve myself is removing the matched text with wildcards as obviously it can has differing lengths. If the wildcards replace single characters only (?) it's quite simple and you should have no problems doing it yourself based on my formulas below. Unfortunately I am at a loss how to solve the problem when * wildcards are used.
I don't know of a solution and would love to learn myself.
String found:
=INDEX(list,MAX(IF(ISERROR(SEARCH(list,$A2)),-1,1)*(ROW(list)-ROW($A$7)+1)))
Row found:
=MAX(IF(ISERROR(SEARCH(list,A2)),-1,1)*ROW(list))
Position found:
=AGGREGATE(14, 6, SEARCH(list,A2), 1)
answered Nov 20 at 23:32
Michal Rosa
1,3061714
Michal thank you, the provided formula does work, and will need a little tweaking but you're definitely on the right track, as to what I'm in need of. Again thank you
– Miaka3
Nov 21 at 1:05
add a comment |
I have a partial solution for you and I would love to know as well if it's possible to answer your question completely without using RegEx or a custom function.
Finding partial matches with wildcards, their relative positions in a list and where they start is really trivial and has been covered dozens of times on this website alone.
The one problem I cannot solve myself is removing the matched text with wildcards as obviously it can has differing lengths. If the wildcards replace single characters only (?) it's quite simple and you should have no problems doing it yourself based on my formulas below. Unfortunately I am at a loss how to solve the problem when * wildcards are used.
I don't know of a solution and would love to learn myself.
String found:
=INDEX(list,MAX(IF(ISERROR(SEARCH(list,$A2)),-1,1)*(ROW(list)-ROW($A$7)+1)))
Row found:
=MAX(IF(ISERROR(SEARCH(list,A2)),-1,1)*ROW(list))
Position found:
=AGGREGATE(14, 6, SEARCH(list,A2), 1)
answered Nov 20 at 23:32
Michal Rosa
1,3061714
Michal thank you, the provided formula does work, and will need a little tweaking but you're definitely on the right track, as to what I'm in need of. Again thank you
– Miaka3
Nov 21 at 1:05
add a comment |
I have a partial solution for you and I would love to know as well if it's possible to answer your question completely without using RegEx or a custom function.
Finding partial matches with wildcards, their relative positions in a list and where they start is really trivial and has been covered dozens of times on this website alone.
The one problem I cannot solve myself is removing the matched text with wildcards as obviously it can has differing lengths. If the wildcards replace single characters only (?) it's quite simple and you should have no problems doing it yourself based on my formulas below. Unfortunately I am at a loss how to solve the problem when * wildcards are used.
I don't know of a solution and would love to learn myself.
String found:
=INDEX(list,MAX(IF(ISERROR(SEARCH(list,$A2)),-1,1)*(ROW(list)-ROW($A$7)+1)))
Row found:
=MAX(IF(ISERROR(SEARCH(list,A2)),-1,1)*ROW(list))
Position found:
=AGGREGATE(14, 6, SEARCH(list,A2), 1)
answered Nov 20 at 23:32
Michal Rosa
1,3061714
I have a partial solution for you and I would love to know as well if it's possible to answer your question completely without using RegEx or a custom function.
Finding partial matches with wildcards, their relative positions in a list and where they start is really trivial and has been covered dozens of times on this website alone.
The one problem I cannot solve myself is removing the matched text with wildcards as obviously it can has differing lengths. If the wildcards replace single characters only (?) it's quite simple and you should have no problems doing it yourself based on my formulas below. Unfortunately I am at a loss how to solve the problem when * wildcards are used.
I don't know of a solution and would love to learn myself.
String found:
=INDEX(list,MAX(IF(ISERROR(SEARCH(list,$A2)),-1,1)*(ROW(list)-ROW($A$7)+1)))
Row found:
=MAX(IF(ISERROR(SEARCH(list,A2)),-1,1)*ROW(list))
Position found:
=AGGREGATE(14, 6, SEARCH(list,A2), 1)
answered Nov 20 at 23:32
Michal Rosa
1,3061714
answered Nov 20 at 23:32
Michal Rosa
1,3061714
answered Nov 20 at 23:32
Michal Rosa
1,3061714
answered Nov 20 at 23:32
Michal Rosa
1,3061714
1,3061714
Michal thank you, the provided formula does work, and will need a little tweaking but you're definitely on the right track, as to what I'm in need of. Again thank you
– Miaka3
Nov 21 at 1:05
add a comment |
Michal thank you, the provided formula does work, and will need a little tweaking but you're definitely on the right track, as to what I'm in need of. Again thank you
– Miaka3
Nov 21 at 1:05
Michal thank you, the provided formula does work, and will need a little tweaking but you're definitely on the right track, as to what I'm in need of. Again thank you
– Miaka3
Nov 21 at 1:05
Michal thank you, the provided formula does work, and will need a little tweaking but you're definitely on the right track, as to what I'm in need of. Again thank you
– Miaka3
Nov 21 at 1:05
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53400153%2fms-excel-search-and-extract-text-from-a-string-using-wildcards-in-a-column-rang%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
3
Why are you SHOUTING? This is very annoying.
– teylyn
Nov 20 at 19:28
My apologies, thanks for the heads up... I retyped to be a little more subtle.
– Miaka3
Nov 20 at 19:32
Can you please edit you question and let us know what you have tried so far?
– cybernetic.nomad
Nov 20 at 19:39
I've attempted several formulas
– Miaka3
Nov 20 at 19:43
I've attempted several formulas: My use of the Index, Match and Search formula was able to find and return the corresponding value of the text pattern identified within the string. =IFERROR(INDEX($D$2:$D$25,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$25,A2)),0))&"","")
– Miaka3
Nov 20 at 19:49