return value if it contains text
I am trying to write a formula to retrieve items from a column only if it contains a set of text inside it. I have been messing with a formula and it returns incorrectly and instead of skipping a line and just giving me the next one it places a 0 and moves on.
Basically, I only want to return the values in a column if it contains ".FC"
This is to narrow down the rows we will be linking to a flowchart without having to use the search criteria for ".FC" and just copying the column that way.
Edit: The ".FC" is part of a string of characters and will always be the last three characters. What I have now is
=IF(ISNUMBER(SEARCH(".FC",$N:$N)),$N:$N,"")
I need to add something or change something to make it so only the values that have ".FC" get returned and skip the ones that don't have it. This just makes it return nothing in the corresponding row instead of skipping to the next ".FC"
microsoft-excel vlookup
edited Jan 30 at 18:59
user1251007
504622
asked Jan 30 at 16:26
mrheineyboymrheineyboy
12
add a comment |
I am trying to write a formula to retrieve items from a column only if it contains a set of text inside it. I have been messing with a formula and it returns incorrectly and instead of skipping a line and just giving me the next one it places a 0 and moves on.
Basically, I only want to return the values in a column if it contains ".FC"
This is to narrow down the rows we will be linking to a flowchart without having to use the search criteria for ".FC" and just copying the column that way.
Edit: The ".FC" is part of a string of characters and will always be the last three characters. What I have now is
=IF(ISNUMBER(SEARCH(".FC",$N:$N)),$N:$N,"")
I need to add something or change something to make it so only the values that have ".FC" get returned and skip the ones that don't have it. This just makes it return nothing in the corresponding row instead of skipping to the next ".FC"
microsoft-excel vlookup
edited Jan 30 at 18:59
user1251007
504622
asked Jan 30 at 16:26
mrheineyboymrheineyboy
12
2
Can you please edit your question to give us sample data, expected output, what you've tried so far and how it fails to meet expectations?
– cybernetic.nomad
Jan 30 at 16:28
1
Welcome to superuser. It'd be easier to help if you were able to post an example of your spreadsheet or what formula you're using that isn't working. Is ".FC" all that's in the cells you're trying to search, or is it part of a larger string of characters?
– jrichall
Jan 30 at 16:31
add a comment |
I am trying to write a formula to retrieve items from a column only if it contains a set of text inside it. I have been messing with a formula and it returns incorrectly and instead of skipping a line and just giving me the next one it places a 0 and moves on.
Basically, I only want to return the values in a column if it contains ".FC"
This is to narrow down the rows we will be linking to a flowchart without having to use the search criteria for ".FC" and just copying the column that way.
Edit: The ".FC" is part of a string of characters and will always be the last three characters. What I have now is
=IF(ISNUMBER(SEARCH(".FC",$N:$N)),$N:$N,"")
I need to add something or change something to make it so only the values that have ".FC" get returned and skip the ones that don't have it. This just makes it return nothing in the corresponding row instead of skipping to the next ".FC"
microsoft-excel vlookup
edited Jan 30 at 18:59
user1251007
504622
asked Jan 30 at 16:26
mrheineyboymrheineyboy
12
I am trying to write a formula to retrieve items from a column only if it contains a set of text inside it. I have been messing with a formula and it returns incorrectly and instead of skipping a line and just giving me the next one it places a 0 and moves on.
Basically, I only want to return the values in a column if it contains ".FC"
This is to narrow down the rows we will be linking to a flowchart without having to use the search criteria for ".FC" and just copying the column that way.
Edit: The ".FC" is part of a string of characters and will always be the last three characters. What I have now is
=IF(ISNUMBER(SEARCH(".FC",$N:$N)),$N:$N,"")
I need to add something or change something to make it so only the values that have ".FC" get returned and skip the ones that don't have it. This just makes it return nothing in the corresponding row instead of skipping to the next ".FC"
microsoft-excel vlookup
microsoft-excel vlookup
edited Jan 30 at 18:59
user1251007
504622
asked Jan 30 at 16:26
mrheineyboymrheineyboy
12
edited Jan 30 at 18:59
user1251007
504622
asked Jan 30 at 16:26
mrheineyboymrheineyboy
12
edited Jan 30 at 18:59
user1251007
504622
edited Jan 30 at 18:59
user1251007
504622
edited Jan 30 at 18:59
user1251007
504622
504622
asked Jan 30 at 16:26
mrheineyboymrheineyboy
12
asked Jan 30 at 16:26
mrheineyboymrheineyboy
12
asked Jan 30 at 16:26
mrheineyboymrheineyboy
12
12
2
Can you please edit your question to give us sample data, expected output, what you've tried so far and how it fails to meet expectations?
– cybernetic.nomad
Jan 30 at 16:28
1
Welcome to superuser. It'd be easier to help if you were able to post an example of your spreadsheet or what formula you're using that isn't working. Is ".FC" all that's in the cells you're trying to search, or is it part of a larger string of characters?
– jrichall
Jan 30 at 16:31
add a comment |
2
Can you please edit your question to give us sample data, expected output, what you've tried so far and how it fails to meet expectations?
– cybernetic.nomad
Jan 30 at 16:28
1
Welcome to superuser. It'd be easier to help if you were able to post an example of your spreadsheet or what formula you're using that isn't working. Is ".FC" all that's in the cells you're trying to search, or is it part of a larger string of characters?
– jrichall
Jan 30 at 16:31
2
2
Can you please edit your question to give us sample data, expected output, what you've tried so far and how it fails to meet expectations?
– cybernetic.nomad
Jan 30 at 16:28
Can you please edit your question to give us sample data, expected output, what you've tried so far and how it fails to meet expectations?
– cybernetic.nomad
Jan 30 at 16:28
1
1
Welcome to superuser. It'd be easier to help if you were able to post an example of your spreadsheet or what formula you're using that isn't working. Is ".FC" all that's in the cells you're trying to search, or is it part of a larger string of characters?
– jrichall
Jan 30 at 16:31
Welcome to superuser. It'd be easier to help if you were able to post an example of your spreadsheet or what formula you're using that isn't working. Is ".FC" all that's in the cells you're trying to search, or is it part of a larger string of characters?
– jrichall
Jan 30 at 16:31
add a comment |
2 Answers
2
active
oldest
votes
With data in column A like:
In B1 enter:
=IF(ISNUMBER(SEARCH("FC",A1)),1,"")
and in B2 enter:
=IF(ISNUMBER(SEARCH("FC",A2)),1+MAX($B$1:B1),"")
Then copy B2 downwards:
As you seee, all the "good" values are marked with a simple sequential value.
in C1 enter:
=IFERROR(INDEX(A:A,MATCH(ROW(),B:B,0)),"")
and copy downwards:
answered Jan 30 at 16:44
Gary's StudentGary's Student
13.7k31730
1
Woah, theMATCH(ROW(),B:B,0))is pretty clever. I use similar type setups in a few sheets, but that's definitely going to be used from now on, thanks for that!
– BruceWayne
Jan 30 at 18:24
1
Not quite what I am looking for but it can work for now. I was hoping I could figure out one formula that would do the whole thing instead of two. By the way, the formula you put in B2 would work as the B1 formula as well. No need for two different formulas in that column.
– mrheineyboy
Jan 30 at 19:11
@mrheineyboy I tried the standard Aggregate() approach, but I couldn't get it to work.
– Gary's Student
Jan 30 at 20:45
Nah. Your way is what I'm gonna use for now. Just had to tweak the formula and it works for what I'm doing.
– mrheineyboy
Jan 31 at 1:38
add a comment |
Single Formula route:
Put this in the first cell and copy down
=IFERROR(INDEX(A:A,AGGREGATE(15,7,ROW(A1:A100)/(ISNUMBER(SEARCH(".FC",A1:A100))),ROW(1:1)),"")
The main drawback is that this is an array type formula and if the data set is large it will slow down the calcs.
answered Jan 30 at 20:59
Scott CranerScott Craner
12.2k11118
This one does work great. But you are right. It is very slow to run because I have a lot of values.
– mrheineyboy
Jan 31 at 18:16
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%2f1400155%2freturn-value-if-it-contains-text%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
With data in column A like:
In B1 enter:
=IF(ISNUMBER(SEARCH("FC",A1)),1,"")
and in B2 enter:
=IF(ISNUMBER(SEARCH("FC",A2)),1+MAX($B$1:B1),"")
Then copy B2 downwards:
As you seee, all the "good" values are marked with a simple sequential value.
in C1 enter:
=IFERROR(INDEX(A:A,MATCH(ROW(),B:B,0)),"")
and copy downwards:
answered Jan 30 at 16:44
Gary's StudentGary's Student
13.7k31730
1
Woah, theMATCH(ROW(),B:B,0))is pretty clever. I use similar type setups in a few sheets, but that's definitely going to be used from now on, thanks for that!
– BruceWayne
Jan 30 at 18:24
1
Not quite what I am looking for but it can work for now. I was hoping I could figure out one formula that would do the whole thing instead of two. By the way, the formula you put in B2 would work as the B1 formula as well. No need for two different formulas in that column.
– mrheineyboy
Jan 30 at 19:11
@mrheineyboy I tried the standard Aggregate() approach, but I couldn't get it to work.
– Gary's Student
Jan 30 at 20:45
Nah. Your way is what I'm gonna use for now. Just had to tweak the formula and it works for what I'm doing.
– mrheineyboy
Jan 31 at 1:38
add a comment |
With data in column A like:
In B1 enter:
=IF(ISNUMBER(SEARCH("FC",A1)),1,"")
and in B2 enter:
=IF(ISNUMBER(SEARCH("FC",A2)),1+MAX($B$1:B1),"")
Then copy B2 downwards:
As you seee, all the "good" values are marked with a simple sequential value.
in C1 enter:
=IFERROR(INDEX(A:A,MATCH(ROW(),B:B,0)),"")
and copy downwards:
answered Jan 30 at 16:44
Gary's StudentGary's Student
13.7k31730
1
Woah, theMATCH(ROW(),B:B,0))is pretty clever. I use similar type setups in a few sheets, but that's definitely going to be used from now on, thanks for that!
– BruceWayne
Jan 30 at 18:24
1
Not quite what I am looking for but it can work for now. I was hoping I could figure out one formula that would do the whole thing instead of two. By the way, the formula you put in B2 would work as the B1 formula as well. No need for two different formulas in that column.
– mrheineyboy
Jan 30 at 19:11
@mrheineyboy I tried the standard Aggregate() approach, but I couldn't get it to work.
– Gary's Student
Jan 30 at 20:45
Nah. Your way is what I'm gonna use for now. Just had to tweak the formula and it works for what I'm doing.
– mrheineyboy
Jan 31 at 1:38
add a comment |
With data in column A like:
In B1 enter:
=IF(ISNUMBER(SEARCH("FC",A1)),1,"")
and in B2 enter:
=IF(ISNUMBER(SEARCH("FC",A2)),1+MAX($B$1:B1),"")
Then copy B2 downwards:
As you seee, all the "good" values are marked with a simple sequential value.
in C1 enter:
=IFERROR(INDEX(A:A,MATCH(ROW(),B:B,0)),"")
and copy downwards:
answered Jan 30 at 16:44
Gary's StudentGary's Student
13.7k31730
With data in column A like:
In B1 enter:
=IF(ISNUMBER(SEARCH("FC",A1)),1,"")
and in B2 enter:
=IF(ISNUMBER(SEARCH("FC",A2)),1+MAX($B$1:B1),"")
Then copy B2 downwards:
As you seee, all the "good" values are marked with a simple sequential value.
in C1 enter:
=IFERROR(INDEX(A:A,MATCH(ROW(),B:B,0)),"")
and copy downwards:
answered Jan 30 at 16:44
Gary's StudentGary's Student
13.7k31730
answered Jan 30 at 16:44
Gary's StudentGary's Student
13.7k31730
answered Jan 30 at 16:44
Gary's StudentGary's Student
13.7k31730
answered Jan 30 at 16:44
Gary's StudentGary's Student
13.7k31730
13.7k31730
1
Woah, theMATCH(ROW(),B:B,0))is pretty clever. I use similar type setups in a few sheets, but that's definitely going to be used from now on, thanks for that!
– BruceWayne
Jan 30 at 18:24
1
Not quite what I am looking for but it can work for now. I was hoping I could figure out one formula that would do the whole thing instead of two. By the way, the formula you put in B2 would work as the B1 formula as well. No need for two different formulas in that column.
– mrheineyboy
Jan 30 at 19:11
@mrheineyboy I tried the standard Aggregate() approach, but I couldn't get it to work.
– Gary's Student
Jan 30 at 20:45
Nah. Your way is what I'm gonna use for now. Just had to tweak the formula and it works for what I'm doing.
– mrheineyboy
Jan 31 at 1:38
add a comment |
1
Woah, theMATCH(ROW(),B:B,0))is pretty clever. I use similar type setups in a few sheets, but that's definitely going to be used from now on, thanks for that!
– BruceWayne
Jan 30 at 18:24
1
Not quite what I am looking for but it can work for now. I was hoping I could figure out one formula that would do the whole thing instead of two. By the way, the formula you put in B2 would work as the B1 formula as well. No need for two different formulas in that column.
– mrheineyboy
Jan 30 at 19:11
@mrheineyboy I tried the standard Aggregate() approach, but I couldn't get it to work.
– Gary's Student
Jan 30 at 20:45
Nah. Your way is what I'm gonna use for now. Just had to tweak the formula and it works for what I'm doing.
– mrheineyboy
Jan 31 at 1:38
1
1
Woah, the
MATCH(ROW(),B:B,0)) is pretty clever. I use similar type setups in a few sheets, but that's definitely going to be used from now on, thanks for that!– BruceWayne
Jan 30 at 18:24
Woah, the
MATCH(ROW(),B:B,0)) is pretty clever. I use similar type setups in a few sheets, but that's definitely going to be used from now on, thanks for that!– BruceWayne
Jan 30 at 18:24
1
1
Not quite what I am looking for but it can work for now. I was hoping I could figure out one formula that would do the whole thing instead of two. By the way, the formula you put in B2 would work as the B1 formula as well. No need for two different formulas in that column.
– mrheineyboy
Jan 30 at 19:11
Not quite what I am looking for but it can work for now. I was hoping I could figure out one formula that would do the whole thing instead of two. By the way, the formula you put in B2 would work as the B1 formula as well. No need for two different formulas in that column.
– mrheineyboy
Jan 30 at 19:11
@mrheineyboy I tried the standard Aggregate() approach, but I couldn't get it to work.
– Gary's Student
Jan 30 at 20:45
@mrheineyboy I tried the standard Aggregate() approach, but I couldn't get it to work.
– Gary's Student
Jan 30 at 20:45
Nah. Your way is what I'm gonna use for now. Just had to tweak the formula and it works for what I'm doing.
– mrheineyboy
Jan 31 at 1:38
Nah. Your way is what I'm gonna use for now. Just had to tweak the formula and it works for what I'm doing.
– mrheineyboy
Jan 31 at 1:38
add a comment |
Single Formula route:
Put this in the first cell and copy down
=IFERROR(INDEX(A:A,AGGREGATE(15,7,ROW(A1:A100)/(ISNUMBER(SEARCH(".FC",A1:A100))),ROW(1:1)),"")
The main drawback is that this is an array type formula and if the data set is large it will slow down the calcs.
answered Jan 30 at 20:59
Scott CranerScott Craner
12.2k11118
This one does work great. But you are right. It is very slow to run because I have a lot of values.
– mrheineyboy
Jan 31 at 18:16
add a comment |
Single Formula route:
Put this in the first cell and copy down
=IFERROR(INDEX(A:A,AGGREGATE(15,7,ROW(A1:A100)/(ISNUMBER(SEARCH(".FC",A1:A100))),ROW(1:1)),"")
The main drawback is that this is an array type formula and if the data set is large it will slow down the calcs.
answered Jan 30 at 20:59
Scott CranerScott Craner
12.2k11118
This one does work great. But you are right. It is very slow to run because I have a lot of values.
– mrheineyboy
Jan 31 at 18:16
add a comment |
Single Formula route:
Put this in the first cell and copy down
=IFERROR(INDEX(A:A,AGGREGATE(15,7,ROW(A1:A100)/(ISNUMBER(SEARCH(".FC",A1:A100))),ROW(1:1)),"")
The main drawback is that this is an array type formula and if the data set is large it will slow down the calcs.
answered Jan 30 at 20:59
Scott CranerScott Craner
12.2k11118
Single Formula route:
Put this in the first cell and copy down
=IFERROR(INDEX(A:A,AGGREGATE(15,7,ROW(A1:A100)/(ISNUMBER(SEARCH(".FC",A1:A100))),ROW(1:1)),"")
The main drawback is that this is an array type formula and if the data set is large it will slow down the calcs.
answered Jan 30 at 20:59
Scott CranerScott Craner
12.2k11118
answered Jan 30 at 20:59
Scott CranerScott Craner
12.2k11118
answered Jan 30 at 20:59
Scott CranerScott Craner
12.2k11118
answered Jan 30 at 20:59
Scott CranerScott Craner
12.2k11118
12.2k11118
This one does work great. But you are right. It is very slow to run because I have a lot of values.
– mrheineyboy
Jan 31 at 18:16
add a comment |
This one does work great. But you are right. It is very slow to run because I have a lot of values.
– mrheineyboy
Jan 31 at 18:16
This one does work great. But you are right. It is very slow to run because I have a lot of values.
– mrheineyboy
Jan 31 at 18:16
This one does work great. But you are right. It is very slow to run because I have a lot of values.
– mrheineyboy
Jan 31 at 18:16
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.
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%2f1400155%2freturn-value-if-it-contains-text%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
2
Can you please edit your question to give us sample data, expected output, what you've tried so far and how it fails to meet expectations?
– cybernetic.nomad
Jan 30 at 16:28
1
Welcome to superuser. It'd be easier to help if you were able to post an example of your spreadsheet or what formula you're using that isn't working. Is ".FC" all that's in the cells you're trying to search, or is it part of a larger string of characters?
– jrichall
Jan 30 at 16:31