0

My data is stored in Column F, the raw data looks like this:

The Images- had poor pixel quality.

What I want to do is to create an array formula that will look for my key word which is a string and convert that string keyword value to an int value.

Video= 1
Audio=2
Jpeg=3
Images=4

This is what I tried, but I just can't seem to work out the logic as it doesn't seem to recognize the order of my second array list.

lookup(F2,
       {"Video","Audio","Jpeg","Images"},
       {"1", "2","3","4"}
  ) 
2
  • Your posted image is not showing. However, an image is not usually helpful alone. Please share a link to your sheet (or to a copy of it).
    – Erik Tyler
    Feb 2 at 19:32
  • Hi and welcome to WebApps. Just to be clear, and please correct me if I am wrong. the cells in Column F consists strings. Each string includes one (and only one) of the following four words "Video","Audio","Jpeg","Images". You want to evaluate the string against the list of words, and substitute an integer value depending on the word list (Video= 1 Audio=2 Jpeg=3 Images=4). For example, a the string "The Images- had poor pixel quality." would be replaced by '4' because the string contains the word "Images. Is that right?
    – Tedinoz
    Feb 3 at 22:23

1 Answer 1

0

This answer is based on the Docs Editors Help Topic Google Sheet Arrayformula + IF + REGEXMATCH contains text within a range

There are two options:

  • a formula using an external list of search strings and corresponding values ("Mapping")
  • a formula containing the various search strings and values (no Mapping).

Mapping

=ArrayFormula(IFNA(vLOOKUP(regexextract(A2:A6,TEXTJOIN("|",1,Mapping!A2:A9)),Mapping!A2:B9,2,0),"Other"))

Not Mapped =arrayformula(IF(REGEXMATCH(A2:A6,"Video"),1,IF(REGEXMATCH(A2:A6,"Audio"),2,IF(REGEXMATCH(A2:A6,"Jpeg"),3,IF(REGEXMATCH(A2:A6,"Images"),4,"Other")))))


Sample data and results

sample

Mapping

map

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.