问题描述：
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
FINDandSEARCH, however these start from left, what I need is to start from right.
解决办法一：
1 | =FIND("☃",SUBSTITUTE(A1," ","☃",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) |
说明：
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("☃",[...])will give you the position of the snowman. Which is the position of the last space.
解决办法二：
1 | // Here's another way, any characters are allowed in A1 (even snowmen!) =LOOKUP(2^15,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1))))) |
In Excel 2010 or later you could also use AGGREGATE function like this:
1 | =AGGREGATE(14,6,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))),1) |
说明：
FINDhas 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.
LOOKUPthen 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.
FINDreturns 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].
INDIRECTworks ->Assuming A1 has 12 characters then this part
"1:"&LEN(A1)evaluates to"1:12"that’s a string whichROWcan’t process butINDIRECTconverts a string to a valid reference whichROWcan handle, soROW(1:12)then gives you
{1;2;3;4;5;6;7;8;9;10;11;12};
FINDreturns an array, e.g if A1 containsbarry larry houdinithenFINDwill return this array{6;6;6;6;6;6;12;12;12;12;12;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}andLOOKUPand/orAGGREGATEwill take the last/largest numeric value from that, i.e12.
