تابع Substitute در اکسل به زبان ساده + کاربردها و مثال

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

فرمول تابع substitute در اکسل

فرمول این تابع به شکل زیر نوشته می‌شود:

آرگوما‌ن‌های آن نیز به شرح زیر هستند:

• Text : محل قرارگیری رشته یا سلولی که رشته در آن نوشته شده است.
• Old-text : متنی که قصد تغییر دادن آن را دارید.
• New-text : رشته جدیدی که قرار است به جای متن قبلی قرار بگیرد.
• [instance_num] : شماره نمونه رشته قدیمی که قصد جایگزین کردنش را دارید. اگر می‌خواهید تمام نمونه‌ها عوض شوند، این آرگومان را خالی بگذارید.

با اینکه فرمول رسمی اکسل برای این تابع از عبارت Text یا متن به جای رشته استفاده می‌کند، اما شما می‌توانید اعداد و نمادها را نیز در این فرمول به کار ببرید. در صورتی که می‌خواهید حرفی را کاملا حذف کنید نیز کافی است جای آن را خالی بگذارید.

نحوه استفاده از تابع substitute در اکسل

برای درک هرچه بهتر این تابع، نمونه زیر را در نظر بگیرید.

فیلم آموزش جستجو و جایگزینی در اکسل با تابع SUBSTITUTE (رایگان) در فرادرس

کلیک کنید

در این جدول، چند کلمه مختلف در هر سلول داریم که می‌خواهیم یکی از حروف آن‌ها را با حرف دیگری جابه‌جا کنیم.

همان‌طور که می‌بینید، حرف مربوطه در هریک از این کلمات، در جای متفاوتی قرار دارد، به همین دلیل باید آرگومان instance_num  را در هر فرمول به درستی وارد کنیم.

در نهایت نتیجه مانند تصویر زیر شده و تمام حروف موردنظر به درستی جایگزین می‌شوند.

جایگزینی عدد با عدد

راحت‌ترین روش برای نشان دادن نحوه کارکرد هر فرمولی، به کار بردن مثال‌های متعدد است. در این نمونه به خصوص، شماره تلفنی را داریم که کد کشوری آن به اشتباه وارد شده است (۱+). هدف این است که این کد را با کمک تابع substitute در اکسل به کد درست (۹۸+) تغییر دهیم.

برای این منظور ابتدا سلولی که می‌خواهیم رشته جایگزین در آن نوشته شود را انتخاب می‌کنیم. در این مثال سلول «A1» را مدنظر قرار داده‌ایم. سپس فرمول زیر را در نوار فرمول می‌نویسیم:

این فرمول، رشته «۱» را در سلول A1 پیدا کرده و سپس آن را با رشته «۹۸» جایگزین می‌کند. وجود عدد ۱ به جای آرگومان instance_num به این معنا است که می‌خواهیم فقط اولین مقدار «۹۸» جایگزین شود.

همان‌طور که در تصویر بالا می‌بینید، اکسل به راحتی عدد کد را تغییر داده است. شما همچنین می‌توانید فرمول را به شکل‌های مختلف تغییر دهید تا نتیجه نهایی را ببیند. مثلا خالی گذاشتن جای یکی از اعداد یا عوض کردن مقدار آرگومان instance_num  که به عوض کردن اعداد بعدی منجر می‌شود. برای حذف عدد از متن در اکسل نیز می‌توانید از روش مشابه استفاده کنید.

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

فرض کنید لیستی از چند کلمه داریم که یکی از حروف آن‌ها اشتباه نوشته شده است و می‌خواهیم آن را در کل یک ستون درست کنیم. مثلا در نمونه زیر، باید حرف «ر»‌ را با «د» تعویض کنیم.

برای انجام این کار، فرمول زیر را در اولین سلول نتیجه (B2) می‌نویسیم.

سپس به کمک «Fill Handle» در گوشه سمت راست پایین همان سلول، آن را تا انتهای ستون گسترش می‌دهیم.

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

مثال جایگزین کردن نماد با استفاده از تابع substitute در اکسل

گاهی ممکن است از نماد اشتباهی برای جداسازی داده‌ها استفاده شده باشد. در این صورت می‌توانید این نماد را نیز به کمک تابع substitute در اکسل به راحتی تغییر دهید. در این مثال قصد داریم نماد «_» را در داده‌های زیر، با «فاصله» ‌(Space) جایگزین کنیم.

برای دستیابی به نتیجه دلخواه، فرمول زیر را درون سلول نتیجه اول (B2) نوشته و سپس آن را به کمک Fill Handle تا انتهای لیست گسترش می‌دهیم.

به این شکل، در تمام سلول‌ها، نماد «ـ» با فاصله جایگزین شده و در ستون نتایج نشان داده خواهد شد. برای جدا کردن نام و نام خانوادگی در اکسل نیز می‌توانید از روش مشابه کمک بگیرید.

جایگزین کردن تمام حروف اشتباه رشته با فرمول substitute

در مثال‌های قبلی، تنها یکی از حروف را با کمک فرمول تابع substitute در اکسل در سلول جایگزین می‌کردیم. در این نمونه قصد داریم تمام نمادهای «-» موجود در سلول را با «فاصله» (Space) جابه‌جا کنیم.

جدول ابتدایی مانند تصویر زیر است.

