Probably a better way to do this, but this works...
=LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1
I know I've seen this done but for the life of me can't figure it out.
I want to find the first space in a text string (in a cell) counting from the right.
ie.... in Cell A1 I have "now is the time"
I want a formula to tell me that the first space from the right is at position 5.
Thanks!
Probably a better way to do this, but this works...
=LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Regards
Ford
Is this it:
Code:=(LEN(A1)-FIND(" ",A1))+1
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Regards
Ford
I thinkit only works if there is only one space. Here's a VBA function:
Code:Function TEST(r As Range) As Long For i = Len(r.Value) To 1 Step -1 If Mid(r.Value, i, 1) = " " Then TEST = i Exit Function End If Next i End Function
the formula I suggested, while a bit cumbersome, returns 5 in the provided sample, and works on other samples I created...
A B 1now is the time 5 2now is the too 4 3now one t 2 4now two two aabbccdd 9
B1=LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1
copied down
- Posting guidelines, forum rules and terms of use
- Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
Regards
Ford
Yes, sorry, I meant my first one was wrong.
There is a much simpler VBA solution than that available...
However, these functions do not give the answer the OP wanted. The OP wanted the count from the right side of the text, our functions above give the count from the left side. Although the OP has a formula solution, for completeness sake, here is the UDF version...Code:Function Test(S As String) As Long Test = InStrRev(S, " ") End Function
Code:Function LastSpace(S As String) As Long If S Like "* *" Then LastSpace = Len(S) - InStrRev(S, " ") + 1 End Function
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
Perhaps
Code:Function LastSpaceFromRight(aString As String) As Long LastSpaceFromRight = 1 + Len(Split(StrReverse(aString), " ")(0)) End Function
Last edited by mikerickson; May 1st, 2017 at 02:52 AM.
Like this thread? Share it with others