Find pos of next whitespace char?
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...) Robert Crandal |
Find pos of next whitespace char?
On Wed, 13 Apr 2011 00:57:27 -0700, "Robert Crandal" wrote:
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...) Robert Crandal VBA does not have a shortcut for a whitespace character although it does have an Instrrev function. You can use character classes but not with Instrrev. You can use it with the Like operator. So to look for white space backwards in a string, using native VBA, you could use something like: ================================ Sub foo() Const StringCheck As String = "ABC DEF xyz*ghi " 'The character class contains <space<tab<nbsp Const WhiteSpace As String = "[ *]" Dim i As Long Debug.Print InStrRev(StringCheck, WhiteSpace) For i = Len(StringCheck) To 1 Step -1 If Mid(StringCheck, i, 1) Like WhiteSpace Then Debug.Print i End If Next i End Sub =================================== Or you could use Regular Expressions in VBA. For some reason, the whitespace shorthand does not include the nbsp, so that needs to be added separately if needed; ================================= Sub WhiteSpaceReverseSearch() Const StringCheck As String = "ABC DEF xyz*ghi " Const sPat As String = "[\s\xA0]" Dim re As Object, mc As Object, m As Object Dim i As Long Set re = CreateObject("vbscript.regexp") re.Pattern = sPat re.Global = True If re.test(StringCheck) Then Set mc = re.Execute(StringCheck) 'matches locations listed in order For Each m In mc Debug.Print m.firstindex + 1 Next m Debug.Print vbLf 'match locations listed in reverse For i = mc.Count To 1 Step -1 Debug.Print mc(i - 1).firstindex + 1 Next i End If End Sub =========================== Perhaps with more detail about what you are trying to accomplish, a better solution can be devised. |
Find pos of next whitespace char?
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 Briscoe |
Find pos of next whitespace char?
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 ========================= |
Find pos of next whitespace char?
In message of Wed, 13 Apr
2011 09:58:38 in microsoft.public.excel.programming, Ron Rosenfeld writes 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 [snipped my proposed code] 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) I had forgotten that. 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. And that. ;) 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 The use of Global and execute to get the last match is magical. If re.test(StringCheck) Then Set mc = re.Execute(StringCheck) The OP's requirement might be better served with something like Set mc = re.Execute(left(StringCheck,len(StringCheck)/2)) LastWhiteSpace = mc(mc.Count - 1).firstindex + 1 End If End Function ========================= I find I do not like Execute. I probably use Replace more than I should as I encapsulate it in: Option Explicit ' Force data declaration Dim RE As Object Private Sub EnsureREInitialized() If RE Is Nothing Then Set RE = CreateObject("VBScript.Regexp") RE.Global = True End If End Sub Private Function GetSub(ByVal from As String, ByVal Match As String, _ ByVal Part As String) As String EnsureREInitialized RE.Pattern = Match GetSub = RE.Replace(from, Part) End Function I might prefer RE to be a static variable, but then would not have an easy access to Set RE = Nothing I also encapsulate test with Private Function IsMatch(ByVal from As String, ByVal Match As String) _ As Boolean EnsureREInitialized RE.Pattern = Match IsMatch = RE.test(from) End Function I have yet to evolve a one liner using execute. These are typical uses of those encapsulations. If IsMatch(S, "^00:00 .+\r\n00:00 .+$") Then O.innerhtml = GetSub(O.innerhtml, "<IMG (?:border=0 )?alt=(?:""([^""]+)""|(\w+)) src=[^]+", " $1$2;") When looking for typical uses, I found some uses of getsub which should be replaced by IsMatch. My code is never perfect. ;) P.S. Can anyone here recommend a public forum to discuss html? I find Microsoft's offerings in place of Usenet unattractive. I find the DOM (Document Object Model) for a site I use is completely different in IE9 than IE8. e.g. <http://www.tfl.gov.uk/tfl/livetravelnew s/realtime/track.aspx?offset=7 I can scrape data from this in IE8, but have yet to find why the model is radically different in IE9. I installed IE9, hit a wall I could not easily climb, and restored IE8. I suppose I ought to put 8 and 9 on two machines and compare. That is probably something to do on holiday. -- Walter Briscoe |
Find pos of next whitespace char?
On Wed, 13 Apr 2011 18:11:46 +0100, Walter Briscoe wrote:
I find I do not like Execute. I probably use Replace more than I should as I encapsulate it in: Yes, I have a group regex functions in a personal add-in which can be called similarly for my own uses. But when responding here, I usually provide routines which are just focused on the primary task. And in my own work, I find using early binding is more convenient, especially since I get those little reminders as to what all the valid arguments might be. |
All times are GMT +1. The time now is 02:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com