I have an Excel sheet with text values, e.g. in A1: "This is the text". I need to find the position of the last space character. Is there an excel formula that will give me the result I want? I have tried FIND and SEARCH, however these start from left, what I need is to start from right.
If by blank value you mean "space", and by XLS you mean Excel, then you can use this formula (assuming the test you want to find the last space in is in A1
):
=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 itx
,SUBSTITUTE(A1," ","☃",[x])
will replace thex
th space (so the last one) by a snowman,FIND("☃",[...])
will give you the position of the snowman. Which is the position of the last space.

Works perfect. Thanks. It took me a while to figure out why it worked inspite of your explanation :) – Dheer Nov 26 '13 at 12:57

4+1 for a nice, detailed answer, and the most creative replacement character yet. – Doug Glancy Nov 26 '13 at 20:39
Here's another way, any characters are allowed in A1 (even snowmen!)
=LOOKUP(2^15,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))
FIND
has a third argument that defines the start position of the search, if you apply an array of integer values 1 to n (where n is the length of A1) to that parameter you get an array back with the last number being the position of the last space.
LOOKUP
then extracts that number by searching for a value greater than any value that might be found in that array, in which case the last number is found
In Excel 2010
or later you could also use AGGREGATE
function like this
=AGGREGATE(14,6,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))),1)
FIND
returns the same array as before and by using 14 as 1st argument of AGGREGATE
and 1 as the last you get the largest value in the array, whilst ignoring errors [6]

1works great. Although I was developer some [long] time ago I still can't figure out how and why INDIRECT works :) – Dheer Nov 27 '13 at 4:41

1Assuming A1 has 12 characters then this part
"1:"&LEN(A1)
evaluates to "1:12"  that's a string which ROW can't process but INDIRECT converts a string to a valid reference which ROW can handle so ROW(1:12) then gives you {1;2;3;4;5;6;7;8;9;10;11;12} – barry houdini Nov 27 '13 at 10:25 
Thanks for clarif. Does make some sense now. Which means Find itself implictly works with the array and gets executed for every value as starting position ... – Dheer Nov 27 '13 at 11:34

That's right, FIND returns an array, e.g if A1 contains
barry larry houdini
then FIND will return this array{6;6;6;6;6;6;12;12;12;12;12;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
and LOOKUP and/or AGGREGATE will take the last/largest numeric value from that, i.e 12 – barry houdini Nov 27 '13 at 11:59