Table of Contents
Finding the last space in a string using Excel can easily be achieved by utilizing the built-in functions and formulas provided by the software. First, the user can use the “LEN” function to determine the length of the string. Then, the “FIND” function can be used to search for the last space character in the string, by specifying the space character as the search parameter and the length of the string as the starting point. This will return the position of the last space in the string. Alternatively, the “RIGHT” function can also be used to extract the last word in the string, which will automatically exclude any spaces at the end. These methods allow for efficient and accurate identification of the last space in a string within Excel.
Excel: Find Last Space in a String
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.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I find the last space in a string using Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-find-the-last-space-in-a-string-using-excel/
stats writer. "How can I find the last space in a string using Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-find-the-last-space-in-a-string-using-excel/.
stats writer. "How can I find the last space in a string using Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-find-the-last-space-in-a-string-using-excel/.
stats writer (2024) 'How can I find the last space in a string using Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-find-the-last-space-in-a-string-using-excel/.
[1] stats writer, "How can I find the last space in a string using Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I find the last space in a string using Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.