کوتاه کردن همه فضاها

Question

سوداکار داده هایی در ستون A دارد که دارای فضاهای انتهایی است. اگر از تابع TRIM روی داده ها استفاده کند، برخی از فضاهای انتهایی را حذف می کند، اما نه همه آنها. او تعجب می کند که چرا برخی از فضاها نادیده گرفته می شوند و چگونه می تواند همه آنها را حذف کند.

در واقع چند راه مختلف وجود دارد که می توانید با این مشکل برخورد کنید. روش مناسب برای اهداف شما همیشه به ماهیت داده هایی که استفاده می کنید بستگی دارد.

اول از همه، اگر از TRIM در یک سلول استفاده می کنید، به یاد داشته باشید که فقط فضاها را حذف می کند. این، طبق تعریف، به این معنی است که فقط کاراکترهایی را حذف می کند که دارای کد اسکی 32 هستند. اینها توسط TRIM حذف نمی شوند.

اگر بتوانید بفهمید آن شخصیت های مزاحم چه هستند، کمک می کند، درست است؟ خوب، اگر متن داخل سلول آنقدر طولانی نیست، یک ماکرو کوچک مفید در اینجا وجود دارد که به محتویات سلول نگاه می کند و هر کاراکتر را به نوبه خود همراه با مقدار ASCII آن نمایش می دهد:

Sub StringContents()
    Dim sTemp As String
    Dim sMsg As String
    Dim J As Integer
    
    If Selection.Cells.Count > 1 Then
        sMsg = "Please select only one cell"
    Else
        sMsg = "Full string: >" & ActiveCell.Value & "<" & vbCrLf
        For J = 1 To Len(ActiveCell.Value)
            sTemp = Mid(ActiveCell.Value, J, 1)
            sMsg = sMsg & ">" & sTemp & "<     " & Asc(sTemp) & vbCrLf
        Next J
    End If
    MsgBox sMsg
End Sub

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

هنگامی که مقدار ASCII کاراکتر توهین آمیز را که TRIM حذف نمی کند، دانستید، می توانید آن کاراکتر را جایگزین کنید. به عنوان مثال، اگر فضای غیرفضا مقدار ASCII 160 را نشان دهد، می توانید جایگزینی را به این صورت انجام دهید:

=SUBSTITUTE(A1,CHAR(160)," ")

این کاراکتر فضای واقعی را برای هر نویسه ASCII 160 در رشته جایگزین می کند. به همین ترتیب می توانید سایر فضاهای جعلی مشابه را حذف کنید.

البته، برای خلاص شدن از شر برخی از کاراکترهای ASCII غیر چاپی، می توانید از یک تابع اکسل متفاوت استفاده کنید، مانند این:

=CLEAN(A1)

از شر همه چیز خلاص نمی شود، اما در پاکسازی بسیاری از شخصیت های متخلف کار خوبی انجام می دهد.

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

=TRIM(CLEAN(A1))

می توانید یک قدم جلوتر بروید، به این ترتیب:

=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," "))

این فرمول از شر کاراکترهای ASCII 160 و همچنین چیزهایی که توسط CLEAN گرفتار شده است خلاص می شود و سپس یک TRIM بر روی آن نتیجه انجام می دهد.

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

Sub CleanCells()
    Dim rTarget As Range
    Dim c As Range
    Dim sTemp As String
    Dim J As Integer
    
    Set rTarget = Selection.SpecialCells(xlCellTypeConstants, 2)
    
    For Each c In rTarget
        sTemp = c.Value
        For J = 1 To 31
            sTemp = Replace(sTemp, Chr(J), " ")
        Next J
        For J = 127 To 255
            sTemp = Replace(sTemp, Chr(J), " ")
        Next J
        c.Value = sTemp
    Next c
End Sub

ماکرو فقط روی آن دسته از سلول های موجود در انتخاب فعلی که حاوی مقادیر ثابت هستند کار می کند. به عبارت دیگر، سلول هایی که حاوی فرمول نیستند. و از آنجایی که جایگزین هر چیزی با مقدار ASCII 127 یا بیشتر است، ممکن است با کاراکترهایی جایگزین شوید که واقعاً نمی خواهید جایگزین شوند (مانند کاراکترهای زبان خارجی). اگر چنین شد، باید متن خود را همانطور که قبلاً بحث شد ارزیابی کنید و فقط آن دسته از کاراکترهایی را که ناخواسته هستند تغییر دهید.