Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Suppose I have the following string in a variable:
sLine = "abc G. James #000" I'm only interested in extracting TWO fragments as follows: The first extracted fragment should be "abc" The second extracted fragment should be "G. James #000" Is there a regular expression pattern that might be able to extract the above fragments? In general, I'm trying to divide any input string into 2 pieces, and the dividing point is the first set of whitespaces that occur in the line. In the above example, the dividing point occurs right after the "abc". Clear as mud? (Haha) Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Hi,
Am Tue, 14 May 2019 02:57:53 -0700 (PDT) schrieb RG III: sLine = "abc G. James #000" I'm only interested in extracting TWO fragments as follows: The first extracted fragment should be "abc" The second extracted fragment should be "G. James #000" Is there a regular expression pattern that might be able to extract the above fragments? In general, I'm trying to divide any input string into 2 pieces, and the dividing point is the first set of whitespaces that occur in the line. In the above example, the dividing point occurs right after the "abc". try it this way: sLine = "abc G. James #000" sLine = Application.Substitute(sLine, " ", "*", 1) varTmp = Split(sLine, "*") str1 = varTmp(0) str2 = Trim(varTmp(1)) Regards Claus B. -- Windows10 Office 2016 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Holy sheet! That works better than regular expression! 8)
You guys are the best. Thanks again! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Hi,
Am Tue, 14 May 2019 03:30:24 -0700 (PDT) schrieb RG III: Holy sheet! That works better than regular expression! 8) another way: sLine = "abc G. James #000" str1 = Left(sLine, InStr(sLine, " ") - 1) str2 = Trim(Replace(sLine, str1, "")) Regards Claus B. -- Windows10 Office 2016 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Hi Claus. Here's a different problem.
How can I test if a string begins with 1) one or more characters, 2) is followed by one or more whitespace characters, 3) and followed by any character string mixed with anything, including whitespaces? Here are some example strings that meet the above criteria: "b908 g" "what g. no 99" "a b" Here are some example strings that do NOT meet the criteria: "b9000 " ' There are no chars after the whitespaces "abc" ' Missing whitespaces and chars at the end "ab" ' Missing whitespaces and chars at the end I basically want to run a true or false test to check if the string follows the patterns mentioned earlier. I'm not sure if it's possible without using regular expressions. Robert |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Hi Robert,
Am Thu, 16 May 2019 02:40:10 -0700 (PDT) schrieb RG III: Hi Claus. Here's a different problem. How can I test if a string begins with 1) one or more characters, 2) is followed by one or more whitespace characters, 3) and followed by any character string mixed with anything, including whitespaces? Here are some example strings that meet the above criteria: "b908 g" "what g. no 99" "a b" Here are some example strings that do NOT meet the criteria: "b9000 " ' There are no chars after the whitespaces "abc" ' Missing whitespaces and chars at the end "ab" ' Missing whitespaces and chars at the end try it with this function: Function StrChk(myRng As Range) As Boolean Select Case Asc(Left(myRng, 1)) Case 97 To 122 Select Case Asc(Right(myRng, 1)) Case 48 To 122 If InStr(myRng, " ") 0 Then StrChk = True End Select End Select End Function If your string is in A1 call the function in the sheet with =StrChk(A1) If some strings don't work with that function, please poste the not working examples. Then we must do it with Regular Expression. Regards Claus B. -- Windows10 Office 2016 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
My input strings are in variables, not the spreadsheet, but I'll look into converting it for my needs and see how it works. Thanks again!
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
If InStr(myRng, " ") 0 Then StrChk = True
Hi Claus, Another way might be to write the above like this: StrChk = (InStr(myRng, " ") 0) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
My input strings are in variables, not the spreadsheet, but I'll look into
converting it for my needs and see how it works. Thanks again! You could try... Function StrChk(sText As String) As Boolean Select Case Asc(Left(sText, 1)) Case 97 To 122 Select Case Asc(Right(sText, 1)) Case 48 To 122: StrChk = (InStr(sText, " ") 0) End Select '//Asc(Right(sText, 1)) End Select '//Asc(Left(sText, 1)) End Function '//StrChk() -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Hi Claus. I found that your split procedure works fine when the input is in a string variable. But for some reason there's a bug when I read the input string from a file. Here's the code:
Sub FileReadTestForError() Dim sPath As String Dim sLine As String Dim sFile As Object Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") ' data file contains "a123 A. BC #123" sPath = "C:\data.txt" Set sFile = fso.OpenTextFile(sPath, 1) sLine = sFile.ReadLine ' The bug occurs here. sLine = Application.Substitute(sLine, " ", "*", 1) varTmp = Split(sLine, "*") str1 = varTmp(0) str2 = Trim(varTmp(1)) MsgBox str2 ' Is supposed to return "A. BC #123" ' But returns "BC #123" sFile.Close End Sub The code actually works if you replace that sFile.Readline code with the following: sLine = "a123 A. BC #123" So, that tells me the Readline function is producing a tainted string somehow. Do you know what's going on? Thanks! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
You could simply split the lines using the space as the delimiter; - skip the
empty element! Your sample text "a123 A. BC #123", for example: vTmp = Split(sLine, " ") returns the following... vTmp(0) = "a123" vTmp(1) = "" vTmp(2) = "A." vTmp(3) = "BC" vTmp(4) = "#123" ...so checking each line for content is the lines to process. HOWEVER: You should read the entire file into an array and process (dump) it into a blank worksheet, then parse the data however desired. Alternatively, you could process each line with the code I posted earlier and do whatever with the results. Here's how: Sub ParseFile() Dim sFile$, sTextIn$, vData, n&, rng 'Select the file sFile = Get_FileToOpen(): If sFile = "" Then Exit Sub 'Get file contents into an array sTextIn = ReadTextFile(sFile): vData = Split(sTextIn, vbCrLf) 'Dump the array into a blank worksheet ActiveWorkbook.Sheets.Add Set rng = ActiveSheet.Cells(1).Resize(UBound(vData) + 1) rng = Application.Transpose(vData) 'Process each cell's contents For n = 1 to rng.rows.count If StrChk(rng.Cells(n)) Then 'do something... Else 'do something else... End If Next 'n '-OR- Process each element of the array For n = LBound(vData) to UBound(vData) If StrChk(vData(n)) Then 'do something... Else 'do something else... End If Next 'n End Sub 'ParseFile Function ReadTextFile$(Filename$) ' Reads large amounts of data from a text file in one shot. Dim iNum% On Error GoTo ErrHandler iNum = FreeFile(): Open Filename For Input As #iNum ReadTextFile = Space$(LOF(iNum)) ReadTextFile = Input(LOF(iNum), iNum) ErrHandler: Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description End Function 'ReadTextFile() Function Get_FileToOpen$(Optional FileTypes$) Dim vFile If FileTypes = "" Then FileTypes = "All Files ""*.*"", *.*" vFile = Application.GetOpenFileName(FileTypes) Get_FileToOpen = IIf(vFile = False, "", vFile) End Function Function StrChk(sText$) As Boolean Select Case Asc(Left(sText, 1)) Case 97 To 122 Select Case Asc(Right(sText, 1)) Case 48 To 122: StrChk = (InStr(sText, " ") 0) End Select '//Asc(Right(sText, 1)) End Select '//Asc(Left(sText, 1)) End Function '//StrChk() -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Well, I'm adding functionality to an existing spreadsheet, and I prefer NOT to dump the file contents into an empty sheet. I wanted my code to be as compact, fast, and non-intrusive as possible.
The code that I posted earlier should have worked, and it fits my needs, but for some reason the OpenTextFile and ReadLine combination somehow taints the file string and prevent's Claus's solution from working. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Hi Robert,
Am Sun, 19 May 2019 11:06:28 -0700 (PDT) schrieb RG III: Well, I'm adding functionality to an existing spreadsheet, and I prefer NOT to dump the file contents into an empty sheet. I wanted my code to be as compact, fast, and non-intrusive as possible. The code that I posted earlier should have worked, and it fits my needs, but for some reason the OpenTextFile and ReadLine combination somehow taints the file string and prevent's Claus's solution from working. try: Function StrChk(myStr As String) As Boolean Select Case Asc(Left(myStr, 1)) Case 97 To 122 Select Case Asc(Right(myStr, 1)) Case 48 To 122 If InStr(myStr, " ") 0 Then StrChk = True End Select End Select End Function Sub ParseFile() Dim n As Integer, i As Integer, RowNmbr As Integer Dim varOut() As Variant Dim sText As String Const sPath = "G:\Data\data.txt" 'Select the file Open sPath For Input As #1 Do Until EOF(1) RowNmbr = RowNmbr + 1 Line Input #1, sText If StrChk(sText) = True Then ReDim Preserve varOut(i) varOut(i) = RowNmbr i = i + 1 End If Loop Close #1 MsgBox "The rows:" & Chr(10) & Join(varOut, Chr(10)) & _ Chr(10) & "of the text file are correct" End Sub The messagebox will show you the row numbers with correct data. Regards Claus B. -- Windows10 Office 2016 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Well, I'm adding functionality to an existing spreadsheet, and I prefer NOT
to dump the file contents into an empty sheet. I wanted my code to be as compact, fast, and non-intrusive as possible. So use the array solution instead (that's why I included it), and process a file as Claus suggests! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Hi Robert,
Am Sun, 19 May 2019 11:06:28 -0700 (PDT) schrieb RG III: Well, I'm adding functionality to an existing spreadsheet, and I prefer NOT to dump the file contents into an empty sheet. I wanted my code to be as compact, fast, and non-intrusive as possible. The code that I posted earlier should have worked, and it fits my needs, but for some reason the OpenTextFile and ReadLine combination somehow taints the file string and prevent's Claus's solution from working. try: Function StrChk(myStr As String) As Boolean Select Case Asc(Left(myStr, 1)) Case 97 To 122 Select Case Asc(Right(myStr, 1)) Case 48 To 122 If InStr(myStr, " ") 0 Then StrChk = True End Select End Select End Function Sub ParseFile() Dim n As Integer, i As Integer, RowNmbr As Integer Dim varOut() As Variant Dim sText As String Const sPath = "G:\Data\data.txt" 'Select the file Open sPath For Input As #1 Do Until EOF(1) RowNmbr = RowNmbr + 1 Line Input #1, sText If StrChk(sText) = True Then ReDim Preserve varOut(i) varOut(i) = RowNmbr i = i + 1 End If Loop Close #1 MsgBox "The rows:" & Chr(10) & Join(varOut, Chr(10)) & _ Chr(10) & "of the text file are correct" End Sub The messagebox will show you the row numbers with correct data. Regards Claus B. Claus, Loading the entire file into an array will be orders-of-magnitude faster! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky regular expression
Thank you Claus and Garry for your assistance. I'm still experimenting with both of your examples, but I'll let you know if I have any more questions.
Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another regular expression question | Excel Programming | |||
Can someone help me with this regular expression? | Excel Discussion (Misc queries) | |||
Regular Expression Conditionals (?(if)then|else) in VBA? | Excel Programming | |||
Help with regular expression | Excel Programming | |||
Regular Expression | Excel Discussion (Misc queries) |