Find character counting from the right

[Sheridi]

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!

 

[FDibbins]

Probably a better way to do this, but this works...
=LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1

 

[uzaname]

Is this it:

=(LEN(A1)-FIND(" ",A1))+1

 

[FDibbins]

Is this it:

=(LEN(A1)-FIND(" ",A1))+1

That gives 12?

 

[uzaname]

I thinkit only works if there is only one space. Here's a VBA function:

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

 

[FDibbins]

I thinkit only works if there is only one space. Here's a VBA function:

the formula I suggested, while a bit cumbersome, returns 5 in the provided sample, and works on other samples I created...

	A​	B​
1​	now is the time	5​
2​	now is the too	4​
3​	now one t	2​
4​	now two two aabbccdd	9​

B1=LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1
copied down

 

[uzaname]

Yes, sorry, I meant my first one was wrong.

 

[Sheridi]

the formula I suggested, while a bit cumbersome, returns 5 in the provided sample, and works on other samples I created...

	A​	B​
1​	now is the time	5​
2​	now is the too	4​
3​	now one t	2​
4​	now two two aabbccdd	9​

<tbody>
</tbody>

B1=LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1
copied down

This does it!

Thanks so much.

 

[Rick Rothstein]

I thinkit only works if there is only one space. Here's a VBA function:

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

There is a much simpler VBA solution than that available...

Function Test(S As String) As Long
  Test = InStrRev(S, " ")
End Function

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...

Function LastSpace(S As String) As Long
  If S Like "* *" Then LastSpace = Len(S) - InStrRev(S, " ") + 1
End Function

 

[mikerickson]

Perhaps

Function LastSpaceFromRight(aString As String) As Long
    LastSpaceFromRight = 1 + Len(Split(StrReverse(aString), " ")(0))
End Function