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

On Sat, 22 Jul 2006 21:13:29 -0400, cooldyood
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

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" wrote
in message ...

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

On Sat, 22 Jul 2006 21:22:25 -0400, Ron Rosenfeld
wrote:

On Sat, 22 Jul 2006 21:13:29 -0400, cooldyood
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