Extract text from one column to three column with nested if

Hello, I need help on extract some text in column A using formula into three column. More rows will be added into column A, so text to column is not valid in my case.

Column A consists of request type (ad hoc or recurring), complexity (high, medium, low) and priority. They are filled up like below, in alphabetical order
Ad Hoc, Low complexity
Medium complexity, Recurring
Medium complexity, Priority, Recurring
High complexity, Priority, Recurring

I want to extract the information in column A in to the 3 columns, B "Complexity", C "Request Type", D "Priority":
eg. from above column A
first row: B is Low complexity, C is Ad Hoc, D is empty
second row: B is Medium complexity, C is Recurring, D is empty
third row: B is Medium complexity, C is Recurring, D is Priority

I tried to use nested if, but it is not working,
=IF(FIND("Low",A1),"Low complexity (orange)",IF(FIND("Medium",A1),"Medium Complexity (orange)"))

Appreciate advise on how can I extract the information into the 3 columns, thank you

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi pyd, try adding isnumber to your formula, e.g.:-

=IF(ISNUMBER(FIND("Low",A1)),"Low complexity (orange)",IF(ISNUMBER(FIND("Medium",A1)),"Medium Complexity (orange)","-"))

Hi,
Try these formulae in B1, C1 and D1, respectively,

=IFERROR(LOOKUP(1,1/SEARCH("Complexity",TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),1+(COLUMN($A$1:$C$1)-1)*LEN($A1),LEN($A1)))),TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),1+(COLUMN($A$1:$C$1)-1)*LEN($A1),LEN($A1)))),"")

=TEXTJOIN("",TRUE,IFERROR(IF(SEARCH({"Ad Hoc";"Recurring"},TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),1+(COLUMN($A$1:$C$1)-1)*LEN($A1),LEN($A1)))),{"Ad Hoc";"Recurring"}),""))

=IFERROR(LOOKUP(1,1/SEARCH("Priority",TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),1+(COLUMN($A$1:$C$1)-1)*LEN($A1),LEN($A1)))),TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",LEN($A1))),1+(COLUMN($A$1:$C$1)-1)*LEN($A1),LEN($A1)))),"")