بریک شماره قطعه با طول متغیر

Question

مارتی یک کاربرگ با یک سری طولانی از اعداد در ستون A دارد. اینها شامل حروف و اعداد مانند A123BC، AB123C و غیره است. مارتی می خواهد داده ها را به سه ستون تقسیم کند، به طوری که متن قبل از اعداد در یک ستون باشد. ستون، اعداد در ستون دوم و متن بعد از اعداد در سوم.

عاملی که تقسیم شماره قطعه به بخش ها را پیچیده می کند این است که طول مشخصی برای هر جزء از شماره قطعه ترکیبی وجود ندارد. اگر طول مؤلفه ها استاندارد بود، می توانید از تابع Text to Columns در اکسل استفاده کنید. از آنجایی که آنها وجود ندارند و هیچ مرزبندی بین اجزا وجود ندارد، پس این راه بالقوه برای یک راه حل بسته است.

اگر می خواهید از فرمول هایی برای جدا کردن شماره های قطعه استفاده کنید، به سه عدد از آنها نیاز دارید، یکی برای هر جزء که می خواهید استخراج کنید. با فرض اینکه شماره قسمت ها از الگوی مشخص شده (متن، ارقام، متن) پیروی می کنند و شماره قسمت اول در سلول A1 است، می توانید از موارد زیر در سلول B1 استفاده کنید:

=LEFT(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0)-1)

این باید به عنوان یک فرمول آرایه وارد شود، به این معنی که باید آن را با استفاده از Ctrl+Shift+Enter وارد کنید . فرمول اولین رقم عددی را در شماره قطعه پیدا می کند و سپس همه چیز قبل از آن رقم را برمی گرداند. روی هر قسمتی که طول آن بیش از 100 کاراکتر نباشد کار می کند.

برای استخراج جزء دوم قطعه نامبر می توانید فرمول زیر را در سلول C1 قرار دهید:

=MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),
1)),0),COUNT(1*MID(A1,ROW(INDIRECT("1:100")),1)))

باز هم، این یک فرمول واحد است و باید به عنوان یک فرمول آرایه ( Ctrl+Shift+Enter ) وارد شود تا بتواند روی هر کاراکتر در شماره قطعه اصلی کار کند. شماره قطعه را بررسی می کند و نقطه شروع ارقام را تعیین می کند و سپس تمام آن ارقام را استخراج می کند. یک رشته متنی را برمی گرداند، حتی اگر آن رشته از ارقام تشکیل شده باشد. اگر می خواهید در واقع به عنوان یک عدد در نظر گرفته شود (البته که از شر صفرهای ابتدایی خلاص می شود)، باید کل فرمول را در یک تابع Value محصور کنید، همانطور که در اینجا نشان داده شده است:

=VALUE(MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),
1)),0),COUNT(1*MID(A1,ROW(INDIRECT("1:100")),1))))

برای به دست آوردن آخرین مولفه شماره قطعه، باید از فرمول زیر استفاده کنید که دوباره به عنوان فرمول آرایه وارد شده است:

=MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),
1)),0)+COUNT(1*MID(A1,ROW(INDIRECT("1:100")),1)),100)

در حالی که این روش بسیار خوب کار می کند، فرمول های آرایه به طور مشخصی محاسباتی فشرده هستند، به خصوص زمانی که تعداد زیادی از فرمول ها را در کاربرگ خود دارید. اگر شما نیاز به جداسازی هزاران شماره قسمت دارید، به این معنی است که در نهایت با 3000 فرمول آرایه مواجه می شوید که می تواند در محاسبه مجدد بسیار بسیار کند باشد.

اگر در این موقعیت قرار گرفتید، ممکن است بخواهید از یک ماکرو برای جدا کردن شماره های قطعه استفاده کنید. ماکرو زیر باید روی شماره های قسمتی کار کند که از الگوی متن، اعداد، متن پیروی می کنند، همانطور که قبلاً توضیح داده شد.

Sub Split1()
    Dim C As Range
    Dim sNew As New
    Dim i As Integer
    
    For Each C In Selection
        sNew = ""
        i = 1

         Get first part, which is text
        Do While IsNumeric(Mid(C, i, 1)) = False
            sNew = sNew & Mid(C, i, 1)

            i = i + 1
            If i > Len(C) Then Exit Do
        Loop
        C.Offset(0, 1).Value = sNew

         Pull next part, which should be digits
        sNew = ""
        Do While IsNumeric(Mid(C, i, 1)) = True
            sNew = sNew & Mid(C, i, 1)

            i = i + 1
            If i > Len(C) Then Exit Do
        Loop
        C.Offset(0, 2).Value = sNew

         Use rest of original cell
        sNew = Mid(C, i, Len(C))
        C.Offset(0, 3).Value = sNew
    Next C
End Sub

برای استفاده از ماکرو، کافی است تعدادی از شماره های قطعه را انتخاب کرده و آن را اجرا کنید. ماکرو از مفهوم جستجوی تغییرات بین مقادیر عددی/غیر عددی در رشته ای از کاراکترها در سلول استفاده می کند. هنگامی که به یکی از این مرزها رسید، بخشی از رشته اصلی قبل از مرز در یک سلول جدید قرار می گیرد. این مفهوم را می توان کمی کوتاه کرد، همانطور که در مثال زیر انجام شده است.

Sub Split2()
    Dim C As Range
    Dim j As Integer
    Dim k As Integer

    For Each C In Selection
        j = 1
        Do While Not (IsNumeric(Mid(C.Value, j, 1))) And j 

The difference between this version of the macro and the previous one, of course, is that this version steps through the original cell and determines the boundaries all at once. When they are known, then the components of the original part number are stuffed into the cells.

An interesting approach to pulling apart the part numbers is to use a couple of short user-defined functions that determine where the boundaries are between the components. Consider the following two functions:

Function pNumber(X)
    i = 1
    Do Until Mid(X, i, 1) Like "#": i = i + 1: Loop
    pNumber = i
End Function

Function pAlpha(X)
    X = UCase(X)
    i = pNumber(X)
    Do Until Mid(X, i, 1) Like "[A-Z]": i = i + 1: Loop
    pAlpha = i
End Function

These are much shorter than the previous macros, and all they do is return the boundary where the numbers start (in the case of pNumber) and the boundary where the second group of text starts (in the case of pAlpha). To use the functions, you use the following three formulas to return, respectively, the first, second, and third components of the original part number:

=MID(A1,1,pNumber(A1)-1)
=MID(A1,pNumber(A1),pAlpha(A1)-pNumber(A1))
=MID(A1,pAlpha(A1),LEN(A1)-pAlpha(A1)+1)