فرض کنید شما یک کاربرگ با تعداد زیادی کد محصول در ستون A دارید. این کدها در قالب A4، B12، AD4 و غیره هستند. آنها حاوی یک خط تیره بین حروف و اعداد هستند.
راه های مختلفی برای انجام این کار وجود دارد. اگر ساختار کدهای محصول شما یکسان است، در آن قرار دادن خط تیره ها یک ضربه فوری است. به عنوان مثال، اگر همیشه یک حرف به دنبال اعداد وجود داشته باشد، می توانید از فرمولی مانند زیر استفاده کنید:
=LEFT(A1,1) & "-" & RIGHT(A1,LEN(A1)-1)
این احتمال وجود دارد که داده های شما ساختاری نداشته باشند، به این معنی که می توانید یک یا دو حرف و حداکثر سه رقم داشته باشید. بنابراین، هر دو A4 و QD284 هر دو کد محصول معتبر خواهند بود. در این مورد، یک فرمول راه حل کمی خلاقیت بیشتری می طلبد.
یکی از راه های رسیدگی به آن با فرمول آرایه است. فرمول زیر را در نظر بگیرید:
=REPLACE(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0),0,"-")
اگر مقادیر در A1-A10 هستند، می توانید این فرمول را در B1 قرار دهید و سپس آن را در ستون کپی کنید. از آنجایی که فرمول آرایه است، باید با فشردن کلیدهای Ctrl+Shift+Enter وارد شود. فرمول محل اولین عدد را در سلول پیدا می کند و یک خط تیره قبل از آن درج می کند.
برای مثال فرض کنید سلول A1 حاوی BR27 است. داخلی ترین بخش فرمول، INDIRECT("1:100")، متن 1:100 را به یک محدوده تبدیل می کند. این مورد استفاده قرار می گیرد تا درج یا حذف ردیف ها بر فرمول تأثیری نداشته باشد. بخش بعدی فرمول، ROW(INDIRECT("1:100"))، اساساً آرایه ای از مقادیر 1-100 را ایجاد می کند: 1،2،3،...،99،100. این برای عمل بر روی هر شخصیت در سلول استفاده می شود.
بخش بعدی، MID(A1,ROW(INDIRECT("1:100"))،1)، به هر کاراکتر جداگانه در رشته اشاره دارد. این منجر به آرایه می شود: "B"، "R"، "2" و "7". ضرب آرایه در 1 (بخش بعدی فرمول) منجر به تبدیل هر یک از کاراکترهای مجزا به یک عدد می شود. اگر کاراکتر یک عدد نباشد، این تبدیل یک خطا ایجاد می کند. در مورد رشته در حال تبدیل (BR27)، این نتیجه به: #VALUE، #VALUE، 2 و 7 می شود.
مرحله بعدی اعمال تابع ISERROR در نتایج حاصل از ضرب است. این خطاها را به TRUE و غیر خطاها را به FALSE تبدیل می کند و TRUE، TRUE، FALSE و FALSE را به دست می دهد. تابع MATCH در آرایه مقادیر TRUE و FALSE برای مطابقت دقیق FALSE جستجو می کند. در این مثال، تابع MATCH عدد 3 را برمی گرداند، زیرا اولین مقدار FALSE در موقعیت سوم آرایه است. در این مرحله، ما اساساً مکان اولین عدد را در سلول می دانیم.
تابع نهایی REPLACE است که برای درج خط تیره در رشته منبع استفاده می شود که از کاراکتر سوم شروع می شود.
همانطور که می توانید بگویید، رمزگشایی فرمول انجام تبدیل می تواند کمی دلهره آور باشد. برای کسانی که اینقدر تمایل دارند، ممکن است ساده تر باشد که فقط یک تابع تعریف شده توسط کاربر ایجاد کند. ماکرو زیر نمونه ای از ماکرو است که رشته ای را با خط تیره در جای مناسب برمی گرداند:
Function DashIn(myText As String)
Dim i As Integer
Dim myCharCode As Integer
Dim myLength As Integer
Application.Volatile
myLength = Len(myText)
For i = 1 To myLength
myCharCode = Asc(Mid(myText, i, 1))
If myCharCode >= 48 And myCharCode <= 57 Then
Exit For
End If
Next i
If i = 1 Or i > myLength Then
DashIn = myText
Else
DashIn = Left(myText, i - 1) & "-" _
& Mid(myText, i, myLength - 1)
End If
End Function
ماکرو هر کاراکتر را در رشته اصلی بررسی می کند و وقتی اولین کاراکتر عددی را پیدا کرد، یک خط تیره در آن نقطه وارد می کند. شما از تابع به این صورت استفاده می کنید:
=DashIn(A1)
