Feb 06 2021 06:44 AM
Feb 06 2021 07:00 AM
I'll help you with the first one, but since it is a homework assignment, you should try the other ones yourself.
=SUBSTITUTE(SUBSTITUTE(MID(TRIM(Raw_data!B2),FIND(" ",TRIM(Raw_data!B2))+1,100)," State","")," state","")
Feb 06 2021 07:05 AM
It all depends on which version of Excel you, do you willing to transform source data into the structured table, do you consider Power Query as an option.
I addition to @Hans Vogelaar , first couple of formulas could be
=LET( txtTrim, TRIM(B2), txtNoPrefix, IF(SEARCH(" ",txtTrim)=3, REPLACE(txtTrim,1,3,""), txtTrim), name, TRIM(SUBSTITUTE(SUBSTITUTE(txtNoPrefix,"state",""),"State","")), name)
and
=FILTERXML(C2,"//span")