Excel multiple FIND formula
I am no expert with excel so any help is appreciated!
I am trying to make one cell do multiple searches within another.
I know that to search one cell for a specific word you would use
=FIND(Blue,C2)
But I need to find multiple different words in one cell. Heres an example of what im trying to do:
I need to show all the allergens in the ingredients cell eg peanuts, gluten, milk, sesame seeds etc
How do I get the result to list all the allergens?
I know that I can do a single find function in separate cells and then concatenate them, but there must be a more simple way?
Thanks
microsoft-excel
edited Dec 21 '18 at 12:42
cybernetic.nomad
1,367112
asked Dec 21 '18 at 10:10
ImiImi
93
|
show 2 more comments
I am no expert with excel so any help is appreciated!
I am trying to make one cell do multiple searches within another.
I know that to search one cell for a specific word you would use
=FIND(Blue,C2)
But I need to find multiple different words in one cell. Heres an example of what im trying to do:
I need to show all the allergens in the ingredients cell eg peanuts, gluten, milk, sesame seeds etc
How do I get the result to list all the allergens?
I know that I can do a single find function in separate cells and then concatenate them, but there must be a more simple way?
Thanks
microsoft-excel
edited Dec 21 '18 at 12:42
cybernetic.nomad
1,367112
asked Dec 21 '18 at 10:10
ImiImi
93
are you just looking to confirm the presence of multiple words or do you need to know where they appear in the string?
– RickyTillson
Dec 21 '18 at 10:19
What form of search result do you expect to get? Assuming that more that 1 word of search for list can be found...
– Akina
Dec 21 '18 at 10:22
It isn't clear what you mean by find different words in one cell. Please provide some example cells and what you want the result to be. This could refer to finding the presence or count of any, each, or all, and then how and where do you express the result?
– fixer1234
Dec 21 '18 at 10:25
I think you're misunderstanding what FIND does - this would return a number rather than the word. I assume what you want the formula to do is in return the words that match
– RickyTillson
Dec 21 '18 at 14:47
i need it to return as a word, i thought if you use " it would define the word?
– Imi
Dec 21 '18 at 15:01
|
show 2 more comments
I am no expert with excel so any help is appreciated!
I am trying to make one cell do multiple searches within another.
I know that to search one cell for a specific word you would use
=FIND(Blue,C2)
But I need to find multiple different words in one cell. Heres an example of what im trying to do:
I need to show all the allergens in the ingredients cell eg peanuts, gluten, milk, sesame seeds etc
How do I get the result to list all the allergens?
I know that I can do a single find function in separate cells and then concatenate them, but there must be a more simple way?
Thanks
microsoft-excel
edited Dec 21 '18 at 12:42
cybernetic.nomad
1,367112
asked Dec 21 '18 at 10:10
ImiImi
93
I am no expert with excel so any help is appreciated!
I am trying to make one cell do multiple searches within another.
I know that to search one cell for a specific word you would use
=FIND(Blue,C2)
But I need to find multiple different words in one cell. Heres an example of what im trying to do:
I need to show all the allergens in the ingredients cell eg peanuts, gluten, milk, sesame seeds etc
How do I get the result to list all the allergens?
I know that I can do a single find function in separate cells and then concatenate them, but there must be a more simple way?
Thanks
microsoft-excel
microsoft-excel
edited Dec 21 '18 at 12:42
cybernetic.nomad
1,367112
asked Dec 21 '18 at 10:10
ImiImi
93
edited Dec 21 '18 at 12:42
cybernetic.nomad
1,367112
asked Dec 21 '18 at 10:10
ImiImi
93
edited Dec 21 '18 at 12:42
cybernetic.nomad
1,367112
edited Dec 21 '18 at 12:42
cybernetic.nomad
1,367112
edited Dec 21 '18 at 12:42
cybernetic.nomad
1,367112
1,367112
asked Dec 21 '18 at 10:10
ImiImi
93
asked Dec 21 '18 at 10:10
ImiImi
93
asked Dec 21 '18 at 10:10
ImiImi
93
93
are you just looking to confirm the presence of multiple words or do you need to know where they appear in the string?
– RickyTillson
Dec 21 '18 at 10:19
What form of search result do you expect to get? Assuming that more that 1 word of search for list can be found...
– Akina
Dec 21 '18 at 10:22
It isn't clear what you mean by find different words in one cell. Please provide some example cells and what you want the result to be. This could refer to finding the presence or count of any, each, or all, and then how and where do you express the result?
– fixer1234
Dec 21 '18 at 10:25
I think you're misunderstanding what FIND does - this would return a number rather than the word. I assume what you want the formula to do is in return the words that match
– RickyTillson
Dec 21 '18 at 14:47
i need it to return as a word, i thought if you use " it would define the word?
– Imi
Dec 21 '18 at 15:01
|
show 2 more comments
are you just looking to confirm the presence of multiple words or do you need to know where they appear in the string?
– RickyTillson
Dec 21 '18 at 10:19
What form of search result do you expect to get? Assuming that more that 1 word of search for list can be found...
– Akina
Dec 21 '18 at 10:22
It isn't clear what you mean by find different words in one cell. Please provide some example cells and what you want the result to be. This could refer to finding the presence or count of any, each, or all, and then how and where do you express the result?
– fixer1234
Dec 21 '18 at 10:25
I think you're misunderstanding what FIND does - this would return a number rather than the word. I assume what you want the formula to do is in return the words that match
– RickyTillson
Dec 21 '18 at 14:47
i need it to return as a word, i thought if you use " it would define the word?
– Imi
Dec 21 '18 at 15:01
are you just looking to confirm the presence of multiple words or do you need to know where they appear in the string?
– RickyTillson
Dec 21 '18 at 10:19
are you just looking to confirm the presence of multiple words or do you need to know where they appear in the string?
– RickyTillson
Dec 21 '18 at 10:19
What form of search result do you expect to get? Assuming that more that 1 word of search for list can be found...
– Akina
Dec 21 '18 at 10:22
What form of search result do you expect to get? Assuming that more that 1 word of search for list can be found...
– Akina
Dec 21 '18 at 10:22
It isn't clear what you mean by find different words in one cell. Please provide some example cells and what you want the result to be. This could refer to finding the presence or count of any, each, or all, and then how and where do you express the result?
– fixer1234
Dec 21 '18 at 10:25
It isn't clear what you mean by find different words in one cell. Please provide some example cells and what you want the result to be. This could refer to finding the presence or count of any, each, or all, and then how and where do you express the result?
– fixer1234
Dec 21 '18 at 10:25
I think you're misunderstanding what FIND does - this would return a number rather than the word. I assume what you want the formula to do is in return the words that match
– RickyTillson
Dec 21 '18 at 14:47
I think you're misunderstanding what FIND does - this would return a number rather than the word. I assume what you want the formula to do is in return the words that match
– RickyTillson
Dec 21 '18 at 14:47
i need it to return as a word, i thought if you use " it would define the word?
– Imi
Dec 21 '18 at 15:01
i need it to return as a word, i thought if you use " it would define the word?
– Imi
Dec 21 '18 at 15:01
|
show 2 more comments
1 Answer
1
active
oldest
votes
I would like to suggest Two possible methods. 1st is Formula and 2nd is MACRO.
Method 1:
=IFERROR(IF(OR(SEARCH(B1,A1)>0,SEARCH(C1,A1)>0,SEARCH(D1,A1)>0,SEARCH(E1,A1)>0),"Found",""),"Nfound")
Note:
- Write this Formula in cell
G1and drag it Down. - You can extend the Formula, if more Text/Words to be searched.
Method 2:
- At the Active Sheet press
Alt+F11to openVB Editor.
Copy & Paste below show code as Standard Module.- Select the Range
A1:A3, where you want to search Text/Words to highlight.
RUN the Macro.- Enter Words in INPUT BOX, separated by Comma and press OK
Macro will Bold Face the words been found in Range A1:A3.
Sub MultiFindNBoldFace()
Dim strSearch As String
Dim arySearch As Variant
Dim searchRng As Range
Dim cel As Range
Dim i As Long, ii As Long
Set searchRng = Application.Selection
strSearch = InputBox("Please enter the text to make bold as a comma delimited list (Abc,Xyz) - No spaces:", "Bold Text")
If strSearch = "" Then Exit Sub
arySearch = Split(strSearch, ",")
For Each cel In searchRng
With cel
.Font.Bold = False
For ii = LBound(arySearch) To UBound(arySearch)
i = InStr(cel.Value, arySearch(ii))
If i > 0 Then
.Characters(i, Len(arySearch(ii))).Font.Bold = True
End If
Next ii
End With
Next cel
End Sub
You can adjust cell references in the Formula as well as in the Macro also.
answered Dec 22 '18 at 7:05
Rajesh SRajesh S
1
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%2f1386599%2fexcel-multiple-find-formula%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 would like to suggest Two possible methods. 1st is Formula and 2nd is MACRO.
Method 1:
=IFERROR(IF(OR(SEARCH(B1,A1)>0,SEARCH(C1,A1)>0,SEARCH(D1,A1)>0,SEARCH(E1,A1)>0),"Found",""),"Nfound")
Note:
- Write this Formula in cell
G1and drag it Down. - You can extend the Formula, if more Text/Words to be searched.
Method 2:
- At the Active Sheet press
Alt+F11to openVB Editor.
Copy & Paste below show code as Standard Module.- Select the Range
A1:A3, where you want to search Text/Words to highlight.
RUN the Macro.- Enter Words in INPUT BOX, separated by Comma and press OK
Macro will Bold Face the words been found in Range A1:A3.
Sub MultiFindNBoldFace()
Dim strSearch As String
Dim arySearch As Variant
Dim searchRng As Range
Dim cel As Range
Dim i As Long, ii As Long
Set searchRng = Application.Selection
strSearch = InputBox("Please enter the text to make bold as a comma delimited list (Abc,Xyz) - No spaces:", "Bold Text")
If strSearch = "" Then Exit Sub
arySearch = Split(strSearch, ",")
For Each cel In searchRng
With cel
.Font.Bold = False
For ii = LBound(arySearch) To UBound(arySearch)
i = InStr(cel.Value, arySearch(ii))
If i > 0 Then
.Characters(i, Len(arySearch(ii))).Font.Bold = True
End If
Next ii
End With
Next cel
End Sub
You can adjust cell references in the Formula as well as in the Macro also.
answered Dec 22 '18 at 7:05
Rajesh SRajesh S
1
add a comment |
I would like to suggest Two possible methods. 1st is Formula and 2nd is MACRO.
Method 1:
=IFERROR(IF(OR(SEARCH(B1,A1)>0,SEARCH(C1,A1)>0,SEARCH(D1,A1)>0,SEARCH(E1,A1)>0),"Found",""),"Nfound")
Note:
- Write this Formula in cell
G1and drag it Down. - You can extend the Formula, if more Text/Words to be searched.
Method 2:
- At the Active Sheet press
Alt+F11to openVB Editor.
Copy & Paste below show code as Standard Module.- Select the Range
A1:A3, where you want to search Text/Words to highlight.
RUN the Macro.- Enter Words in INPUT BOX, separated by Comma and press OK
Macro will Bold Face the words been found in Range A1:A3.
Sub MultiFindNBoldFace()
Dim strSearch As String
Dim arySearch As Variant
Dim searchRng As Range
Dim cel As Range
Dim i As Long, ii As Long
Set searchRng = Application.Selection
strSearch = InputBox("Please enter the text to make bold as a comma delimited list (Abc,Xyz) - No spaces:", "Bold Text")
If strSearch = "" Then Exit Sub
arySearch = Split(strSearch, ",")
For Each cel In searchRng
With cel
.Font.Bold = False
For ii = LBound(arySearch) To UBound(arySearch)
i = InStr(cel.Value, arySearch(ii))
If i > 0 Then
.Characters(i, Len(arySearch(ii))).Font.Bold = True
End If
Next ii
End With
Next cel
End Sub
You can adjust cell references in the Formula as well as in the Macro also.
answered Dec 22 '18 at 7:05
Rajesh SRajesh S
1
add a comment |
I would like to suggest Two possible methods. 1st is Formula and 2nd is MACRO.
Method 1:
=IFERROR(IF(OR(SEARCH(B1,A1)>0,SEARCH(C1,A1)>0,SEARCH(D1,A1)>0,SEARCH(E1,A1)>0),"Found",""),"Nfound")
Note:
- Write this Formula in cell
G1and drag it Down. - You can extend the Formula, if more Text/Words to be searched.
Method 2:
- At the Active Sheet press
Alt+F11to openVB Editor.
Copy & Paste below show code as Standard Module.- Select the Range
A1:A3, where you want to search Text/Words to highlight.
RUN the Macro.- Enter Words in INPUT BOX, separated by Comma and press OK
Macro will Bold Face the words been found in Range A1:A3.
Sub MultiFindNBoldFace()
Dim strSearch As String
Dim arySearch As Variant
Dim searchRng As Range
Dim cel As Range
Dim i As Long, ii As Long
Set searchRng = Application.Selection
strSearch = InputBox("Please enter the text to make bold as a comma delimited list (Abc,Xyz) - No spaces:", "Bold Text")
If strSearch = "" Then Exit Sub
arySearch = Split(strSearch, ",")
For Each cel In searchRng
With cel
.Font.Bold = False
For ii = LBound(arySearch) To UBound(arySearch)
i = InStr(cel.Value, arySearch(ii))
If i > 0 Then
.Characters(i, Len(arySearch(ii))).Font.Bold = True
End If
Next ii
End With
Next cel
End Sub
You can adjust cell references in the Formula as well as in the Macro also.
answered Dec 22 '18 at 7:05
Rajesh SRajesh S
1
I would like to suggest Two possible methods. 1st is Formula and 2nd is MACRO.
Method 1:
=IFERROR(IF(OR(SEARCH(B1,A1)>0,SEARCH(C1,A1)>0,SEARCH(D1,A1)>0,SEARCH(E1,A1)>0),"Found",""),"Nfound")
Note:
- Write this Formula in cell
G1and drag it Down. - You can extend the Formula, if more Text/Words to be searched.
Method 2:
- At the Active Sheet press
Alt+F11to openVB Editor.
Copy & Paste below show code as Standard Module.- Select the Range
A1:A3, where you want to search Text/Words to highlight.
RUN the Macro.- Enter Words in INPUT BOX, separated by Comma and press OK
Macro will Bold Face the words been found in Range A1:A3.
Sub MultiFindNBoldFace()
Dim strSearch As String
Dim arySearch As Variant
Dim searchRng As Range
Dim cel As Range
Dim i As Long, ii As Long
Set searchRng = Application.Selection
strSearch = InputBox("Please enter the text to make bold as a comma delimited list (Abc,Xyz) - No spaces:", "Bold Text")
If strSearch = "" Then Exit Sub
arySearch = Split(strSearch, ",")
For Each cel In searchRng
With cel
.Font.Bold = False
For ii = LBound(arySearch) To UBound(arySearch)
i = InStr(cel.Value, arySearch(ii))
If i > 0 Then
.Characters(i, Len(arySearch(ii))).Font.Bold = True
End If
Next ii
End With
Next cel
End Sub
You can adjust cell references in the Formula as well as in the Macro also.
answered Dec 22 '18 at 7:05
Rajesh SRajesh S
1
answered Dec 22 '18 at 7:05
Rajesh SRajesh S
1
answered Dec 22 '18 at 7:05
Rajesh SRajesh S
1
answered Dec 22 '18 at 7:05
Rajesh SRajesh S
1
1
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.
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%2f1386599%2fexcel-multiple-find-formula%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
are you just looking to confirm the presence of multiple words or do you need to know where they appear in the string?
– RickyTillson
Dec 21 '18 at 10:19
What form of search result do you expect to get? Assuming that more that 1 word of search for list can be found...
– Akina
Dec 21 '18 at 10:22
It isn't clear what you mean by find different words in one cell. Please provide some example cells and what you want the result to be. This could refer to finding the presence or count of any, each, or all, and then how and where do you express the result?
– fixer1234
Dec 21 '18 at 10:25
I think you're misunderstanding what FIND does - this would return a number rather than the word. I assume what you want the formula to do is in return the words that match
– RickyTillson
Dec 21 '18 at 14:47
i need it to return as a word, i thought if you use " it would define the word?
– Imi
Dec 21 '18 at 15:01