12 Digit value.....

smartguy

Well-known Member
Joined
Jul 14, 2009
Messages
775
Hello all,

Good day.

I have excel file in the below format.

Sheet1

*B
1Data
22 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
3Customer 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
BC
1DataResult
22 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-2981W284696001
3Customer 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 CodeEID91W284696001
Sheet1
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
UDF?

Code:
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
 
Last edited:

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Oh wow, shg.
That is a much cleaner code!

Since I wrote it anyways (a quick code I made)
Code:
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! :(
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

If you prefer not to set a reference,

Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,919
Similar to shg's, but accesible to Mac users.
The formula would be =MatchingWord(A2, "##[A-Za-z]#########")

Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Mac doesn't support regular expressions, Mike?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,919
Mac doesn't support regular expressions, Mike?
Not through VBA.
This gives a Error 429, "ActiveX component can't create object"
Code:
Dim oRE As Object
   
Set oRE = CreateObject("VBScript.RegExp")
 
Last edited:

smartguy

Well-known Member
Joined
Jul 14, 2009
Messages
775
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 ....
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

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
Top