excel - Return all text to the right of last numeric digit in strings -

excel - Return all text to the right of last numeric digit in strings -

return right text string of cell until first number right e.g.

"geb. 14 oct 1956 westerkerk hrl" must homecoming westerkerk hrl "geb. 14 oct 1956" must homecoming empty cell " " must homecoming empty cell

formula solution:

=trim(mid(a1,1+max(find({1,2,3,4,5,6,7,8,9,0},a1&"1234567890")*isnumber(find({1,2,3,4,5,6,7,8,9,0},a1))),len(a1)))

edit: realized above formula may run issues if there repeated number (for illustration if had been 1966 instead of 1956), went , came not affected issue:

=trim(mid(a1,1+lookup(2,1/isnumber(--mid(a1,row(indirect("1:"&len(a1))),1)),row(indirect("1:"&len(a1)))),len(a1)))

however, indirect volatile function , seek avoid it. instead utilize number going higher length of text strings in order accomplish same goal without volatile function, though may not faster using indirect function in formula:

=trim(mid(a1,1+lookup(2,1/isnumber(--mid(a1,row($1:$255),1)),row($1:$255)),len(a1)))

so basically, can utilize either formula, whichever prefer.

excel function substring