آشنایی با تابع substitute اکسل - انتشارات و آکادمی آموزشی اشکاوند

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

در چنین شرایطی تابع SUBSTITUTE یکی از توابع بسیار کاربردی اکسل است.

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

تابع SUBSTITUTE در اکسل چیست؟

تابع SUBSTITUTE برای جایگزین کردن بخشی از یک متن با متن دیگر استفاده می‌شود.

ساختار این تابع به شکل زیر است:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

پارامترهای تابع عبارت‌اند از:

text : متن یا سلولی که می‌خواهیم آن را تغییر دهیم.
old_text : متن یا کاراکتری که باید پیدا شود.
new_text : متن یا کاراکتری که باید جایگزین شود.
instance_num : شماره وقوع موردنظر؛ این بخش اختیاری است.

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

نکته: این تابع نسبت به حروف کوچک و بزرگ حساس می‌باشد و از WILDCARDها نیز پشتیبانی نمی‌کند.

مثال: در شکل زیر حالت‌های مختلف استفاده از این تابع را مشاهده می‌کنید.

یک مثال دیگر از SUBSTITUTE

فرض کنید در سلول A2 عبارت زیر قرار دارد:

آموزش اکسل حرفه ای

می‌خواهیم کلمه «اکسل» را با «Excel» جایگزین کنیم.

فرمول:

=SUBSTITUTE(A2,"اکسل","Excel")

نتیجه:

آموزش Excel حرفه ای

خواهد بود.

در واقع SUBSTITUTE متن موردنظر را پیدا کرده و عبارت جدید را جایگزین آن می‌کند.

جایگزین کردن یک کاراکتر

SUBSTITUTE فقط برای جایگزینی کلمه استفاده نمی‌شود و می‌توان یک کاراکتر را نیز تغییر داد.

فرض کنید مقدار سلول A2 به شکل زیر است:

1405-06-20

اگر بخواهیم خط تیره را با / جایگزین کنیم:

=SUBSTITUTE(A2,"-","/")

نتیجه:

1405/06/20

خواهد بود.

این کاربرد در پاک‌سازی و یکسان‌سازی داده‌ها بسیار مفید است.

حذف یک کلمه یا کاراکتر با SUBSTITUTE

یکی از کاربردهای جالب این تابع، حذف یک بخش از متن است.

برای حذف یک کلمه کافی است مقدار new_text را خالی قرار دهیم.

مثلاً:

=SUBSTITUTE(A2,"Excel","")

اگر مقدار A2 برابر باشد با:

آموزش Excel

نتیجه:

آموزش

خواهد بود.

بنابراین می‌توان گفت:

SUBSTITUTE علاوه بر جایگزینی، برای حذف بخشی از متن نیز کاربرد دارد.

حذف فاصله‌های موجود در متن

فرض کنید در سلول A2 عبارت زیر قرار دارد:

1 2 3 4 5

اگر بخواهیم فاصله‌ها را حذف کنیم:

=SUBSTITUTE(A2," ","")

نتیجه:

خواهد بود.

این روش زمانی مفید است که داده‌ها با فاصله وارد شده‌اند اما برای پردازش یا مقایسه به شکل بدون فاصله نیاز داریم.

تفاوت SUBSTITUTE و Replace

ممکن است این سؤال برایتان پیش بیاید که اگر در اکسل امکان Find & Replace وجود دارد، چرا از SUBSTITUTE استفاده کنیم؟

تفاوت مهم این است که SUBSTITUTE داخل فرمول قرار می‌گیرد و نتیجه آن به‌صورت پویا ایجاد می‌شود.

مثلاً:

=SUBSTITUTE(A2,"تهران","مشهد")

اگر مقدار A2 تغییر کند، نتیجه فرمول نیز متناسب با آن تغییر خواهد کرد.

اما ابزار Find & Replace معمولاً برای تغییر مستقیم داده‌های موجود در سلول‌ها استفاده می‌شود.

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

جایگزینی چند کاراکتر

فرض کنید اطلاعات ما به شکل زیر است:

A-125-Excel

و می‌خواهیم هم خط تیره‌ها را حذف کنیم:

=SUBSTITUTE(A2,"-","")

نتیجه:

A125Excel

خواهد بود.

همچنین می‌توانیم چند SUBSTITUTE را با هم ترکیب کنیم.

برای مثال:

=SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","")

در این حالت ابتدا خط تیره حذف می‌شود و سپس فاصله‌ها نیز حذف خواهند شد.

