Find Last Blank Value (Space) in Excel Cell

How to find Last Blank Value, which is nothing but Space in a Excel Cell? This might be useful to get the Last word separated in to another column. For Example - You want to get City Name into the another column from the Address mentioned in the First column.

Firstly, Lets start with find the last blank value. Formula would be :

FIND("☃",SUBSTITUTE(A1," ","☃",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

You can divide it into 3 parts:

LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

gives you the number of spaces, let's call it x,

SUBSTITUTE(A1," ","☃",[x])

will replace the xth space (so the last one) by a snowman,

FIND("☃",[Above Formula])

will give you the position of the snowman. Which is the position of the last space.

Now then this position of the last space is from the left, but if you want to get the last word after last space separated into another column, you can use Right() formula, but for that you would required the position of the space from right instead of left.

LEN(A1)-FIND("☃",SUBSTITUTE(A1," ","☃",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

would provide you the last space location from right.

Now to get the last word after space into another column, just apply RIGHT() Formula

RIGHT(A1,LEN(A1)-FIND("☃",SUBSTITUTE(A1," ","☃",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Hope this works for you and you get going in your task. Happy Exceling