string - Excel: Returning characters to the left and right of the last (or second to last) occurrence? -

string - Excel: Returning characters to the left and right of the last (or second to last) occurrence? -

i have string this: 100 stupid street ; authorities tower 7; chicago palm, ma 92111

i want homecoming characters left of lastly semicolon.

so, left of lastly semicolon be: 100 stupid street ; authorities tower 7

that step 1. extract characters string: chicago palm, ma 92111

i want homecoming characters right , left of sec lastly space.

so, right of sec lastly space be: ma 92111

to left of sec lastly space be: chicago palm,

if there way remove lastly comma in lastly formula, better.

this formula has helped me bit: =trim(right(substitute(a1,";",rept(" ",len(a1))),len(a1)))

cell a1 has original string. formula returns right of lastly semicolon.

thanks incoming help!!

if looking parse out of pieces, of previous returns can assist in next operation(s) portions of formulas not have repeated.

    

b1 =left(substitute(a3, ";", "×",len(a3)-len(substitute(a3,";",""))), find("×",substitute(a3&"×", ";", "×",len(a3)-len(substitute(a3,";",""))))-1)

b2 =trim(right(substitute(a2,";",rept(" ",99)),99))

b3 =trim(right(substitute(a3,",",rept(" ",99)),99))

b4 =trim(substitute(substitute(a4,b3,""),",",""))

string excel return formula semicolon