0

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.

enter image description here

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.

0

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.

enter image description here

0

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:

  1. Select the column that has the text to extract.
  2. Press CTRL+H key combination
  3. Find what: Low
  4. Replace with: -Low-
  5. Click on Replace All button.
  6. Repeat from step 3 to 5 for Medium and High.

See the output below:

here

I hope this helps.

0

Here's a simple approach that uses wildcards.

enter image description here

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.