12 Digit value.....

[smartguy]

Hello all,

Good day.

I have excel file in the below format.

Sheet1

*	B
1	Data
2	2 2011-08-31 *FR00 POD available 2011-09-06 2011-08-31 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-06 2011-09-06 *Delivered *7269785 G100 ECF5142685 Cust. Shipment No. 2011-09-01 *2011-08-31 2011-09-01 *FR00 POD available 2011-09-02 2011-08-29 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-01 2011-09-01 *Delivered *7268455 G100 EIH5958228 Cust. Shipment No. 2011-08-31 *2011-08-29 2011-08-31 *FR00 POD available 2011-09-05 2011-08-30 * 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-05 2011-09-05 *Delivered *7269434 G100 ECF5139702 Cust. Shipment No. 2011-08-31 *2011-08-31 2011-08-31 *FR00 POD available 2011-09-06 2011-08-31 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-02 2011-09-02 *Delivered *7269216 G100 ECF5136427 Cust. Shipment No. 2011-09-01 *2011-08-29 2011-09-01 *FR00 POD available 2011-09-05 2011-08-30 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-06 2011-09-06 *Delivered *7264790 G100 ESE1288114 Cust. Shipment No. 2011-08-30 *2011-08-26 2011-08-30 *FR00 POD available 2011-09-05 2011-08-30 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-02 2011-09-02 *Delivered *7268455 G100 EIC0487477 Cust. Shipment No. 2011-08-29 *2011-08-29 2011-08-29 *FR00 POD available 2011-09-02 2011-08-29
3	Customer Name:Order Status: AllSubmittedAdminAdmin/ProcessingAdmin/AckedProductionProductionDoneShipped from FactoryRegisteredShipped to CustomerDeliveredInvoicedOpen OrdersClosed OrdersCancelednot Shipped from Factorynot Shipped to Customernot Deliverednot Invoiced--- Ship/Invoice Status --Order fully ShippedOrder partial ShippedOrder not ShippedOrder fully InvoicedOrder partial InvoicedOrder not Invoiced--- Header Status ---Admin (header)Production (header)Consolidation (header)Shipped (header)Delivered (header) * SAP Order #:Customer #: Order Type: AllTradeLeaseDebitSRQTrade & LeaseTrade & Lease & SRQInternalDemoResaleReturnCreditStatisticalContractCare PackNo Charge Order * Purchase Order #:Sales Admin:Order Origin: Search by Numbers: *Search by Codes: * Asset Tag NoOIS ID NoCust Family NoCust Ship NoCustomer NoeClaim NoFact Ship NoInvoice NoMac AddressNCRF numberPurch AgreementQuote NoSAL NoSerial NoIMEI (Telco)Web Order NoGvt Contract NoWAWF NumberAccount NameLegacy Order TypeMaster Contract IDProduct NoCust. Product No * CD CodeDeal/Price IDDC CodeEIDId CodeInvoice to IDOPG codeProduct LinesSales OrgSales Rep.Siebel Agent IDShip to IDShip to ZipSold to IDSpecial CodesSupplierTeam IDCampaign CodeFunnel IDCatalog IDDistrib ChannelCommerciality CodePayment MethodCommunity IDTeleweb Agent ID * Asset Tag NoOIS ID NoCust Family NoCust Ship NoCustomer NoeClaim NoFact Ship NoInvoice NoMac AddressNCRF numberPurch AgreementQuote NoSAL NoSerial NoIMEI (Telco)Web Order NoGvt Contract NoWAWF NumberAccount NameLegacy Order TypeMaster *91W284696001 *Orderererer rerere rererer sa sasas Contract IDProduct NoCust. Product No * CD CodeDeal/Price IDDC CodeEID

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:618px;"></colgroup>

I need 1st 12 Digit value ...

12 Digit Format :

1. 1st 2 Digit Number
2. 1 Alpapets
3. 9 Digit Number

I am looking for VBA Code...

Please Help

Answer :

Excel Workbook
	B	C
