Data cleaning

[J_koya]

Given my data.... Looks like this
an Anambra state
ab Abia state
as Adamawa
cc federal capital Territory
de delta
ed Edo.. etc (data set in the first picture)
And I want to extract just the name.. for it to look like this
Anambra
Abia
Adamawa
Federal capital Territory
Delta
Edo... (Second screenshot)
Already used trim to remove spaces... but To extract only state name for It to look like this... And the numbers from numerator... Which formula(s) do you suggest does these effectively
How can I do this with excel

[#2]

@J_koya 

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","")

[#3]

@J_koya 

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