تابع substitute در اکسل

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

نحوه نوشتن تابع

شکل کلی تابع SUBSTITUTE به صورت زیر است:

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

توضیح پارامترها:

text: سلول یا رشته‌ای که قرار است در آن جایگزینی انجام شود.
old_text: متن یا کاراکتری که باید جایگزین شود.
new_text: متن جایگزین.
instance_num (اختیاری): عددی که مشخص می‌کند کدام تکرار old_text باید جایگزین شود؛ اگر حذف شود، همه تکرارها جایگزین می‌شوند.

ویژگی‌ها و نکات کلیدی

تابع SUBSTITUTE حساس به حروف بزرگ و کوچک است (case-sensitive).
اگر متن مورد نظر یافت نشود، مقدار اصلی بدون تغییر بازگردانده می‌شود.
برای جایگزینی تنها یک تکرار (مثلاً دومین یا آخرین تکرار) می‌توان از پارامتر instance_num استفاده کرد یا ترکیب‌هایی برای محاسبه شماره تکرار به‌کار برد.
برای انجام جایگزینی‌ها متعدد می‌توان SUBSTITUTE را تو در تو (chained) استفاده کرد یا در اکسل 365 از توابع پیشرفته مانند LET و REDUCE بهره برد.

مثال‌های پایه‌ای

=SUBSTITUTE("apple, banana, apple", "apple", "orange")

این فرمول هر دو واژه “apple” را با “orange” جایگزین می‌کند و خروجی “orange, banana, orange” خواهد بود.

=SUBSTITUTE("a,b,c,b", "b", "B", 2)

در این مثال فقط دومین تکرار “b” با “B” جایگزین می‌شود و خروجی “a,b,c,B” خواهد بود.

مثال عملی: حذف فاصله غیرقابل‌دید (non-breaking space)

گاهی داده‌ها حاوی کاراکتر غیرقابل‌دید CHAR(160) (در HTML ) هستند که TRIM آنها را حذف نمی‌کند. برای تبدیل به فاصله معمولی یا حذف کامل:

=TRIM(SUBSTITUTE(A1, CHAR(160), " "))

این ترکیب تمام کاراکترهای CHAR(160) را با فاصله معمولی جایگزین می‌کند و سپس با TRIM فاصله‌های اضافی ابتدا/انتها را برمی‌دارد.

جایگزینی آخرین تکرار یک زیررشته

برای جایگزینی یا حذف آخرین تکرار یک کاراکتر (مثلاً حذف آخرین ویرگول)، می‌توان از شمارش تکرارها استفاده کرد:

=SUBSTITUTE(A1, ",", ";", LEN(A1)-LEN(SUBSTITUTE(A1, ",", "")))

اینجا LEN(A1)-LEN(SUBSTITUTE(A1, “,”, “”)) تعداد ویرگول‌ها را محاسبه می‌کند و SUBSTITUTE تنها همان تکرار آخر را جایگزین می‌کند.

چند مثال کاربردی در کار با داده‌ها

تبدیل فرمت تاریخ/ساعت متنی: حذف کلمه ثابت مثل “UTC” یا “(GMT)” با SUBSTITUTE قبل از تبدیل به تاریخ.
پاک‌سازی ارقام: حذف کاما از اعداد متنی و سپس تبدیل به عدد:

=VALUE(SUBSTITUTE(A1, ",", ""))

این فرمول کاماها را حذف و سپس رشته را به عدد تبدیل می‌کند تا بتوان محاسبات ریاضی انجام داد.

استفاده ترکیبی با توابع دیگر

در پروژه‌های واقعی معمولاً از SUBSTITUTE همراه با توابعی مثل TRIM، VALUE، LEFT, RIGHT، FIND، LEN و TEXTJOIN استفاده می‌شود تا عملیات پیچیده‌تری انجام شود.

=TRIM(SUBSTITUTE(VALUE(SUBSTITUTE(A1, ".", "")), ",", "."))

مثال بالا یک سناریوی بین‌المللی را نشان می‌دهد: ابتدا نقطه‌ها حذف می‌شوند، سپس کاما به نقطه تبدیل و مقدار عددی نهایی تمیز می‌شود (نمونه‌ای از تبدیل فرمت عدد در سیستم‌هایی با جداکننده‌های متفاوت).

جلوگیری از مشکلات رایج

حساسیت به حروف: اگر نیاز به جایگزینی بدون توجه به حروف بزرگ/کوچک دارید، متن‌ها را با UPPER یا LOWER تبدیل و سپس SUBSTITUTE را اجرا کنید:
```
=SUBSTITUTE(UPPER(A1), UPPER("old"), "new")
```
توجه: در این حالت باید متن خروجی نیز مطابق با حالت UPPER باشد یا مجدداً ساختار اصلی را بازیابی کنید.
وقتی می‌خواهید چند متن مختلف را هم‌زمان جایگزین کنید، می‌توانید توابع SUBSTITUTE را داخل هم بنویسید یا در اکسل 365 از REDUCE/LET/LAMBDA برای خوانایی بهتر استفاده کنید.

مثال پیشرفته با LET (Excel 365)

=LET(
  txt, A1,
  findList, {"cat","dog","mouse"},
  replaceList, {"گربه","سگ","موش"},
  REDUCE(txt, SEQUENCE(ROWS(findList)),
    LAMBDA(acc, i, SUBSTITUTE(acc, INDEX(findList, i), INDEX(replaceList, i)))
  )
)

این فرمول با استفاده از LET و REDUCE چندین جایگزینی را به صورت قابل‌خواناتر انجام می‌دهد؛ ابتدا آرایه‌ای از عبارات جستجو و آرایه‌ای از جایگزین‌ها تعریف می‌شود و سپس REDUCE به ترتیب هر مورد را جایگزین می‌کند. توجه: این مثال برای اکسل 365/2021 است که توابع آرایه‌ای پیشرفته را پشتیبانی می‌کند.

جدول نمونه: ورودی‌ها و خروجی‌ها

ورودی (A1)	فرمول	خروجی
apple, banana, apple	`=SUBSTITUTE(A1,"apple","orange")`	orange, banana, orange
a b c	`=TRIM(SUBSTITUTE(A1,CHAR(160)," "))`	a b c
1,234,567	`=VALUE(SUBSTITUTE(A1,",",""))`	1234567 (عدد)
one,two,three,two	`=SUBSTITUTE(A1,"two","2",2)`	one,two,three,2

خلاصه و پیشنهادهای عملی

برای پاک‌سازی داده‌ها و تغییر ساختار متن‌ها، SUBSTITUTE ابزار ساده و قدرتمندی است.
در مواجهه با کاراکترهای خاص از CHAR استفاده کنید (مثلاً CHAR(160)).
برای جایگزینی‌های پیچیده‌تر از ترکیب SUBSTITUTE با سایر توابع یا قابلیت‌های جدید اکسل (LET، REDUCE، LAMBDA) بهره ببرید.
همیشه به حساسیت به حروف توجه کنید و در صورت نیاز متن‌ها را Normalize (UPPER/LOWER) کنید.

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

آیا این مطلب برای شما مفید بود ؟

خیر

بله