12 Digit value.....

S

smartguy

Well-known Member
Joined
Jul 14, 2009
Messages
778
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
 
Sort by date Sort by votes
Code: 
Option Base 0
Option Explicit
Sub kpark28June12_2()
    Dim i As Long, LR As Long, strArray() As String, j As Long, k As Long
    Dim check As Boolean, checkNum As Boolean, checkAlpha As Boolean
    LR = Range("D" & Rows.count).End(xlUp).Row
    
    For i = 2 To LR
        strArray = Split(Range("D" & i).Value, " ")
        For j = LBound(strArray) To UBound(strArray)
            check = True
            checkNum = False
            checkAlpha = False
            If Len(strArray(j)) = 12 Then
                For k = 1 To 12
                    If checkNum = False Or checkAlpha = False Then
                        If IsNumber(Mid(strArray(j), k, 1)) Then
                            checkNum = True
                        ElseIf IsAlphabet(Mid(strArray(j), k, 1)) Then
                            checkAlpha = True
                        End If
                    End If
                    check = check And (IsNumber(Mid(strArray(j), k, 1)) Or IsAlphabet(Mid(strArray(j), k, 1)))
                Next k
                If check And checkNum And checkAlpha Then
                    Range("E" & 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

I assumed that the the values are always delimited by space (" ") and it only takes the last number that satisfied the conditions in column D and put the results in column E.
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Code: 
Option Base 0
Option Explicit
Sub kpark28June12_2()
    Dim i As Long, LR As Long, strArray() As String, j As Long, k As Long
    Dim check As Boolean, checkNum As Boolean, checkAlpha As Boolean
    LR = Range("D" & Rows.count).End(xlUp).Row
    
    For i = 2 To LR
        strArray = Split(Range("D" & i).Value, " ")
        For j = LBound(strArray) To UBound(strArray)
            check = True
            checkNum = False
            checkAlpha = False
            If Len(strArray(j)) = 12 Then
                For k = 1 To 12
                    If checkNum = False Or checkAlpha = False Then
                        If IsNumber(Mid(strArray(j), k, 1)) Then
                            checkNum = True
                        ElseIf IsAlphabet(Mid(strArray(j), k, 1)) Then
                            checkAlpha = True
                        End If
                    End If
                    check = check And (IsNumber(Mid(strArray(j), k, 1)) Or IsAlphabet(Mid(strArray(j), k, 1)))
                Next k
                If check And checkNum And checkAlpha Then
                    Range("E" & 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

I assumed that the the values are always delimited by space (" ") and it only takes the last number that satisfied the conditions in column D and put the results in column E.
Lastly, I have assumed that the values you want to extract must have BOTH alphabets and numbers.
 
Upvote 0
smartguy said:
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.
Click to expand...
If you had installed the function that I referenced in my earlier response, then the answer to your current question would have been this formula...

=GetPattern(D2,REPT("[A-Za-z0-9]",12))
 
Upvote 0
smartguy said:
Hello sir,

I am looking for vba code....
Click to expand...
Then call my GetPattern function directly from within your VB code (a UDF is just a normal VB function that is structured in such a way that is can also be called from within a worksheet formula... the only change you need to make is to pass an explicit Range reference in for the direct cell reference used in a formula and change the REPT function call to something VB understands (there are two ways to do that). So, whereas you would use...

=GetPattern(D2,REPT("[A-Za-z0-9]",12))

in a worksheet formula, you would use it either of these ways inside VB code...

Code: 
TwelveDigitAlphaNumber = GetPattern(Range("D2").Value, Replace(String(12, "@"), "@", "[A-Za-z0-9]"))
or you could just repeat the "[A-Za-z0-9]" twelve times directly...

Code: 
TwelveDigitAlphaNumber = GetPattern(Range("D2").Value, "[A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9]")
Remember, though, you have to copy the function I posted in my mini-blog article into a standard Module (same place you would put it for use as a UDF) in order to use it within your own VB code.

Also, so the first formula I posted (where you wanted a two-digit number followed by a letter followed by nine more digits) would look like this if called from within your VB code...

Code: 
Number2DigitsLetter9Digits =GetPattern(Range("A2").Value, "##[A-Za-z]#########")
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
370
kevin9999
K
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
401
felixstraube
F
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
309
Peter_SSs
Peter_SSs
A
  • Question
GET MAX TIME VALUES BETWEEN TWO RANGES IN EXCEL LIST
2
Replies
19
Views
619
AFZAL SOHAIL
A

Forum statistics

Threads
1,214,918
Messages
6,122,249
Members
449,075
Latest member
staticfluids

Share this page

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