این تکنیک برای پاک‌سازی داده‌های نامنظم بسیار کاربردی است.

جایگزین کردن فقط یک مورد خاص

یکی از قابلیت‌های مهم SUBSTITUTE، آرگومان چهارم یعنی instance_num است.

فرض کنید سلول A2 شامل عبارت زیر باشد:

Excel Excel Excel

اگر بنویسیم:

=SUBSTITUTE(A2,"Excel","Word")

تمام موارد Excel با Word جایگزین می‌شوند:

Word Word Word

اما اگر فقط بخواهیم دومین Excel را تغییر دهیم:

=SUBSTITUTE(A2,"Excel","Word",2)

نتیجه:

Excel Word Excel

خواهد بود.

این قابلیت یکی از تفاوت‌های مهم SUBSTITUTE با یک جایگزینی ساده است.

حذف فقط یک مورد خاص

همین قابلیت برای حذف یک مورد مشخص نیز قابل استفاده است.

فرض کنید:

A B A B A

در سلول A2 قرار دارد.

اگر بخواهیم فقط دومین A را حذف کنیم:

=SUBSTITUTE(A2,"A","",2)

نتیجه:

A B B A

خواهد بود.

تفاوت SUBSTITUTE و REPLACE در اکسل

دو تابع SUBSTITUTE و REPLACE ممکن است در نگاه اول شبیه هم باشند، اما روش کارشان متفاوت است.

SUBSTITUTE

بر اساس متن مشخص جایگزینی انجام می‌دهد.

مثلاً:

=SUBSTITUTE(A2,"Excel","Word")

یعنی هر جا عبارت Excel پیدا شد، آن را با Word جایگزین کن.

REPLACE

بر اساس موقعیت و تعداد کاراکترها عمل می‌کند.

ساختار:

=REPLACE(old_text,start_num,num_chars,new_text)

مثلاً:

=REPLACE(A2,1,5,"Word")

یعنی از کاراکتر اول شروع کن، 5 کاراکتر را حذف کن و Word را جایگزین آن کن.

بنابراین به‌صورت ساده:

SUBSTITUTE → جایگزینی بر اساس متن

REPLACE → جایگزینی بر اساس موقعیت

استفاده از SUBSTITUTE برای اصلاح شماره تلفن

فرض کنید شماره تلفن‌ها در فایل شما به شکل‌های مختلف ثبت شده‌اند:

0912-123-4567
0912 123 4567
0912/123/4567

اگر بخواهیم خط تیره را حذف کنیم:

=SUBSTITUTE(A2,"-","")

و برای حذف فاصله:

=SUBSTITUTE(A2," ","")

می‌توانیم این عملیات را نیز ترکیب کنیم:

=SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","")

اگر علاوه بر آن / هم وجود داشته باشد:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),"/","")

با این روش می‌توانیم شکل ظاهری شماره‌ها را یکسان کنیم.

استفاده از SUBSTITUTE برای پاک‌سازی کدها

فرض کنید کد محصولات به شکل زیر ثبت شده‌اند:

PR-125-EX
PR-126-EX
PR-127-EX

اگر بخواهیم خط تیره‌ها را حذف کنیم:

=SUBSTITUTE(A2,"-","")

نتیجه:

PR125EX

خواهد بود.

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

ترکیب SUBSTITUTE با LEN

می‌توان از تابع LEN در کنار SUBSTITUTE برای بررسی تعداد یک کاراکتر خاص در یک متن استفاده کرد.

برای مثال اگر بخواهیم تعداد حروف a در یک متن انگلیسی را محاسبه کنیم:

=LEN(A2)-LEN(SUBSTITUTE(A2,"a",""))

منطق فرمول ساده است:

طول متن اصلی را با LEN محاسبه می‌کنیم.
با SUBSTITUTE تمام aها را حذف می‌کنیم.
طول متن جدید را محاسبه می‌کنیم.
اختلاف دو طول، تعداد aهای حذف‌شده را نشان می‌دهد.

این تکنیک یکی از کاربردهای جالب ترکیب توابع متنی اکسل است.

استفاده از SUBSTITUTE برای شمارش جداکننده‌ها

فرض کنید در سلول A2 عبارت زیر وجود دارد:

تهران-مشهد-اصفهان-تبریز

اگر بخواهیم تعداد خط تیره‌ها را پیدا کنیم:

=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))

نتیجه برابر با:

خواهد بود.

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

ترکیب SUBSTITUTE با TRIM

تابع TRIM برای حذف فاصله‌های اضافی کاربرد دارد.

