I need to extract specific numbers from a text string that varies.
For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'
Can anyone help please?
It's pretty simple to do using a UDF utilizing Regular Expressions.
To enter the UDF,
<alt-F11> opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.
To use this, enter the formula:
=RegexSub(A1,".*/(\d+).*","$1")
The relevant pattern is
".*/(\d+).*
which separates out the digit(s) following a "/" from everything else in the
string and captures into "reference 1"
The "$1" means to substitute only what was captured in reference 1 for the
entire string.
The string needs to be all on one single line. If it is on multiple lines, we
need to change the pattern.
============================
Option Explicit
Function RegexSub(Str As String, SrchFor As String, ReplWith As String) As
String
Dim objRegExp As Object
Set objRegExp = CreateObject("vbscript.regexp")
With objRegExp
.Pattern = SrchFor
.IgnoreCase = True
.Global = True
.MultiLine = True
End With
RegexSub = objRegExp.Replace(Str, ReplWith)
End Function
==================================
--ron