1	Data	Result
2	2 2011-08-31 *FR00 POD available 2011-09-06 2011-08-31 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-06 2011-09-06 *Delivered *7269785 G100 ECF5142685 Cust. Shipment No. 2011-09-01 *2011-08-31 2011-09-01 *FR00 POD available 2011-09-02 2011-08-29 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-01 2011-09-01 *Delivered *7268455 G100 EIH5958228 Cust. Shipment No. 2011-08-31 *2011-08-29 2011-08-31 *FR00 POD available 2011-09-05 2011-08-30 *21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-05 2011-09-05 *Delivered *7269434 G100 ECF5139702 Cust. Shipment No. 2011-08-31 *2011-08-31 2011-08-31 *FR00 POD available 2011-09-06 2011-08-31 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-02 2011-09-02 *Delivered *7269216 G100 ECF5136427 Cust. Shipment No. 2011-09-01 *2011-08-29 2011-09-01 *FR00 POD available 2011-09-05 2011-08-30 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-06 2011-09-06 *Delivered *7264790 G100 ESE1288114 Cust. Shipment No. 2011-08-30 *2011-08-26 2011-08-30 *FR00 POD available 2011-09-05 2011-08-30 7209505926 21X5979S INGRAM MICRO SAS 81W284696001 * *B2Bi Trade Order 00 00 2011-08-25 2011-08-25 2011-09-02 2011-09-02 *Delivered *7268455 G100 EIC0487477 Cust. Shipment No. 2011-08-29 *2011-08-29 2011-08-29 *FR00 POD available 2011-09-02 2011-08-29	81W284696001
3	Customer Name:Order Status: AllSubmittedAdminAdmin/ProcessingAdmin/AckedProductionProductionDoneShipped from FactoryRegisteredShipped to CustomerDeliveredInvoicedOpen OrdersClosed OrdersCancelednot Shipped from Factorynot Shipped to Customernot Deliverednot Invoiced--- Ship/Invoice Status --Order fully ShippedOrder partial ShippedOrder not ShippedOrder fully InvoicedOrder partial InvoicedOrder not Invoiced--- Header Status ---Admin (header)Production (header)Consolidation (header)Shipped (header)Delivered (header) *SAP Order #:Customer #: Order Type: AllTradeLeaseDebitSRQTrade & LeaseTrade & Lease & SRQInternalDemoResaleReturnCreditStatisticalContractCare PackNo Charge Order *Purchase Order #:Sales Admin:Order Origin: Search by Numbers: *Search by Codes: * Asset Tag NoOIS ID NoCust Family NoCust Ship NoCustomer NoeClaim NoFact Ship NoInvoice NoMac AddressNCRF numberPurch AgreementQuote NoSAL NoSerial NoIMEI (Telco)Web Order NoGvt Contract NoWAWF NumberAccount NameLegacy Order TypeMaster Contract IDProduct NoCust. Product No * CD CodeDeal/Price IDDC CodeEIDId CodeInvoice to IDOPG codeProduct LinesSales OrgSales Rep.Siebel Agent IDShip to IDShip to ZipSold to IDSpecial CodesSupplierTeam IDCampaign CodeFunnel IDCatalog IDDistrib ChannelCommerciality CodePayment MethodCommunity IDTeleweb Agent ID * Asset Tag NoOIS ID NoCust Family NoCust Ship NoCustomer NoeClaim NoFact Ship NoInvoice NoMac AddressNCRF numberPurch AgreementQuote NoSAL NoSerial NoIMEI (Telco)Web Order NoGvt Contract NoWAWF NumberAccount NameLegacy Order TypeMaster *91W284696001 *Orderererer rerere rererer sa sasas Contract IDProduct NoCust. Product No * CD CodeDeal/Price IDDC CodeEID	91W284696001
Sheet1

 

[shg]

UDF?

Function RegEx(sInp As String, sPatt As String) As String
    Static oRE As RegExp
    
    If oRE Is Nothing Then Set oRE = New RegExp
    
    With oRE
        .Pattern = sPatt
        If .Test(sInp) Then RegEx = .Execute(sInp)(0)
    End With
End Function