برای انجام این کار از فرمول زیر کمک می‌گیریم.

همان‌طور که ‌می‌بینید، جای آرگومان instance_num را خالی گذاشته‌ایم. این یعنی تمام نمونه‌های موجود از این نماد در کل سلول با نماد جدید جایگزین شوند.

نتیجه‌ای که در تصویر بالا می‌بینید، این است که کل نمادهای «-» با فاصله جابه‌جا شده‌اند. اگر این فرمول را به صورت برعکس بنویسید به یکی از روش های حذف فاصله در اکسل تبدیل می‌شود.

استفاده از تابع substitute در اکسل به صورت تودرتو

متاسفانه تابع substitute در اکسل نمی‌تواند چندین رشته را درون یک سلول جایگزین کند. با این حال شما می‌توانید برای انجام این کار، چندین فرمول substitute را درون یک سلول بنویسید. روش مناسب برای انجام این کار، نوشتن فرمول به صورت تودرتو است.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

در این مثال، سلولی حاوی سه حرف اختصاری داریم و می‌خواهیم آن‌ها را با نام کامل جایگزین کنیم. به این منظور ابتدا سلول نتیجه را انتخاب کرده و سپس فرمول زیر را درون نوار فرمول می‌نویسیم.

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

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

استفاده از تابع substitute برای تبدیل اعداد به آدرس اکسل

یکی دیگر از روش‌های استفاده از تابع substitute در اکسل به کار بردن آن در ترکیب با سایر توابع است. به عنوان نمونه، فرض کنید می‌خواهیم شماره ستون‌های اکسل را به عدد تبدیل کنیم. درست مانند شماره ردیف‌ها که به شکل A ،B ،C و مانند آن‌هستند. جدول ورودی به شکل زیر است.

به این منظور باید تابع substitute را با تابع Address در اکسل ترکیب کنیم. تابع Address برای نمایش آدرس سلول‌ها به کار می‌رود و معمولا در کنار سایر توابع از آن استفاده می‌شود. برای اینکه ببینیم هریک از این اعداد به عنوان ستون، اگر به حروف تبدیل شوند، چگونه خواهند شد، فرمول را به شکل زیر می‌نویسیم:

در این فرمول ابتدا تابع Address، آدرس سلول را به صورت متنی تولید می‌کند. در این تابع آرگومان اول که ما به جای آن عدد «۱» را نوشته‌ایم، برای یافتن شماره ردیف به کار می‌رود. سپس آرگومان دوم، شماره ستون را پیدا می‌کند که در این مثال درون سلول A2 قرار دارد. آرگومان سوم نیز پرچمی برای مشخص کردن نوع ارجاع است و در این نمونه از آدرس مطلق ستون استفاده شده است.

در ادامه تابع substitute وارد کار شده و زیررشته «۱» که نشان دهنده شماره ردیف در آدرس تولیدی است را با یک «رشته خالی» ("") جایگزین کرده و به این شکل، شماره ردیف را از آدرس سلول حذف می‌کند.

همان‌طور که می‌بینید، تمام اعداد موجود در لیست به حروف متناظر تبدیل شده‌اند و از آنجا که حروف انگلیسی تنها ۲۶ عدد هستند، برای نمایش اعداد بزرگ‌تر از ترکیبی از آن‌ها استفاده شده است.

نکات مهم درباره تابع substitute در اکسل

در ادامه برخی از نکات مهم درباره تابع substitute در اکسل را می‌بینیم:

• از تابع substitute برای جایگزین کردن متن در اکسل استفاده می‌شود. این تابع از نسخه اکسل ۲۰۰۷ معرفی شده است.
• توابع Replace و substitute عملکردی شبیه به همدیگر دارند اما از تابع substitute در اکسل برای جایگزین کردن متن در موقعیت‌های مختلف استفاده می‌شود.
• این تابع حروف بزرگ و کوچک انگلیسی را به عنوان دو ورودی جداگانه در نظر می‌گیرد. به عنوان مثال دو کلمه «Apple» و «apple» در این تابع، به صورت متفاوت در نظر گرفته می‌شوند.

سخن پایانی

تابع substitute در اکسل این قابلیت را به شما می‌دهد که حروف و کلمات موجود در یک رشته را به راحتی با هر متن دیگری جابه‌جا کنید. البته امکان استفاده از نمادها و اعداد نیز با این تابع وجود دارد. در این مقاله ابتدا فرمول این تابع را دیدیم و آرگومان‌های آن را توضیح دادیم.

در ادامه این مطلب از مجله فرادرس تلاش کردیم با ذکر مثال‌های مختلف در موقعیت‌های متفاوت، بیشتر با عملکرد تابع substitute در اکسل آشنا شویم. به عنوان نمونه، مثال‌هایی برای استفاده از این تابع به منظور جایگزینی حروف، اعداد و حتی نمادها را دیدیم.

در نهایت نیز به آموزش استفاده از این تابع به شکل تودرتو و در ترکیب با شایر توابع پرداختیم. مثال اول، نمونه‌ای برای معرفی نحوه نوشتن چند تابع substitute به صورت تودرتو بود که امکان جایگزینی چندین رشته در یک سلول را به ما می‌داد. در مثال آخر نیز ترکیب این تابع با تابع Address و کارکرد آن را بررسی کردیم.