extracting data from one cell to another

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

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?
 
Try the below...This might go wrong if you have multiple instances of the
text "QN "

With text in cell A1

=IF(ISNUMBER(FIND("QN ",A1)),MID(A1,FIND("QN ",A1),14),"")
 
Try the below...This might go wrong if you have multiple instances of the
text "QN "

With text in cell A1

=IF(ISNUMBER(FIND("QN ",A1)),MID(A1,FIND("QN ",A1),14),"")

--
Jacob (MVP - Excel)





- Show quoted text -
Click to expand...

I do so that won't work
 
Try

=MID(A2,FIND("QN ",A2),14)

and copy down

--

HTH

Bob






- Show quoted text -
Click to expand...

this works. almost always. It would all the time but i am dealing with
morons.

Thanks
 
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?
Click to expand...

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
 
Back
Top