You can use the following formula to find the position of the last space in a string in Excel:
=FIND("/", SUBSTITUTE(A2," ","/", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))
This particular formula finds the position of the last space in the string in cell A2.
For example, if the string in cell A2 is “Hello everyone” then this formula would return a value of 6 since this the position in the string where the first space occurs.
You can also use the following formula to find the total number spaces in a string:
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
For example, if the string in cell A2 is “Hello everyone” then this formula would return a value of 1 since there is only one space in the string.
The following example shows how to use each of these formulas in practice.
Example: How to Find Last Space in String in Excel
Suppose we have the following column of strings in Excel:
We can type the following formula into cell B2 to find the position of the last space in cell A2:
=FIND("/", SUBSTITUTE(A2," ","/", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays the position of the last space for each string in column A.
We can also type the following formula into cell C2 to find the total number of spaces in the string in cell A2:
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
We can then click and drag this formula down to each remaining cell in column C:
Column C now displays the total number of spaces in each string in column A.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Remove Specific Text from Cells
Excel: How to Remove Last 3 Characters from String
Excel: How to Insert a Character into a String