E.g., =RegEx(A1, "\d\d[A-Z]\d{9}")

EDIT: This needs a reference -- in the VBE, Tools > References, tick Microsoft VBScript Regular Expressions

 

[kpark91]

Oh wow, shg.
That is a much cleaner code!

Since I wrote it anyways (a quick code I made)

Sub kpark28June12_2()
    Dim i As Long, LR As Long, strArray() As String, j As Long, k As Long
    Dim check As Boolean
    LR = Range("B" & Rows.count).End(xlUp).Row
    
    For i = 2 To LR
        strArray = Split(Range("B" & i).Value, " ")
        For j = LBound(strArray) To UBound(strArray)
            check = True
            If Len(strArray(j)) = 12 Then
                For k = 1 To 12
                    If k <> 3 Then
                        check = check And IsNumber(Mid(strArray(j), k, 1))
                    Else
                        check = check And IsAlphabet(Mid(strArray(j), k, 1))
                    End If
                Next k
                If check Then
                    Range("c" & i).Value = strArray(j)
                End If
            End If
        Next j
    Next i
End Sub

Function IsNumber(s As String)
    If Asc(s) >= 48 And Asc(s) <= 57 Then
        IsNumber = True
        Exit Function
    End If
    IsNumber = False
End Function

Function IsAlphabet(s As String)
    If (Asc(s) >= 65 And Asc(s) <= 90) Or (Asc(s) >= 97 And Asc(s) <= 122) Then
        IsAlphabet = True
        Exit Function
    End If
    IsAlphabet = False
End Function

The only advantage is that you don't need to include the reference shg has mentioned lolll.
Other than that, it's probably gonna be slower, more memory intensive, more resource intensive haha.
and wrong if the word you want to get is not delimited by space!

 

[Rick Rothstein]

I posted a function that will allow you to retrieve a value that matches a given pattern from inside a larger piece of text in my mini-blog article here...

Find a text substring that matches a given "pattern"

Once you install the UDF (user defined function) from that article, you would use this formula in your worksheet...

=GetPattern(A2,"##[A-Za-z]#########")

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetPattern just like it was a built-in Excel function (see example formula shown above).

 

[shg]

If you prefer not to set a reference,

Function RegEx(sInp As String, sPatt As String) As String
    Static oRE As Object
    
    If oRE Is Nothing Then Set oRE = CreateObject("VBScript.RegExp")
    
    With oRE
        .Pattern = sPatt
        If .Test(sInp) Then RegEx = .Execute(sInp)(0)
    End With
End Function

 

[mikerickson]

Similar to shg's, but accesible to Mac users.
The formula would be =MatchingWord(A2, "##[A-Za-z]#########")

Function MatchingWord(ByVal TestString As String, ByVal Pattern As String, Optional ByVal Instance As Long = 1) As String
    Dim oneWord As Variant
    
    TestString = Replace(Replace(TestString, vbCr, " "), vbLf, " ")
    
    For Each oneWord In Split(TestString, " ")
        Instance = Instance + CLng(oneWord Like Pattern)
        If Instance < 1 Then
            MatchingWord = oneWord
            Exit Function
        End If
    Next oneWord
End Function

 

[shg]

Mac doesn't support regular expressions, Mike?

 

[Rick Rothstein]

Mac doesn't support regular expressions, Mike?

I don't have a Mac, so I don't know the answer to this, but the documentation for CreateObject says it creates and returns a reference to an ActiveX object... do Macs support ActiveX COM objects? I don't know for sure, so I could be wrong, but I always thought they were Windows "things" only.

 

[mikerickson]

Mac doesn't support regular expressions, Mike?

Not through VBA.
This gives a Error 429, "ActiveX component can't create object"

Dim oRE As Object
   
Set oRE = CreateObject("VBScript.RegExp")

 

[smartguy]

Hi all,

Thanks to all.

Special thanks to kpark91


Hi kpark91 ,

Macro working fine.

I Need one More help.

If Need 12 Digit data Column D.Please Ignore the Old Format.

the only condition is 12 value comes like alpha & Number Compenation.

Please help ....