استخراج یک ایالت و یک کد پستی در اکسل

Question

Dan یک ستون از سلول ها دارد و هر سلول شامل سه مورد است: شهر، ایالت و کد پستی. (هر سه در یک سلول قرار دارند، دقیقاً مانند آنچه در یک خط آدرس می بینید.) برخی از کدهای پستی پنج رقمی و برخی 9 رقمی هستند. Dan باید هم حالت دو کاراکتری و هم کد پستی پنج رقمی را به سلول‌های خودش، در سمت راست داده‌های فعلی بکشد. دن می‌داند که می‌تواند از ابزار Text to Columns استفاده کند، اما احساس می‌کند که این ابزار کار زیادی را شامل می‌شود، زیرا او باید با نام‌های چند کلمه‌ای شهر و کاما سر و کار داشته باشد. فکر نمی کنم ممکن است یک رویکرد فرمولی وجود داشته باشد که راحت تر باشد.

برای ارائه هر گونه توصیه، باید چند فرض در مورد داده ها وجود داشته باشد. به عنوان مثال، فرض می کنیم که تمام داده ها در این فرمت هستند:

My Town, CA 98765-4321

بخش از خط تیره به بعد (قسمت انتهایی کد پستی) اختیاری است، اما موقعیت کاما ثابت است - همیشه نام شهر را دنبال می کند - و حالت همیشه از دو کاراکتر تشکیل شده است. در این مورد به راحتی می توان دو فرمول ابداع کرد که مخفف حالت و پنج رقم اول کد پستی را استخراج می کند:

=MID(A1,FIND(",",A1)+2,2)
=MID(A1,FIND(",",A1)+5,5)

هر دو فرمول روی کاما کلید می زنند. به عنوان یک مرز بین شهر و دو مورد واقعاً مورد نظر عمل می کند. اگر در داده ها کاما وجود نداشته باشد یا چندین کاما وجود داشته باشد، فرمول ها اطلاعات مورد نظر را بر نمی گردند.

فرمول ها همچنین فرض می کنند که هیچ فضای اضافی در داده های شما وجود ندارد. حداکثر یک فاصله بعد از کاما و بین حالت و کد پستی وجود دارد. البته اجرای این کار به اندازه کافی آسان است - فقط کافی است از Find and Replace برای جایگزینی دو فضا با یک فضای واحد در هر نقطه از کاربرگ خود استفاده کنید.

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

اگر داده‌های شما آنچنان ساختارمند نیستند - شاید دارای کاماهای متعدد در آدرس یا فضاهای اضافی باشد - در این صورت رویکرد کاملا متفاوتی در نظر گرفته می‌شود. برای مقابله با این، تکنیک اساسی شامل برش داده ها برای حذف فضاهای اضافی (پیشرو، دنباله دار، و داخلی)، سپس تعیین مکان آخرین فضا و فضای دوم به آخر است.

با استفاده از این فرمول می‌توانید پنج رقم کد پستی را که بلافاصله پس از آخرین فاصله در داده‌ها قرار می‌گیرد بیرون بکشید:

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",
CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)

مخفف حالت دو کاراکتری را می توان با بیرون کشیدن دو کاراکتر بلافاصله بعد از فاصله دوم به آخر بازگرداند:

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),
LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,2)

اگر داده‌های شما حتی ساختار کمتری دارند - شاید شامل آدرس‌هایی باشد که همگی دارای اختصارات دو نویسه نیستند (NJ به جای NJ) - بهتر است از یک ماکرو برای تقسیم کردن داده‌ها استفاده کنید. دلیل این امر این است که VBA مجموعه بسیار غنی‌تری از توابع مدیریت متن نسبت به آنچه می‌توانید با استفاده از فرمول‌های اکسل انجام دهید، دارد. ماکرو زیر یک تابع تعریف شده توسط کاربر ایجاد می کند که می تواند حالت یا کد ZIP را برگرداند:

Function GetStateZIP(rstrAddress As String, iAction As Integer) As String
    Dim arr As Variant
    Dim sState As String
    Dim sZIP As String

    Application.Volatile
    rstrAddress = Trim(rstrAddress)
    If Len(rstrAddress) = 0 Then Exit Function
    arr = Split(rstrAddress, " ")
    With arr
        If UBound(arr) < 2 Then
            sState = "?"
            sZIP = "?"
        Else
            sState = arr(UBound(arr) - 1)
            sZIP = arr(UBound(arr))
        End If
    End With
    If iAction = 1 Then
        GetStateZIP = sState
    End If
    If iAction = 2 Then
        GetStateZIP = sZIP
    End If
End Function

برای استفاده از این تابع، به سادگی یک مرجع سلول و 1 (اگر وضعیت را می خواهید) یا 2 (اگر کد پستی می خواهید) ارائه دهید. در اینجا نمونه ای از درخواست کد پستی برای هر آدرسی که در سلول A1 وجود دارد آورده شده است:

=GetStateZIP(A1,2)