Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 14th 19, 10:57 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 19
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  
Old May 14th 19, 11:12 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,692
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  
Old May 14th 19, 11:30 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 19
Default Tricky regular expression

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

You guys are the best. Thanks again!
  #4   Report Post  
Old May 15th 19, 12:58 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,692
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  
Old May 16th 19, 10:40 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 19
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  
Old May 16th 19, 12:31 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,692
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  
Old May 16th 19, 09:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 19
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  
Old May 17th 19, 12:11 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,027
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  
Old May 17th 19, 12:25 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,027
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  
Old May 18th 19, 10:29 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 19
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!


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 11:44 AM
Can someone help me with this regular expression? [email protected] Excel Discussion (Misc queries) 3 March 10th 09 07: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 23rd 07 11:57 PM


All times are GMT +1. The time now is 11:29 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017