I have tried several formulas to extract the last word in a string.
In all cases the formula works sometimes. This I cannot understand.
In column B I have
=MID(X16,FIND("☃",SUBSTITUTE(X16," ","☃",LEN(X16)-LEN(SUBSTITUTE(X16," ",""))))+1,10)
I have also tried
=TRIM(SUBSTITUTE(REGEX($X25,"[^ ]*$"),",",""))
I have checked whether the last space in A is the same sort of space: it is the same space whether B returns anything or not.
Can anyone tell me what I need to change?
A | B |
---|---|
6 PRINCESS PARADE NEW ROAD DAGENHAM | DAGENHAM |
CHARLOTTE ROAD DAGENHAM | |
CHARLOTTE ROAD DAGENHAM | |
GREATFIELDS ROAD BARKING | |
GROUND FLOOR PARSLOES AVENUE DAGENHAM | DAGENHAM |