To locate the last spaces, please use FIND function combined with SUBSTITUTE and LEN function.
1. To locate the last space:
=FIND("/", SUBSTITUTE(A2," ","/", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))
Explanations:
– SUBSTITUTE(A2," ",""): Remove the spaces in the text string;
– LEN(SUBSTITUTE(A2," ","")): The length of the text string after removing spaces;
– LEN(A2): The length of the text string;
– LEN(A2)-LEN(SUBSTITUTE(A2," ","")): The number of spaces in the text string;
– SUBSTITUTE(A2," ","/",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))): To replace the last space with "/" (or other special characters) to differentiate with other spaces;
– FIND("/",SUBSTITUTE(A2," ","/",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))): To find the location of the "/" in the text string, which is the location of the last space.
2. To locate the second last space:
=FIND("/",SUBSTITUTE(Cell," ","/", LEN(Cell)- LEN(SUBSTITUTE(Cell," ",""))-1))
Explanations:
– SUBSTITUTE(Cell," ",""): Remove the spaces in the text string;
– LEN(SUBSTITUTE(Cell," ","")): The length of the text string after removing spaces;
– LEN(Cell): The length of the text string;
– LEN(Cell)-LEN(SUBSTITUTE(Cell," ","")): The number of spaces in the text string;
– LEN(Cell)-LEN(SUBSTITUTE(Cell," ",""))-1: The number of spaces minus 1, or the second last space in the text string;
– SUBSTITUTE(Cell," ","/",LEN(Cell)-LEN(SUBSTITUTE(Cell," ",""))-1): To replace the second last space with "/" (or other special characters) to differentiate with other spaces;
– FIND("/",SUBSTITUTE(Cell," ","/",LEN(Cell)-LEN(SUBSTITUTE(Cell," ",""))-1)): To find the location of the "/", which is the location of the second last space.
3. To locate the third last space:
=FIND("/",SUBSTITUTE(Cell," ","/",LEN(Cell)-LEN(SUBSTITUTE(Cell," ",""))-2))
Explanations:
– SUBSTITUTE(Cell," ",""): Remove the spaces in the text string;
– LEN(SUBSTITUTE(Cell," ","")): The length of the text string after removing spaces;
– LEN(Cell): The length of the text string;
– LEN(Cell)-LEN(SUBSTITUTE(Cell," ","")): The number of spaces in the text string;
– LEN(Cell)-LEN(SUBSTITUTE(Cell," ",""))-2: The number of spaces minus 2, or the third last space in the text string;
– SUBSTITUTE(Cell," ","/",LEN(Cell)-LEN(SUBSTITUTE(Cell," ",""))-2): To replace the third last space with "/" (or other special characters) to differentiate with other spaces;
– FIND("/",SUBSTITUTE(Cell," ","/",LEN(Cell)-LEN(SUBSTITUTE(Cell," ",""))-2)): To find the location of the "/", which is the location of the third last space.
4. To locate the Nth last space:
=FIND("/",SUBSTITUTE(Cell," ","/", LEN(Cell)-LEN(SUBSTITUTE(Cell," ",""))-N+1))