Reverse find

[cooldyood]

Is there a built-in function to find the last space in a string? In other words, I want to search within a string from right to left.

[Ron Rosenfeld]

On Sat, 22 Jul 2006 21:13:29 -0400, cooldyood
<cooldyood.2bdj77_1153617310.5571@excelforum-nospam.com> wrote:

>
>Is there a built-in function to find the last space in a string? In
>other words, I want to search within a string from right to left.

Number of Last Space:

=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))


--ron

[Bob Flanagan]

untested:

anyS ="cell.value
for I = len(anyS) to 1 step -1
if mid(anys, i,1) = " " then
msgbox "last space found"
exit for
end if
next

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"cooldyood" <cooldyood.2bdj77_1153617310.5571@excelforum-nospam.com> wrote
in message news:cooldyood.2bdj77_1153617310.5571@excelforum-nospam.com...
>
> Is there a built-in function to find the last space in a string? In
> other words, I want to search within a string from right to left.
>
>
> --
> cooldyood
> ------------------------------------------------------------------------
> cooldyood's Profile:
> http://www.excelforum.com/member.php...o&userid=35611
> View this thread: http://www.excelforum.com/showthread...hreadid=564043
>

[Ron Rosenfeld]

On Sat, 22 Jul 2006 21:22:25 -0400, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>On Sat, 22 Jul 2006 21:13:29 -0400, cooldyood
><cooldyood.2bdj77_1153617310.5571@excelforum-nospam.com> wrote:
>
>>
>>Is there a built-in function to find the last space in a string? In
>>other words, I want to search within a string from right to left.
>
>Number of Last Space:
>
>=FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
>
>
>--ron

I overlooked that this is the Programming group. The VBA function you want is
InStrRev.

==============================
Option Explicit

Sub LastSpace()
Const sTestString As String = "This is a Test"
Dim lLastSpace As Long
Const sSpace As String = " "

lLastSpace = InStrRev(sTestString, sSpace)

Debug.Print "The Last Space is at location " & lLastSpace

End Sub
===============================
The Last Space is at location 10
-----------------------------------


--ron