I have very long address data and trying to clean up
my address column looks like
Q:
13 ABC LANE NE
PO BOX XXX MM NK
XX STREET PENA PE
10TH CROSS

Ans:
13 ABC LANE
PO BOX XXX MM
XX STREET PENA
10TH CROSS

I am trying to removed only last word "NE", "NK", "PE" from the text(if text have). I used formula =LEFT(A1,LEN(A1)-2), This formula worked on first three row but did not work on 4th row (10TH CRO). I used =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"NE",""),"NK",""),"PE","") but it did not work, remove NE from LANE and PE from PENA.

So I am looking to combine few excel function together: IF text possess "NE", "NK", "PE" as last word from LEFT, FIND and SUBSTITUTE as blank ("") or REMOVE, otherwise leave it as A1.

Any suggestion. Much appreciated.

Thanks