به زبان ساده تابع SUBSTITUTE (بخوانید سابس تِ تیود) اکسل تعویض چیزی در یک نوشته با یک چیز دیگر است. دقیقا مانند بازی فوتبال که مربی تصمیم می‌گیرد بازیکنی را با بازیکن دیگری تعویض کند.
شکل ساده این تابع اینگونه است:
=SUBSTITUTE( text, old_text, new_text )ورودی text نوشته اصلی ما است یا آدرس سلولی است که نوشته اصلی ما در آن وارد شده است و اجباری است.
ورودی old_text نوشته قدیمی ما است و همان چیزی است در نوشته اصلی که می‌خواهیم آن را جایگزین کنیم.
ورودی new_text یک نوشته جدید است که جایگزین نوشته قدیمی خواهد شد.
به جدول زیر دقت کنید:
◽ ستون B فرمولی است که ما می‌نویسیم.
◽ ستون C پاسخ فرمول ما خواهد شد.
| A | B | C |
|
1 | atal matal | =SUBSTITUTE(A1, "a", "x") | xtxl mxtxl |
|
2 | 100 kilo | =SUBSTITUTE( A2 , "kilo", "kg" ) | 100 kg |
|
3 | اتل متل | =SUBSTITUTE( A3, " ", "") | اتلمتل |
|
شرح فرمول B1
در نوشته اصلی تمامی حروف a با حرف x تعویض می‌شوند
شرح فرمول B2
به جای کلمه kilo در نوشته اصلی kg می‌نویسیم.
شرح فرمول B3
تمامی فاصله‌های نوشته اصلی را حذف می‌کنیم. فاصله را در داخل علامت " " قرار می‌دهیم و در اکسل ""، یعنی هیچ متنی.
فرمول جایگزین کردن حروف «ي» و «ك» عربی با فارسی
ابتدا حروف ی را جایگزین می‌کنیم و سپس در همان متن حروف ک را جایگزین می‌کنیم:
=SUBSTITUTE( SUBSTITUTE(A1, "ي", "ی"), "ك", "ک" )فرمول شمارش تعداد یک کلمه در یک نوشته
برای شماره تعداد حرف a در سلول A1 این فرمول را می‌نویسیم:
=LEN( A1 ) - LEN( SUBSTITUTE( A1, "a", "" ) )شرح فرمول: تفاوت طول سلول A1 را با حالتی که بدون کلمه a است را محاسبه کرده‌ایم.
فرمول شماره تعداد یک کلمه در یک ستون اکسل
برای این کار ساده‌ترین راه آن است که در ستون B فرمول زیر را بنویسید و آن را در بقیه سلول‌ها کپی کنید و در نهایت اعداد ستون B را جمع بزنید. فرمول زیر تعداد دفعات تکرار کلمه atal را محاسبه می‌کند:
=LEN( A1 ) - LEN( SUBSTITUTE( A1, "atal", "" ) )فرمول حذف یک کلمه در متن سلول اکسل
برای حذف کلمه atal از سلول a1 کافی است که آن را با هیچ چیزی یعنی "" جایگزین کنید:
=SUBSTITUTE( A1, "atal", "" )فرمول حذف تمامی فاصله‌های خالی در یک متن اکسل
=SUBSTITUTE( A1, " ", "" )برای حذف فاصله‌های اضافه در یک متن باید از تابع TRIM استفاده کنید. فاصله اضافه یعنی تمامی SPACEهایی که در ابتدا و انتهای یک متن است و همچنین تمام SPACEهایی وسط متن که بیش از ۱ عدد است.
سایر نکته‌ها و سوالات متداول جایگزینی در اکسل
- اگر می‌خواهید فقط یکبار چیزی را جایگزین کنید و نیازی به فرمول نویسی ندارید، در اکسل کلید Ctrl + H را بزنید تا پنجره Replace اکسل باز شود و در آن مشخص کنید که چه چیزی با چه چیز دیگری باید جایگزین شود.
- برای جایگزینی خروجی‌های خطا در اکسل مانند خطای N/A# باید از تابع IFERROR استفاده کنید.
می‌توانید متن سلول‌های و فرمول‌های جدول این مثال را در اکسل کپی کنید.
به عنوان اولین نفر، تجربه یا دیدگاه خود را بنویسید!