I have a sheet that i downloaded from our erp system that has one
column with text. the text is long and descriptive. the only thing is
the same is that there is one piece that starts with QN then a blank
then 11 numbers like this. QN 40000152489. What i want to do is pull
that from the text cell to another cell. Is that possible?
I'm not sure what your morons are doing, but the following User Defined
Function will return the first substring that meets your criteria. If the QN
could be followed by more than 11 digits, it will only return the first 11. If
there is no substring meeting the criteria in the cell, it returns a blank.
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=QN(A1)
in some cell. Or substitute a string or any cell reference for A1.
========================================
Option Explicit
Function QN(s As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "QN\s\d{11}"
re.ignorecase = False
If re.test(s) = True Then
Set mc = re.Execute(s)
QN = mc(0)
End If
End Function
===================================
--ron
