Thanks:  0
Likes:  0

Thread: Find character counting from the right

1. Find character counting from the right

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!

2. Re: Find character counting from the right

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

3. Re: Find character counting from the right

Is this it:

Code:
`=(LEN(A1)-FIND(" ",A1))+1`

4. Re: Find character counting from the right

Originally Posted by uzaname
Is this it:

Code:
`=(LEN(A1)-FIND(" ",A1))+1`
That gives 12?

5. Re: Find character counting from the right

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

6. Re: Find character counting from the right

Originally Posted by uzaname
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

7. Re: Find character counting from the right

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

8. Re: Find character counting from the right

Originally Posted by FDibbins
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

This does it!

Thanks so much.

9. Re: Find character counting from the right

Originally Posted by uzaname
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```
There is a much simpler VBA solution than that available...
Code:
```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...
Code:
```Function LastSpace(S As String) As Long
If S Like "* *" Then LastSpace = Len(S) - InStrRev(S, " ") + 1
End Function```

10. Re: Find character counting from the right

Perhaps

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

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•