انتقال شماره خانه به سلول خودش

Question

تام با لیست های زیادی از آدرس ها کار می کند. یکی از چیزهایی که او را دیوانه می کند این است که آدرس خیابان در یک سلول است. تام به شماره خانه در یک سلول و خیابان در یک سلول دیگر نیاز دارد. بنابراین، اگر آدرس «1234 Maple Glen Ave.» باشد، پس او به «1234» در یک سلول و «Maple Glen Ave» نیاز دارد. در دیگری. او می تواند شماره خانه را در ستونی که قبل از نام خیابان قرار داده شده کپی کند، اما تایپ مجدد اعداد و حذف آنها از نام خیابان یک فرآیند طولانی است. تام فکر می کند که آیا راه ساده تری وجود دارد که شماره خانه را از نام خیابان جدا کند.

چند راه برای انجام این کار وجود دارد. برخی ممکن است فکر کنند که استفاده از ابزار Text to Columns (در زبانه Data روبان) نتایج دلخواه را به همراه خواهد داشت. متأسفانه، این بهترین راه برای جدا کردن شماره خانه نیست. دلیل آن ساده است: اگر از ابزار با تعیین جداکننده استفاده می کنید، تنها چیزی که منطقی به نظر می رسد فضا است. اما اگر این کار را انجام دهید، در نهایت با "1234 Maple Glen Ave" مواجه می شوید. به جای دو ستون مورد نظر در چهار ستون پخش می شود. سپس باید راهی برای چسباندن نام خیابان در کنار هم بیابید.

راه دیگر برای استفاده بالقوه از ابزار Text to Columns با عرض ستون ثابت (به جای جداکننده) است. این رویکرد تنها در صورتی کار خواهد کرد که همه آدرس های شما دارای تعداد ارقام یکسانی در شماره خانه باشند. البته در بیشتر مجموعه های داده این طور نیست - شماره خانه ها می توانند تقریباً هر رقمی باشند، و حتی ممکن است شامل نویسه های غیر عددی (مانند "1234A"، "1234-B"، یا "1234 باشند". -36").

یکی از روش هایی که جواب می دهد استفاده از فرمول هایی برای جدا کردن آدرس های اصلی است. فرمول های شما می توانند اولین فضای آدرس را وارد کنند و یا قسمت سمت چپ فاصله یا قسمت سمت راست را برگردانند. در اینجا روشی برای بیرون کشیدن همه چیز قبل از فضا وجود دارد، با توجه به آدرسی در سلول A1:

=LEFT(A1,FIND(" ",A1)-1)

اگر کاملاً مطمئن هستید که آدرس ها حاوی هیچ کاراکتر غیر عددی نیستند، می توانید فرمول را در تابع VALUE بپیچید تا در نهایت شماره خانه را به عنوان یک مقدار عددی دریافت کنید:

=VALUE(LEFT(A1,FIND(" ",A1)-1))

برای گرفتن بخشی از آدرس زیر فضای اول می توانید از این فرمول استفاده کنید:

=MID(A1,FIND(" ",A1)+1,LEN(A1))

اگر ترجیح می دهید، می توانید فرمولی ابداع کنید که به شماره خانه ای که با فرمول اول بیرون آورده اید ارجاع دهد. برای مثال فرض کنید فرمول شماره خانه شما در ستون B باشد. می توانید موارد زیر را در ستون C قرار دهید:

=TRIM(SUBSTITUTE(A1,B1,))

با فرمول های خود در ستون های B و C (و آدرس های ستون A)، می توانید فرمول ها را برای هر تعداد ردیف لازم کپی کنید. سپس می توانید آن محدوده B:C را انتخاب کنید و از Paste Special برای چسباندن مقادیر به آن سلول ها استفاده کنید. پس از انجام این کار، می توانید با خیال راحت آدرس های اصلی را در ستون A حذف کنید.

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

Sub SplitAddress()
    Dim c As Range
    Dim j As Integer
    Dim n As String
    Dim addr As String

    Selection.Insert Shift:=xlToRight
    Selection.Offset(0, 1).Select

    For Each c In Selection
        j = InStr(1, c, " ")
        n = Left(c, j)
        c.Offset(0, -1) = n
        addr = Trim(Right(c, Len(c) - j))
        c = addr
    Next
End Sub

اگر ترجیح می دهید، می توانید یک تابع تعریف شده توسط کاربر ایجاد کنید که فقط شماره خانه را برمی گرداند:

Function GrabHouseNumber(Raw As String) As String
    Dim x As Variant
    Dim House As Variant

    x = Split(Raw, " ")     use space char to split elements into array
    House = x(0)   first element of array
    If Left(House, 1) Like "#" Then
        First char is numeric digit
        GrabHouseNumber = House  set return value as house number
    Else
        GrabHouseNumber = ""  First char is text, so not a house number
    End If
End Function

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