How to Locate the Last Space in the Text String

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))