افزودن فاصله در مقابل حروف بزرگ

Question

محمد فهرست بلندبالایی از نام شرکت ها را در یک کاربرگ دارد. نام هر شرکت به نحوی تمام فضاهای خالی را حذف کرده است، به طوری که (به عنوان مثال) به جای "Astro Physics" به عنوان "AstroPhysics" ظاهر می شود. محمد راهی برای پردازش همه نام های شرکت برای درج یک فاصله بین هر حرف بزرگ، به جز حرف اول در سلول، نیاز دارد. او فکر می کند که آیا راه آسانی برای انجام این کار وجود دارد؟

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

در این حالت بهترین راه حل این است که عملیات Find and Replace را انجام دهید تا کاراکتر غیرچاپی را با یک فضای واقعی جایگزین کنید. (می توانید نویسه غیرچاپ را انتخاب کنید - کلید Shift را در حالی که فلش سمت راست را روی جایی که فکر می کنید فشار می دهید نگه دارید - و آن را کپی کنید. سپس آن را در کادر Find What قرار دهید تا جستجو انجام شود.)

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

=SMALL(FIND(0,SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),0)&0),2)

اگر این فرمول در سلول B1 وارد شده باشد، می توانید از موارد زیر برای درج فاصله در نام اصلی شرکت استفاده کنید:

=LEFT(A1,B1-1)&" " & RIGHT(A1,(LEN(A1)-B1)+1)

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

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

Function Add_Spaces(ByVal sText As String) As String
   Dim CharNum As Long
   Dim FixedText As String
   Dim CharCode As Long

   FixedText = Left(sText, 1)

   For CharNum = 2 To Len(sText)
      CharCode = Asc(Mid(sText, CharNum, 1))
      If CharCode >= 65 And CharCode 

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

=Add_Spaces(A1)

یک رویکرد فشرده تر، تکیه بر عبارات منظم برای تعیین مکان حروف بزرگ و درج یک فاصله است، همانطور که در زیر نشان داده شده است:

Function SplitCaps(str As String) As String
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")

    With objRegex
        .Global = True
        .Pattern = "([a-z])([A-Z])"
        SplitCaps = .Replace(str, "$1 $2")
    End With
End Function

این تکنیک خاص ماکرو مستلزم آن است که عبارات منظم را فعال کنید. این کار را در ویرایشگر VB با انتخاب Tools | انجام می دهید مراجع و سپس پیمایش در مراجع موجود برای یافتن گزینه Microsoft VBScript Regular Expressions 5.5. مطمئن شوید که کادر سمت چپ مرجع انتخاب شده است، سپس روی OK کلیک کنید. سپس می توانید از تابع SplitCaps از داخل اکسل به همان روشی که می توانید از تابع Add_Spaces استفاده کنید، استفاده کنید.

البته، مناسب بودن هر رویکرد، تا حد زیادی به ویژگی های داده ای که با آن کار می کنید بستگی دارد. اگر نام اصلی شرکت شما شامل چندین حروف بزرگ متوالی (مانند "ABCCorp.") باشد، در نهایت با فاصله های بسیار زیادی در خروجی خود خواهید داشت، مانند "ABCCorp". تبدیل به "ABC Corp" می شود. به جای "ABC Corp." همچنین، شخصیت های غیرحرفی ممکن است آچاری را در آثار پرتاب کنند که «H&M» به «H&M» تبدیل شود یا به عنوان «H&M» باقی بماند (بسته به رویکرد کلان شما).

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

Sub AddSpaces()
    Dim i As Integer
    Dim j As Integer
    Dim PriorCap As Boolean
    Dim temp As String

    j = 1

    Do While Cells(j, 2) <> ""
        Cells(j, 3) = ""
        PriorCap = True
        For i = 1 To Len(Cells(j, 2))
            Select Case Mid(Cells(j, 2), i, 1)
                Case "A" To "Z", "-"
                    If PriorCap = False Then
                        Cells(j, 3) = Cells(j, 3) & " " & _
                          Mid(Cells(j, 2), i, 1)
                    Else
                        Cells(j, 3) = Cells(j, 3) & _
                          Mid(Cells(j, 2), i, 1)
                    End If
                    PriorCap = True
                Case Else
                    Cells(j, 3) = Cells(j, 3) & _
                      Mid(Cells(j, 2), i, 1)
                    PriorCap = False
            End Select
        Next i
        j = j + 1
    Loop
End Sub