Extract certain words from strings of text in Excel cells
I have a column of cells filled with a lot of text. I only need one word from each cell. If you look closely you will see that the latter part of each cell says either High, Medium, or Low.
I want to create a column next to this one that extracts this word (High, Medium, or Low) from the text string. My initial thought was to extract based on position, but the words are in varying positions.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013 microsoft-excel-2016
edited Jan 24 at 21:28
harrymc
261k14271577
asked Jan 24 at 21:20
forlornforlorn
103
add a comment |
I have a column of cells filled with a lot of text. I only need one word from each cell. If you look closely you will see that the latter part of each cell says either High, Medium, or Low.
I want to create a column next to this one that extracts this word (High, Medium, or Low) from the text string. My initial thought was to extract based on position, but the words are in varying positions.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013 microsoft-excel-2016
edited Jan 24 at 21:28
harrymc
261k14271577
asked Jan 24 at 21:20
forlornforlorn
103
add a comment |
I have a column of cells filled with a lot of text. I only need one word from each cell. If you look closely you will see that the latter part of each cell says either High, Medium, or Low.
I want to create a column next to this one that extracts this word (High, Medium, or Low) from the text string. My initial thought was to extract based on position, but the words are in varying positions.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013 microsoft-excel-2016
edited Jan 24 at 21:28
harrymc
261k14271577
asked Jan 24 at 21:20
forlornforlorn
103
I have a column of cells filled with a lot of text. I only need one word from each cell. If you look closely you will see that the latter part of each cell says either High, Medium, or Low.
I want to create a column next to this one that extracts this word (High, Medium, or Low) from the text string. My initial thought was to extract based on position, but the words are in varying positions.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013 microsoft-excel-2016
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2013 microsoft-excel-2016
edited Jan 24 at 21:28
harrymc
261k14271577
asked Jan 24 at 21:20
forlornforlorn
103
edited Jan 24 at 21:28
harrymc
261k14271577
asked Jan 24 at 21:20
forlornforlorn
103
edited Jan 24 at 21:28
harrymc
261k14271577
edited Jan 24 at 21:28
harrymc
261k14271577
edited Jan 24 at 21:28
harrymc
261k14271577
261k14271577
asked Jan 24 at 21:20
forlornforlorn
103
asked Jan 24 at 21:20
forlornforlorn
103
asked Jan 24 at 21:20
forlornforlorn
103
103
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
This will return the correct value:
=INDEX({"High","Medium","Low"},AGGREGATE(15,6,ROW($1:$3)/(ISNUMBER(SEARCH("*" & {"High";"Medium";"Low"} & "*",A1))),1))
It iterates the possible words and returns the one that does not return an error as a number in order to the INDEX.
answered Jan 24 at 22:05
Scott CranerScott Craner
12.3k11218
add a comment |
I know a function to extract part of a string inside two unique characters. e.g: -Medium- will be Medium.
This is the formula:
=MID(A3,SEARCH("-",A3)+1,SEARCH("-",A3,SEARCH("-",A3)+1)-SEARCH("-",A3)-1)
In order for this to work, you will need to convert your text as shown in the photo below to add the unique character (in this case a dash -). You can do it using the find and replace CTRL+H as follows:
- Select the column that has the text to extract.
- Press
CTRL+Hkey combination - Find what:
Low
- Replace with:
-Low-
- Click on
Replace Allbutton. - Repeat from step 3 to 5 for
MediumandHigh.
See the output below:
I hope this helps.
answered Jan 24 at 22:24
Manuel FlorianManuel Florian
1595
add a comment |
Here's a simple approach that uses wildcards.
The formula in B1:
=IF(COUNTIF(A1,"*Low*"),"Low",IF(COUNTIF(A1,"*Medium*"),"Medium",IF(COUNTIF(A1,"*High*"),"High","")))
It's just some nested IFs to check whether each term is contained in the cell. I added an extra IF test in case there could be a record that does not contain any of the terms (returns a blank).
answered Jan 24 at 22:55
fixer1234fixer1234
18.9k144982
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%2f1398088%2fextract-certain-words-from-strings-of-text-in-excel-cells%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
This will return the correct value:
=INDEX({"High","Medium","Low"},AGGREGATE(15,6,ROW($1:$3)/(ISNUMBER(SEARCH("*" & {"High";"Medium";"Low"} & "*",A1))),1))
It iterates the possible words and returns the one that does not return an error as a number in order to the INDEX.
answered Jan 24 at 22:05
Scott CranerScott Craner
12.3k11218
add a comment |
This will return the correct value:
=INDEX({"High","Medium","Low"},AGGREGATE(15,6,ROW($1:$3)/(ISNUMBER(SEARCH("*" & {"High";"Medium";"Low"} & "*",A1))),1))
It iterates the possible words and returns the one that does not return an error as a number in order to the INDEX.
answered Jan 24 at 22:05
Scott CranerScott Craner
12.3k11218
add a comment |
This will return the correct value:
=INDEX({"High","Medium","Low"},AGGREGATE(15,6,ROW($1:$3)/(ISNUMBER(SEARCH("*" & {"High";"Medium";"Low"} & "*",A1))),1))
It iterates the possible words and returns the one that does not return an error as a number in order to the INDEX.
answered Jan 24 at 22:05
Scott CranerScott Craner
12.3k11218
This will return the correct value:
=INDEX({"High","Medium","Low"},AGGREGATE(15,6,ROW($1:$3)/(ISNUMBER(SEARCH("*" & {"High";"Medium";"Low"} & "*",A1))),1))
It iterates the possible words and returns the one that does not return an error as a number in order to the INDEX.
answered Jan 24 at 22:05
Scott CranerScott Craner
12.3k11218
answered Jan 24 at 22:05
Scott CranerScott Craner
12.3k11218
answered Jan 24 at 22:05
Scott CranerScott Craner
12.3k11218
answered Jan 24 at 22:05
Scott CranerScott Craner
12.3k11218
12.3k11218
add a comment |
add a comment |
I know a function to extract part of a string inside two unique characters. e.g: -Medium- will be Medium.
This is the formula:
=MID(A3,SEARCH("-",A3)+1,SEARCH("-",A3,SEARCH("-",A3)+1)-SEARCH("-",A3)-1)
In order for this to work, you will need to convert your text as shown in the photo below to add the unique character (in this case a dash -). You can do it using the find and replace CTRL+H as follows:
- Select the column that has the text to extract.
- Press
CTRL+Hkey combination - Find what:
Low
- Replace with:
-Low-
- Click on
Replace Allbutton. - Repeat from step 3 to 5 for
MediumandHigh.
See the output below:
I hope this helps.
answered Jan 24 at 22:24
Manuel FlorianManuel Florian
1595
add a comment |
I know a function to extract part of a string inside two unique characters. e.g: -Medium- will be Medium.
This is the formula:
=MID(A3,SEARCH("-",A3)+1,SEARCH("-",A3,SEARCH("-",A3)+1)-SEARCH("-",A3)-1)
In order for this to work, you will need to convert your text as shown in the photo below to add the unique character (in this case a dash -). You can do it using the find and replace CTRL+H as follows:
- Select the column that has the text to extract.
- Press
CTRL+Hkey combination - Find what:
Low
- Replace with:
-Low-
- Click on
Replace Allbutton. - Repeat from step 3 to 5 for
MediumandHigh.
See the output below:
I hope this helps.
answered Jan 24 at 22:24
Manuel FlorianManuel Florian
1595
add a comment |
I know a function to extract part of a string inside two unique characters. e.g: -Medium- will be Medium.
This is the formula:
=MID(A3,SEARCH("-",A3)+1,SEARCH("-",A3,SEARCH("-",A3)+1)-SEARCH("-",A3)-1)
In order for this to work, you will need to convert your text as shown in the photo below to add the unique character (in this case a dash -). You can do it using the find and replace CTRL+H as follows:
- Select the column that has the text to extract.
- Press
CTRL+Hkey combination - Find what:
Low
- Replace with:
-Low-
- Click on
Replace Allbutton. - Repeat from step 3 to 5 for
MediumandHigh.
See the output below:
I hope this helps.
answered Jan 24 at 22:24
Manuel FlorianManuel Florian
1595
I know a function to extract part of a string inside two unique characters. e.g: -Medium- will be Medium.
This is the formula:
=MID(A3,SEARCH("-",A3)+1,SEARCH("-",A3,SEARCH("-",A3)+1)-SEARCH("-",A3)-1)
In order for this to work, you will need to convert your text as shown in the photo below to add the unique character (in this case a dash -). You can do it using the find and replace CTRL+H as follows:
- Select the column that has the text to extract.
- Press
CTRL+Hkey combination - Find what:
Low
- Replace with:
-Low-
- Click on
Replace Allbutton. - Repeat from step 3 to 5 for
MediumandHigh.
See the output below:
I hope this helps.
answered Jan 24 at 22:24
Manuel FlorianManuel Florian
1595
answered Jan 24 at 22:24
Manuel FlorianManuel Florian
1595
answered Jan 24 at 22:24
Manuel FlorianManuel Florian
1595
answered Jan 24 at 22:24
Manuel FlorianManuel Florian
1595
1595
add a comment |
add a comment |
Here's a simple approach that uses wildcards.
The formula in B1:
=IF(COUNTIF(A1,"*Low*"),"Low",IF(COUNTIF(A1,"*Medium*"),"Medium",IF(COUNTIF(A1,"*High*"),"High","")))
It's just some nested IFs to check whether each term is contained in the cell. I added an extra IF test in case there could be a record that does not contain any of the terms (returns a blank).
answered Jan 24 at 22:55
fixer1234fixer1234
18.9k144982
add a comment |
Here's a simple approach that uses wildcards.
The formula in B1:
=IF(COUNTIF(A1,"*Low*"),"Low",IF(COUNTIF(A1,"*Medium*"),"Medium",IF(COUNTIF(A1,"*High*"),"High","")))
It's just some nested IFs to check whether each term is contained in the cell. I added an extra IF test in case there could be a record that does not contain any of the terms (returns a blank).
answered Jan 24 at 22:55
fixer1234fixer1234
18.9k144982
add a comment |
Here's a simple approach that uses wildcards.
The formula in B1:
=IF(COUNTIF(A1,"*Low*"),"Low",IF(COUNTIF(A1,"*Medium*"),"Medium",IF(COUNTIF(A1,"*High*"),"High","")))
It's just some nested IFs to check whether each term is contained in the cell. I added an extra IF test in case there could be a record that does not contain any of the terms (returns a blank).
answered Jan 24 at 22:55
fixer1234fixer1234
18.9k144982
Here's a simple approach that uses wildcards.
The formula in B1:
=IF(COUNTIF(A1,"*Low*"),"Low",IF(COUNTIF(A1,"*Medium*"),"Medium",IF(COUNTIF(A1,"*High*"),"High","")))
It's just some nested IFs to check whether each term is contained in the cell. I added an extra IF test in case there could be a record that does not contain any of the terms (returns a blank).
answered Jan 24 at 22:55
fixer1234fixer1234
18.9k144982
answered Jan 24 at 22:55
fixer1234fixer1234
18.9k144982
answered Jan 24 at 22:55
fixer1234fixer1234
18.9k144982
answered Jan 24 at 22:55
fixer1234fixer1234
18.9k144982
18.9k144982
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%2f1398088%2fextract-certain-words-from-strings-of-text-in-excel-cells%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
