جدا کردن URL

Question

جردن نیاز دارد که URL را به اجزای آن تقسیم کند. اگر URL کامل (http://www.xyz.com/business) در ستون A باشد، او می خواهد راهی برای داشتن دامنه اصلی (xyz یا xyz.com) در ستون B و مسیر (کسب و کار) در ستون B داشته باشد. سی.

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

اگر این کار را بیشتر انجام می دهید، ممکن است از استفاده از فرمولی برای دستکاری URL بهره مند شوید. اولین کار این است که دامنه را از URL بیرون بکشید، اما این کمی پیچیده تر از آن چیزی است که به نظر می رسد. به عنوان مثال، URL ممکن است با هر تعداد مشخص کننده پروتکل (مانند http://، https://، ftp://، و غیره) شروع شود یا اصلاً با یک مشخص کننده پروتکل شروع نشود. به علاوه، ممکن است بخواهید "www" را حذف کنید. نامگذاری، همانطور که برخی افراد آن را بیگانه می دانند. برای اینکه بفهمید دامنه واقعی از کجا شروع می شود، می توانید از این فرمول در ستون B استفاده کنید:

=IFERROR(FIND("www.",$A1)+4,IFERROR(FIND("://",$A1,1)+3,1))

برای بازگرداندن موقعیت اولین کاراکتر بعد از "www."، در صورت موجود بودن، چه کاری انجام می دهد. اگر موجود نباشد، در صورت وجود، موقعیت اولین کاراکتر را بعد از "://" برمی گرداند. اگر این مورد وجود ندارد، شماره 1 برگردانده می شود، زیرا URL حاوی هیچ پیشوند پروتکل یا www نیست.

با این مقدار در ستون B، سپس می توانید شروع به بیرون کشیدن قطعات مورد نظر خود کنید. موارد زیر که در ستون C قرار می گیرند، دامنه را همانطور که قبلاً توضیح داده شد، برمی گرداند. اساساً همه چیز را از موقعیت کاراکتر نشان داده شده در ستون B تا اسلش بعدی (/) برمی گرداند.

=MID(A1,B1,IFERROR(FIND("/",A1,B1)-B1,LEN(A1))

اگر URL اصلی شامل "www.xyz.com" بود، "xyz.com" با فرمول برگردانده می شود. اگر شامل یک زیر دامنه متفاوت غیر از "www" (مانند "research.xyz.com") بود، دامنه کامل شامل زیر دامنه برگردانده می شود. تابع IFERROR در صورتی که هیچ علامت (/) بعد از نام دامنه وجود نداشته باشد گنجانده شده است. (اگر تابع FIND نتواند موردی را که به دنبال آن است پیدا کند، یک خطای #VALUE برمی گرداند.)

برای انتخاب مسیر بعد از دامنه، می توانید از فرمول زیر در ستون D استفاده کنید:

=IFERROR(MID(A1,FIND("/",A1,B1)+1,LEN(A1)),"")

فرمول همه چیز را پس از اولین اسلش (/) که بعد از نام دامنه رخ می دهد، برمی گرداند. اگر بعد از نام دامنه بریده ای وجود نداشته باشد، چیزی بر نمی گرداند. (این دوباره به لطف استفاده از تابع IFERROR است.)

کلید استفاده از فرمول های بالا، مجدداً، ستون کمکی در ستون B است. اگر می خواهید نیاز به ستون B را از بین ببرید، باید تمام نمونه های B1 را با فرمول (بدون) در دو فرمول دیگر جایگزین کنید. علامت مساوی) که به B1 تعلق دارد. البته این دو فرمول دیگر را بسیار طولانی می کند، به خصوص فرمول مورد نظر برای ستون C.

در صورت تمایل می توانید یک ماکرو ایجاد کنید که اساساً همان کار را انجام می دهد، بدون نیاز به ستون کمکی. مثال زیر از شما انتظار دارد که انتخابی انجام دهید و سپس بخش هایی از URL را از سلول ها بیرون می کشد و آنها را در دو ستون سمت راست انتخاب قرار می دهد.

Sub GetURLParts()
    Dim c As Range
    Dim sRaw As String
    Dim J As Integer
    
    For Each c In Selection
        sRaw = c.Text
        J = InStr(sRaw, "://")
        If J > 0 Then sRaw = Mid(sRaw, J + 3)
        If LCase(Left(sRaw, 4)) = "www." Then
            sRaw = Mid(sRaw, 5)
        End If
        J = InStr(sRaw, "/")
        If J > 0 Then
            c.Offset(0, 1) = Left(sRaw, J - 1)
            c.Offset(0, 2) = Mid(sRaw, J + 1)
        Else
            c.Offset(0, 1) = sRaw
            c.Offset(0, 2) = ""
        End If
    Next c
End Sub