در بعضی فایل‌ها ممکن است داده‌ها هم فاصله اضافی داشته باشند و هم یک کاراکتر نامناسب.

مثلاً می‌توانیم از ترکیب:

=TRIM(SUBSTITUTE(A2,"-"," "))

استفاده کنیم.

در این مثال ابتدا خط تیره‌ها با فاصله جایگزین می‌شوند و سپس فاصله‌های اضافی توسط TRIM مدیریت می‌شوند.

این روش در پاک‌سازی داده‌هایی که از منابع مختلف وارد Excel شده‌اند بسیار مفید است.

ترکیب SUBSTITUTE با IF

گاهی می‌خواهیم فقط در صورتی جایگزینی انجام شود که یک شرط خاص برقرار باشد.

مثلاً:

=IF(A2="","",SUBSTITUTE(A2,"قدیمی","جدید"))

اگر سلول A2 خالی باشد، خروجی نیز خالی خواهد بود؛ در غیر این صورت جایگزینی انجام می‌شود.

این روش برای ساخت فایل‌های تمیزتر و جلوگیری از نمایش خروجی‌های غیرضروری مفید است.

مثال کاربردی؛ اصلاح نام شرکت

فرض کنید در یک فایل قدیمی، نام یک شرکت به‌صورت:

شرکت اشکاوند

ثبت شده و قرار است در گزارش جدید با نام:

انتشارات اشکاوند

نمایش داده شود.

می‌توان نوشت:

=SUBSTITUTE(A2,"شرکت اشکاوند","انتشارات اشکاوند")

اگر متن سلول:

فروش به شرکت اشکاوند

باشد، نتیجه:

فروش به انتشارات اشکاوند

خواهد بود.

این قابلیت برای اصلاح نام‌ها و استانداردسازی داده‌ها بسیار کاربردی است.

مثال کاربردی؛ تغییر جداکننده اطلاعات

فرض کنید اطلاعات یک سلول به شکل زیر است:

Excel-Power BI-Word

اما می‌خواهیم جداکننده‌ها به شکل / باشند.

فرمول:

=SUBSTITUTE(A2,"-","/")

نتیجه:

Excel/Power BI/Word

خواهد بود.

نکته مهم درباره SUBSTITUTE و حروف بزرگ و کوچک

در مقایسه‌های متنی، SUBSTITUTE به‌صورت پیش‌فرض نسبت به بزرگ یا کوچک بودن حروف انگلیسی حساس نیست.

بنابراین هنگام کار با متن انگلیسی، بهتر است این موضوع را در طراحی فرمول‌های خود در نظر بگیرید.

برای داده‌های فارسی نیز معمولاً موضوعاتی مانند شکل متفاوت حروف، فاصله و نیم‌فاصله می‌توانند باعث شوند دو متن از نظر ظاهری مشابه به نظر برسند اما از نظر مقدار واقعی یکسان نباشند.

چند مثال سریع از تابع SUBSTITUTE

جایگزینی یک کلمه

=SUBSTITUTE(A2,"Excel","Word")

حذف یک کلمه

=SUBSTITUTE(A2,"Excel","")

جایگزینی خط تیره با اسلش

=SUBSTITUTE(A2,"-","/")

حذف فاصله

=SUBSTITUTE(A2," ","")

جایگزینی فقط دومین مورد

=SUBSTITUTE(A2,"Excel","Word",2)

شمارش تعداد یک کاراکتر

=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))

جمع‌بندی

تابع SUBSTITUTE یکی از توابع بسیار کاربردی اکسل برای جایگزینی، اصلاح و پاک‌سازی داده‌های متنی است.

ساختار اصلی آن:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

است.

اگر بخواهیم یک متن مشخص را با متن دیگری جایگزین کنیم، SUBSTITUTE انتخاب مناسبی است. همچنین می‌توانیم با قرار دادن مقدار خالی در new_text، بخشی از متن را حذف کنیم.

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

جایگزینی کلمات
حذف کاراکترها
اصلاح شماره‌ها
یکسان‌سازی کدها
پاک‌سازی داده‌ها
تغییر جداکننده‌ها
اصلاح نام‌ها
شمارش تعداد یک کاراکتر
آماده‌سازی داده برای تحلیل

استفاده کرد.

اگر SUBSTITUTE را با توابعی مانند LEN، TRIM، IF و سایر توابع متنی ترکیب کنیم، می‌توانیم فرمول‌های بسیار قدرتمندی برای پاک‌سازی و پردازش داده‌های Excel ایجاد کنیم.