تقسیم سلول ها توسط Case در اکسل

Question

مانیک دارای یک کاربرگ است که در ستون A دارای مقادیر متنی با فرمت "mikeDAVIS" است که نام افراد با حروف کوچک و نام خانوادگی با حروف بزرگ است. او می‌خواهد با توجه به مورد متن، نام‌ها را به دو ستون جداگانه تقسیم کند.

این را می توان با استفاده از فرمول یا ماکرو انجام داد. صرف نظر از اینکه از چه رویکردی استفاده می کنید، نکته کلیدی این است که بفهمید متن کجا از حروف کوچک به بزرگ تغییر می کند. این کار فقط با بررسی هر کاراکتر در رشته امکان پذیر است. بنابراین، اگر می خواهید از روش فرمولی استفاده کنید، باید از فرمول آرایه استفاده کنید. فرمول آرایه زیر نام خانوادگی هر چیزی که در سلول A1 است را برمی گرداند:

=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$255),1))>=65)
*(CODE(MID(A1,ROW($2:$255),1))

به یاد داشته باشید، از آنجایی که این یک فرمول آرایه است، باید با فشار دادن Ctrl+Shift+Enter آن را وارد کنید . همه چیز را در سلول باز می گرداند که با اولین حرف بزرگی که پیدا می کند شروع می شود. بنابراین، در "mikeDAVIS" "DAVIS" و در "mikeDavis" "دیویس" را برمی‌گرداند. با فرض اینکه از فرمول آرایه در سلول B1 استفاده می‌کنید، می‌توانید نام کوچک را با استفاده از موارد زیر تعیین کنید:

=SUBSTITUTE(A1,B1,"")

این یک فرمول معمولی است، نه یک فرمول آرایه ای.

فرمول‌های آرایه‌ای مشابه بسیاری وجود دارد که می‌توانند کار مشابهی را انجام دهند. به عنوان مثال، این فرمول آرایه نام کوچک (همه کاراکترها تا اولین کاراکتر بزرگ) هر چیزی که در سلول A1 وجود دارد را برمی گرداند:

=LEFT(A1,MAX((CODE(MID(A$1,ROW(INDIRECT("1:"&
LEN(A1))),1))>96)*ROW(INDIRECT("1:"&LEN(A1)))))

سپس می توانید از همان فرمول معمولی (فرمولی که از تابع SUBSTITUTE استفاده می کند) برای استخراج نام خانوادگی استفاده کنید.

اگر می‌خواهید از یک رویکرد کلان برای یافتن نام‌ها استفاده کنید، تنها کاری که باید انجام دهید این است که فرمولی پیدا کنید که مکان اولین حرف بزرگ را در متن بازگرداند. کد زیر این "نقطه تغییر" را در متن برمی گرداند:

Function GetFirstUpper(MyCell As Range) As Integer
    Dim sCellValue As String
    Dim i As Integer

    sCellValue = Trim(MyCell.Value)
    i = 1
    Do While (Asc(Mid(sCellValue, i, 1)) > 90 _
      Or Asc(Mid(sCellValue, i, 1)) < 65) _
      And i  Len(sCellValue) Then
        GetFirstUpper = 99
    Else
        GetFirstUpper = i
    End If
End Function

برای استفاده از تابع، فرض کنید نام در سلول A1 است. می توانید نام و نام خانوادگی را با استفاده از این فرمول ها در کاربرگ خود پیدا کنید:

=LEFT(A1,GetFirstUpper(A1)-1)
=MID(A1,GetFirstUpper(A1),LEN(TRIM(A1))-GetFirstUpper(A1)+1)

اگر ترجیح می‌دهید ماکرو شما نام‌های واقعی را برگرداند، می‌توانید از یکی از موارد زیر برای برگرداندن همه چیز قبل از اولین حرف بزرگ استفاده کنید:

Function GetFirstName(MyCell As Range) As String
    Dim sCellValue As String
    Dim i As Integer

    sCellValue = Trim(MyCell.Value)
    i = 1
    Do While (Asc(Mid(sCellValue, i, 1)) > 90 _
      Or Asc(Mid(sCellValue, i, 1)) < 65) _
      And i  Len(sCellValue) Then
        GetFirstName = sCellValue
    Else
        GetFirstName = Left(sCellValue, i - 1)
    End If
End Function

برای استفاده از ماکرو، تنها کاری که باید انجام دهید این است که از موارد زیر در یک سلول کاربرگ استفاده کنید. (این فرض می کند که رشته متنی که باید ارزیابی شود در سلول A1 است.)

=GetFirstName(A1)

یک تغییر جزئی در ماکرو به شما این امکان را می دهد که به طور مشابه نام خانوادگی را واکشی کنید، که فرض می شود همه چیز با اولین حرف بزرگ شروع می شود.

Function GetLastName(MyCell As Range) As String
    Dim sCellValue As String
    Dim i As Integer

    sCellValue = Trim(MyCell.Value)
    i = 1
    Do While (Asc(Mid(sCellValue, i, 1)) > 90 _
      Or Asc(Mid(sCellValue, i, 1)) < 65) _
      And i  Len(sCellValue) Then
        GetLastName = sCellValue
    Else
        GetLastName = Mid(sCellValue, i)
    End If
End Function

اگر ترجیح می دهید، می توانید ماکروها را در یک تابع واحد ترکیب کنید که بر اساس آنچه شما مشخص کرده اید، نام یا نام خانوادگی را برمی گرداند:

Function GetName(MyCell As Range, sWanted As String) As String
    Dim sCellValue As String
    Dim i As Integer

    sCellValue = Trim(MyCell.Value)
    i = 1
    Do While (Asc(Mid(sCellValue, i, 1)) > 90 _
      Or Asc(Mid(sCellValue, i, 1)) < 65) _
      And i  Len(sCellValue) Then
        GetName = sCellValue
    Else
        If LCase(sWanted) = "first" Then
            GetName = Left(sCellValue, i - 1)
        Else
            GetName = Mid(sCellValue, i)
        End If
    End If
End Function

برای استفاده از این تابع ترکیبی، کافیست نام مورد نظر خود را مشخص کنید:

=GetName(A1, "First")

کلمه "First" که به عنوان پارامتر به این روش ارسال می شود، نام کوچک (همه چیز قبل از اولین حرف بزرگ) را برمی گرداند. هر رشته دیگری که به عنوان پارامتر دوم ارسال شود (مانند "Last" یا "xxx" یا "Rest" یا حتی "") منجر به بازگشت نام خانوادگی می شود.