معرفی توابع متنی (TextFunctions) - بخش 1
در این بخش، به یکسری از توابع متنی پرکاربرد میپردازیم. این توابع روی آرگومانهای رشتهای کار میکنند.
تابع Left : این تابع، تعداد مشخص شده از کاراکترهای سمت چپ رشته ورودی را باز میگرداند.
RE=LEFT("REZA",2)
که دو کاراکتر ابتدایی این رشته را باز میگرداند. حالا با یک مثال کاربرد این تابع را بررسی میکنیم.
فرض کنید گزارشی از میزان فروش روزانه سایت در تاریخهای مشخص داریم. حال میخواهیم میزان کلی فروش را در هر سال به دست آوریم. ابتدا باید یک ستون با عنوان سال، به گزارش اضافه کنیم. جهت تکمیل محتوای این ستون، با استفاده از تابع Left ، چهار رقم ابتدایی ستون تاریخ فروش گزارش اولیه را استخراج میکنیم:
=LEFT(I2,4)
در این مثال، سلول I2 حاوی تاریخ کامل به فرم 1390/01/02 است، که پس از اجرای تابع، ستون سال حاوی 1390 خواهد بود. جهت نمایش فروش سالانه میتوانید از طریق Auto Filter و انتخاب سال مورد نظر اطلاعات مرتبط را مشاهده کنید.
تابع MID: این تابع، تعداد مشخص شده از کاراکترهای وسط رشته ورودی را باز میگرداند.
=MID("Ali Mohebi",5,100)
Mohebi
فرض کنید میخواهیم نام خانوادگی را از رشته بالا استخراج کنیم. کافیست در آرگمان اول، رشته و آرگمان دوم محل شروع استخراج کاراکترها را مشخص کنیم. آرگومان سوم، نشانگر تعداد کاراکترهایی است که باید برگردانده شود. در این مثال اگر عدد 6 را هم وارد میکردیم، همین پاسخ را میگرفتیم، اما اگر نمیخواهید تعداد کاراکترها را شمارش کنید، میتوانید فقط یک عدد به قدر کافی بزرگ را بزنید.
نکته) در توابع متنی، اگر به عنوان آرگومان اول، از آدرس سلولی استفاده کنیم تفاوتی نمیکند که سلول حاوی عدد یا رشته باشد، به هر حال تابع با آن رودی مشابه رشته رفتار میکند.
نکته) در گزارشهای مربوط به تحقیق کلمات کلیدی، توابع متنی کارکرد مهمی دارند.
تابع Right: این تابع تعداد مشخص شده از کاراکترهای سمت راست رشته ورودی را باز میگرداند.
فرض کنید در مثال بالا میخواهیم عدد روز را از تاریخ کامل استخراج کنیم.
=Right(I2,2)
در این مثال، سلول I2 حاوی تاریخ کامل به فرم 1390/01/02 است، که پس از اجرای تابع، ستون روز، حاوی 02 خواهد بود.
به همین ترتیب میتوان با استفاده از تابع Mid ، عدد ماه را نیز استخراج نمود.
=MID(I2,6,2)
در این مثال، سلول I2 حاوی تاریخ کامل به فرم 1390/01/02است، که پس از اجرای تابع، ستون روز حاوی 01 خواهد بود.
شروع استخراج کاراکترها از جایگاه ششم است. چهار جایگاه اول مربوط به عدد سال و جایگاه پنجم مربوط به کاراکتر / است.
تابع Len: تعداد کاراکترهای یک رشته متنی را برمیگرداند.
=LEN("Ali Mohebi") 9
با یک مثال کاربرد این تابع را بررسی میکنیم. فرض کنید در مثال بالا، تاریخ با فرمت درستی وارد نشده است. مثلاً در بعضی از رکوردها اعداد ماه و سال به صورت تک رقمی وارد شدهاند. در این صورت، ستونهای روز و ماه که با استفاده از توابع Right و Mid استخراج شدهاند، دارای مقادیر نادرستی خواهند شد. بنابراین لازم است که چنین تاریخهایی را شناسایی کنیم. بدین منظور از تابع Len استفاده میکنیم و چک میکنیم که طول محتوای سلول تاریخ کمتر از 10 کاراکتر نباشد.
تابع Search: این تابع، وجود یک زیررشته را در رشته ورودی بررسی میکند و در صورت وجود زیررشته، جایگاه شروع آن در رشته ورودی را بر میگرداند. چون ممکن است زیررشته در چند نقطه از رشته ورودی تکرار شده باشد، در آرگومان سوم میتوانید محل شروع جستجو را مشخص کنید.
=Search("I","Ali Mohebi",1)
3
در این مثال، کاراکتر I یا i را از ابتدای رشته Ali Mohebi جستجو میکند و مکان آن را برمیگرداند.
=Search("I","Ali Mohebi",5)
10
کاراکتر I یا i را از جایگاه پنجم رشته Ali Mohebi جستجو میکند و مکان آن را برمیگرداند .
اگر زیررشته در رشته ورودی وجود نداشته باشد، با خطای #VALUE! مواجه خواهید شد.
تابع Replace: بخشی از رشته متنی ورودی را در رشته متنی دیگری جایگزین میکند. آرگومان اول این تابع، رشته متنی اولیه، آرگومان دوم، جایگاه شروع جایگزینی، آرگومان سوم تعداد کاراکترهایی که میخواهید جایگزین کنید و آرگومان آخر رشته متنی است که میخواهید جایگزین شود.
=REPLACE("Ali Mohebi",5,2,"****")
Ali ****hebi
در این مثال، میخواهیم از جایگاه پنجم، رشته ورودی Ali Mohebi دو کاراکتر را با رشته **** جایگزین کنیم.
کاربرد این تابع، در نمایش امن شماره کارت بانکی یا شماره موبایل است. فرض کنید یک شماره کارت داریم و یخواهیم چهار رقم میانی آن را با کاراکتر * جایگزین کنیم.
B11 à1234-6666-6666-6666
=REPLACE(B11,6,14,"********")
1234-********-6666
تابع Substitute:تفاوت این تابع با تابع Replace در این است که، جایگزینی به ازای کاراکتری که مد نظر شماست انجام میشود. آرگومان اول این تابع، رشته متنی اولیه، آرگومان دوم، رشته متنی است که میخواهید جایگزین شود و آرگومان سوم کاراکترهایی که میخواهید جایگزین کنید.
فرض کنید که میخواهید در یک رشته ورودی همه فاصلهها را با کاراکتر - جایگزین یا حذف کنید.
=SUBSTITUE("Ali Mohebi AALLL"," ","")
AliMohebiAALLL
در این مثال میخواهیم کاراکتر فاصله را از رشته ورودی، حذف کنیم.
=SUBSTITUE("Ali Mohebi AALLL"," ","-")
Ali-Mohebi---AAALLL
در اینجا کاراکتر فاصله با کاراکتر - جایگزین شده است.
این تابع یک آرگومان اختیاری به نام Instance Number هم دارد. در صورتی که زیررشته متنی که قصد جایگزینی آن را دارید، بیش از یک تکرار در رشته ورودی داشته باشد، با این عدد شان میدهید که قصد جایگزین کردن کدام یک از این تکرارها را دارید.
=SUBSTITUE("Ali Mohebi AALLL"," ","-",1)
Ali-Mohebi AAALLL
همانطور که میبینید، فقط اولین کاراکتر فاصله با کاراکتر - جایگزین شده است.
=SUBSTITUE("Ali Mohebi AALLL"," ","-",2)
Ali Mohebi- AAALLL
در این حالت، دومین تکرار کاراکتر فاصله با کاراکتر - جایگزین شده است.