vba regular expression - left and mid value

[Mallesh23]

Hi Team,

Please suggest Regular expression Pattern Just for learning purpose

after split I can get it. its just for learning purpose. Can someone assist thanks.



1) Find Company_Name always comes left side of Company. --------- expected AAABB
AAABB Company


2)1) Find Currency and Product. expected USD and LAPTOP

xxx LAPTOP USD 'Situation 1 -------------->
LAPTOP USD 'Situation2


Thanks
mg

 

[Zot]

Try these:

1) \b(?!Company\b)\w+

2) \b(LAPTOP\s)(\w+)

 

[Mallesh23]

Hi Zot,

Thanks for your help, how to Read this pattern.

\b(?!Company\b)\w+


Thanks
mg

 

[Zot]

Hi Zot,

Thanks for your help, how to Read this pattern.

\b(?!Company\b)\w+


Thanks
mg

Not sure what you meant to ask. This is looking for word Company and if found discard it and take the word before it. For string
AAABB Company

The execution will result in AAABBB

Is this what you meant? If you provide sheet sample using XL2BB then perhaps many helpers here can provide sample code if I'm no replying fast enough ?

 

[Zot]

Okay. Try this. I put string below in Range("A1")
AAABB Company

The code to run

Sub Test()

Dim strTest As String
Dim ws As Worksheet
Dim strResult As Object, RegEx As Object

Set RegEx = CreateObject("VBScript.RegExp")
Set ws = ActiveWorkbook.Sheets("Sheet1")

strTest = ws.Range("A1")

With RegEx
    .Pattern = "\b(?!Company\b)\w+"
    .Global = True
    Set strResult = .Execute(strTest)
End With

Debug.Print strResult(0)

End Sub

 

[Mallesh23]

Hi Zot,

Thanks for your help it worked. ? ?


Thanks
mg

 

[Peter_SSs]

What if the company name contains more than one word?
More sample data & expected results would be useful.

 

[Peter_SSs]

Thanks for your help it worked.

Are you sure? With "abc def" in the cell the code returned "abc" for me

 

[Mallesh23]

Hi Peter,

I dint noticed,, Yes its picking any left value.

Can we limit Just left of Company. thanks for help. just learning.




Thanks
mg

 

[Peter_SSs]

Can we limit Just left of Company. thanks for help. just learning.

As I said before:

What if the company name contains more than one word?
More sample data & expected results would be useful.