On Wed, 13 Apr 2011 12:53:51 +0100, Walter Briscoe wrote:

In message of Wed, 13
Apr 2011 00:57:27 in microsoft.public.excel.programming, Robert Crandal
writes
The variable "n" will be used to denote the midpoint
of my string. Starting at position "n", I need to
traverse the string backwards (or LEFT) and find
the position of the first whitespace character.

Does VBA have a function that does this??
I'm also not sure which function determines if
a character is a whitespace (ie, space, tab, etc...)

I would very much doubt that VBA has such a function.
OTOH, I think it can probably be done in several ways.
I would use Regular expressions for such complicated requirements
The hard thing is getting tab and other non space characters into
strings. I leave generating data to the OP.
He might try playing with this in the debugger.

Option Explicit ' Require all variables to be explicitly declared.

Sub last_whitespace()
Dim S As String
Dim n As Integer

S = "a b c" & Chr(9) & "defghijk" ' Data to analyse
n = Len(S) / 2
Dim S2 As String
S2 = Left(S, n) ' Copy of left half of data
Dim RE As Object
Set RE = CreateObject("VBScript.Regexp")
RE.Pattern = "^(.*[ " & Chr(9) & "]).*$"
' That RE is a sequence of
' ^ Beginning of input
' ( Start remembering match
' .* Any character except a newline 0 or more times
' [ chr(9)] Either a space or a tab
' ) end of match
' .*$ The rest of the input
If Not RE.test(S2) Then
MsgBox "No whitespace found"
Else
MsgBox "Last whitespace at " & Len(RE.Replace(S2, "$1"))
' $1 is the remembered match. Everything else is discarded.
End If
'
End Sub

Walter,

Two points:

\s is a shortcut for white space in this flavor and is equivalent to [ \f\n\r\t\v] (note the <space at the start)

It does not include the non-break space <nbsp but you could just set

re.pattern = "[\s\xA0]" to achieve the same thing.

The "Match" has a property called FirstIndex which is a count of all the characters to the left of the match.

To return just the right most location of white space, something like this might be a bit simpler:

=================
Option Explicit
Function LastWhiteSpace(StringCheck As String)
Const sPat As String = "[\s\xA0]"
Dim re As Object, mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

If re.test(StringCheck) Then
Set mc = re.Execute(StringCheck)
LastWhiteSpace = mc(mc.Count - 1).firstindex + 1
End If

End Function
=========================