RegEx Pattern

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
347
Office Version
  1. 2003 or older
Platform
  1. Windows
I want to modify an SI units extraction function that uses RegEx to include other units apart from m (METER).

How can I incorporate a case-sensitive list of units into the Pattern? The list is A, mol, g, cd, K, m, s, rad, sR, Hz, N, Pa, J, W, C, V, F, O, S, Wb, T, H, °C, lm, lx, Bq, Gy, Sv, kat.

I want to add the above list to incorporate the listed codes alongside < ?m > in the pattern with (\d+).?(\d*)\s*(?:da|[yzafpnµmcdhkMGTPEZY]) as common across the units.

The function:
VBA Code:
Public Function Find(x As String)

    Dim oRegex As Object

    If oRegex Is Nothing Then
        Set oRegex = CreateObject("VBScript.RegExp")
        oRegex.Global = True
        oRegex.Pattern = "(\d+).?(\d*)\s*(?:da|[yzafpnµmcdhkMGTPEZY])?m"
        ' A, mol, g, cd, K, m, s, rad, sR, Hz, N, Pa, J, W, C, V, F, O, S, Wb, T, H, °C, lm, lx, Bq, Gy, Sv, kat
    End If

    Find = Empty

    If oRegex.Test(x) Then Find = oRegex.Execute(x).Item(0).Value

End Function
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try...

VBA Code:
oRegex.Pattern = "(\d+).?(\d*)\s*(?:da|[yzafpnµmcdhkMGTPEZY])?(mol|rad|cd|sR|A|g|m| . . .)"

Note that the list needs to be specified based on the number of characters in each list item, from most to least.

Hope this helps!
 
Last edited:
Upvote 0
@Domenic; Thanks It works ... also how can I incorporate a float-value pattern of [-+]?[0-9]+[.]?[0-9]*([eE][-+]?[0-9]+) instead of current decimal pattern?
 
Upvote 0
Can you please confirm whether you want to search only for the floating value pattern or whether you want to search for both decimal and floating value pattern?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top