جایگزینی حروف با اعداد در اکسل

Question

بیل ستونی از متن دارد که در آن باید تمام حروف را با عددی که نشان دهنده موقعیت حرف در الفبا است جایگزین کند. بنابراین، برای مثال، اگر سلولی حاوی "T10A22" باشد، بیل باید حروفی مانند "2010122" را جایگزین کند، جایی که T با 20 و A با 1 جایگزین می شود. هر سلول داده شده

اگر لازم است این کار را فقط یک بار (در یک کاربرگ واحد) انجام دهید، ممکن است انتخاب ستون و استفاده از Find and Replace به اندازه کافی آسان باشد. می توانید A را جستجو کنید و با 1 جایگزین کنید، B را جستجو کنید و با 2 جایگزین کنید و غیره. تنها 26 تکرار برای انجام وجود خواهد داشت، که ممکن است سخت به نظر برسد، اما می توان آن را در زمان کمتری نسبت به ایجاد یک ماکرو برای انجام تغییرات انجام داد.

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

Sub ReplaceLetters1()
    Selection.Replace What:="A", Replacement:="1", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="B", Replacement:="2"
    Selection.Replace What:="C", Replacement:="3"
    Selection.Replace What:="D", Replacement:="4"
    Selection.Replace What:="E", Replacement:="5"
    Selection.Replace What:="F", Replacement:="6"
    Selection.Replace What:="G", Replacement:="7"
    Selection.Replace What:="H", Replacement:="8"
    Selection.Replace What:="I", Replacement:="9"
    Selection.Replace What:="J", Replacement:="10"
    Selection.Replace What:="K", Replacement:="11"
    Selection.Replace What:="L", Replacement:="12"
    Selection.Replace What:="M", Replacement:="13"
    Selection.Replace What:="N", Replacement:="14"
    Selection.Replace What:="O", Replacement:="15"
    Selection.Replace What:="P", Replacement:="16"
    Selection.Replace What:="Q", Replacement:="17"
    Selection.Replace What:="R", Replacement:="18"
    Selection.Replace What:="S", Replacement:="19"
    Selection.Replace What:="T", Replacement:="20"
    Selection.Replace What:="U", Replacement:="21"
    Selection.Replace What:="V", Replacement:="22"
    Selection.Replace What:="W", Replacement:="23"
    Selection.Replace What:="X", Replacement:="24"
    Selection.Replace What:="Y", Replacement:="25"
    Selection.Replace What:="Z", Replacement:="26"
End Sub

برای استفاده از ماکرو، کافی است سلول هایی را که می خواهید پردازش کنید انتخاب کنید و سپس آن را اجرا کنید. همچنین می توانید از یک رویکرد متفاوت که بر حلقه For...Next متکی است، به این صورت استفاده کنید:

Sub ReplaceLetters2()
    Dim J As Integer

    Selection.Replace What:="A", Replacement:="1", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    For J = 66 To 90
        Selection.Replace What:=Chr(J), Replacement:=J - 64
    Next J
End Sub

در حالی که اولین عملیات Replace را می‌توان در داخل حلقه For...Next قرار داد، من تصمیم گرفتم آن را جدا نگه دارم تا (حداقل برای من) حلقه ساده‌تر به نظر برسد. این یک انتخاب سبکی بیش از هر چیز دیگری است.

اگر به دلایلی نمی‌خواهید از روش .Replace استفاده کنید، می‌توانید از روش قدیمی نگاه کردن به هر شخصیت در هر چیزی که ارزیابی می‌کنید استفاده کنید. این رویکرد مورد استفاده در این تابع تعریف شده توسط کاربر است:

Function LetterToNum(c As Range) As String
    Dim sRaw As String
    Dim J As Integer

    sRaw = UCase(c)
    For J = 1 To 26
        sRaw = Replace(sRaw, Chr(J + 64), J)
    Next J
    LetterToNum = sRaw
End Function

برای استفاده از این تابع، به سادگی موارد زیر را در کاربرگ خود قرار دهید، جایی که A1 سلولی است که می خواهید تبدیل کنید:

=LetterToNum(A1)

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

=TEXTJOIN("",FALSE, IF(ISNUMBER(VALUE(MID(A1,SEQUENCE(1,LEN(A1)),1))), MID(A1,SEQUENCE(1,LEN(A1)),1), COLUMN(INDIRECT(MID(A1,SEQUENCE(1,LEN(A1)),1) & ":" & MID(A1,SEQUENCE(1,LEN(A1)),1)))))

این فرمول فرض می کند که متنی که می خواهید تبدیل کنید در سلول A1 است. اساساً به هر کاراکتر در رشته متن نگاه می کند و تعیین می کند که آیا رقم است یا خیر. اگر چنین است، از شخصیت اصلی استفاده می کند. اگر اینطور نیست، از تابع INDIRECT برای تعیین یک ستون بر اساس حرف استفاده می کند و سپس از COLUMN برای برگرداندن یک مقدار عددی برای آن ستون استفاده می کند. سپس همه اینها با استفاده از تابع TEXTJOIN دوباره ترکیب می شوند.