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!

 

[Rick Rothstein]

Perhaps

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

Returns the length of the string if that string contains no spaces. Also returns #VALUE! error for a blank cell. My code did the same thing before I put the If..Then test that I used in front of it. As an aside, the InStrRev function call I used is more efficient than your Split(StrReverse combination.

 

[mikerickson]

Or

LastSpaceFromRight = InStr(StrReverse(aString), " ")

 

[Rick Rothstein]

Or

LastSpaceFromRight = InStr(StrReverse(aString), " ")

Now that one I like!

 

[Sheridi]

Never mind.........