Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default Tricky regular expression

Holy sheet! That works better than regular expression! 8)

You guys are the best. Thanks again!
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 65
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Another regular expression question Robert Crandal[_2_] Excel Programming 5 February 2nd 13 12:44 PM
Can someone help me with this regular expression? [email protected] Excel Discussion (Misc queries) 3 March 10th 09 08:36 PM
Regular Expression Conditionals (?(if)then|else) in VBA? Lazzaroni Excel Programming 7 October 8th 08 09:53 PM
Help with regular expression PO Excel Programming 3 May 2nd 07 01:39 PM
Regular Expression sl Excel Discussion (Misc queries) 2 January 24th 07 12:57 AM


All times are GMT +1. The time now is 12:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"