تابع Replace در اکسل + فایل دانلودی و چند مثال

۳/۵ - (۱ امتیاز)

فهرست

۱ جایگزینی اطلاعات در سلول با استفاده از توابع
۲ چند مثال از تابع Replace
۳ دانلود فایل آموزش تابع Replace
۴ ثبت ایمیل جهت دانلود فایل

جایگزینی اطلاعات در سلول با استفاده از توابع

توابع متنی در اکسل کاربردهای زیادی دارن. با استفاده از توابع متنی میتونیم در یک رشته متنی یا عددی، تغییراتی ایجاد کنیم. مثلا قسمت هایی رو جدا کنیم، قسمت هایی رو با عبارات جدید جایگزین کنیم و …. در این دسته از توابع دو تابع برای جایگزین کردن وجود داره. تابع Substitute و تابع Replace. این دو تابع هر دو کار جایگزینی رو انجام میدن. یعنی قسمت هایی از یک رشته متنی رو با موارد جدید جایگزین میکنند. تابع Substitute رو مفصل در مقاله تابع Substitute در اکسل توضیح داده ایم. در این مقاله راجع به تابع Replace و مثال هایی در این خصوص صحبت خواهیم کرد:

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

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

=REPLACE(old_text, start_num, num_chars, new_text)

Old_Text: سلول یا رشته ای که میخوایم تغییرات در اون ایجاد کنیم.

Start_Num: این آرگومان از جنس عدد هست و مکان شروع تغییر رو نشون میده. مثلا عدد ۲، نشون دهنده این هست که از دومین کاراکتر شروع کنیم برای ایجاد تغییر.

Num_Chars: این آرگومان نیز از جنس عدد هست و تعداد کاراکتری که میخواهیم حذف بشه رو نشون میده. مثلا عدد ۴ به این معنی هست که از دومین کاراکتر، چهار کاراکتر رو حذف کن. یعنی کاراکتر ۲، ۳، ۴ و ۵.

New_Text: عبارت جدیدی (با هر تعداد کاراکتر) که میخواهیم جایگزین اون کارکترهای قدیمی بشه رو می نویسیم.

چند مثال از تابع Replace

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

مثال اول: جایگزینی کارکتر با یک عبارت

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

=REPLACE(A1,3,1,”DD”)

شکل ۱- تابع Replace – جایگزینی یک کاراکتر با یک عبارت جدید

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

نکته:
خروجی تابع Replace از جنس متن هست یعنی قابلیت محاسبات نداره. پس اگر میخوایم عددی رو با یک عدد دیگه جایگزین میکنیم و برای این کار از تابع Replace استفاده میکنیم، حتما باید خروجی رو به عدد تبدیل کنیم که یکی از راه های تبدیل متن به عدد تابع Value هست. یعنی خروجی تابع Replace در یک Value قرار میگیره.

Value (Replace (…))

مثال دوم: اضافه کردن کارکتر به متن

فرض کنید یک سری کد ملی داریم که میخواهیم دو تا – بین کاراکترها قرار بدیم. از طرفی مطمئنیم که کد ملی ده رقم هست و از طرفی میخواهیم بین کارکتر ۳ و ۴ و بین کاراکتر ۹ و ۱۰ یک – قرار بدیم و کد ها رو بصورت الگوی اصلی کد ملی تبدیل کنیم.

برای حل این مسئله راه حل های متفاوتی وجود داره. در اینجا میخوایم از تابع Replace برای اینکار استفاده کنیم. برای حل این مسئله باید طی دو مرحله از تابع Replace استفاده کنیم.

گام اول: باید بین کاراکتر سوم و چهارم یک – اضافه کنیم. یعنی از کاراکتر چهارم، هیچ کاراکتری رو حذف نکنیم و فقط – رو اضافه کنیم. یعنی:

=REPLACE( A1 , 4 , 0 , “-“)

شکل ۲ – تابع Replace- اضافه کردن کاراکتر اول

گام دوم: حالا باید – دوم رو به خروجی فرمول اول اضافه کنیم. یعنی فرمول اول میشه ورودی فرمول دوم:

=REPLACE ( REPLACE(A1,4,0,”-“) ,۱۱ , ۰ , “-“)

شکل ۳- تابع Replace – اضافه کردن کاراکتر دوم

این فرمول میاد رشته ای که اولین – رو داره به عنوان ورودی در نظر میگیره که الان ۱۱ رقمی هست و بعد از یازدهمین کاراکتر هیچ چیزی رو حذف نمیکنه و – دوم رو اضافه میکنه.

مثال سوم: جابجایی کارکترهایی که مکان متغیر دارند

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

شکل ۴ – تابع Replace – اصلاح آدرسهای ایمیل

برای اینطور مسائل چه باید بکنیم؟

همونطور که قبلا هم گفتیم، برای استفاده از توابع متنی باید بتونیم الگوی مناسبی از داده ها پیدا کنیم. در داده های نمای شداده شده در شکل ۴، مشخصه که عبارت PDC در بعضی از آدرس های ایمیل هم وچود داره. که ما اونو نمیخوایم. پس الگویی که میتونیم بین داده ها پیدا کنیم اینه که، دنبال عبارت PDC بعد از @ باشیم. پس برای اینکه مکان PDC بعد از @ رو پیدا کنیم از تابع Search/Find استفاده میکنیم (از Search استفاده میکنیم چون میدونیم به حروف بزرگ و کوچک حساسیتی نداره).

=SEARCH( “pdc” , A1 , SEARCH (“@”,A1) +1)

برای اینکه جستجو از بعد از @ شروع بشه، از آرگومان آخر استفاده میکنیم و در واقع با یک Search دیگه به تابع میگیم که از @ به بعد شروع به جستجو کن.

شکل ۵- تابع Replace- پیدا کردن عبارت مورد نظر با استفاده از تابع Search

همونطور که در شکل ۵ نمایش داده شده، مکان حرف P از کلمه PDC بعد از @ در هر سلول مشخص شده و آدرس هایی که PDC ندارند، با خطا مواجه شده.

حالا باید PDC رو به PDCA تبدیل کنیم. پس از تابع Replace به شکل زیر استفاده میکنیم:

=REPLACE (A1 , B1 , 3 , “PDCA”)

در این فرمول، سلول B1 برابر است با مکان شروع p از عبارت PDC بعد از @ در هر آدرس ایمیل. از اونجا، سه کاراکتر رو حذف میکنیم و بجاش PDCA رو قرار میدیم.

شکل ۶- تابع Replace – پیدا کردن یک عبارت خاص و جایگزین کردن با عبارت جدید

حالا باید با یک Iferror خطاهای ناشی از فرمول نویسی رو مدیریت کنیم. پس فرمول رو به شرح زیر تغییر میدیم:

=IFERROR(C1 , A1)

شکل ۷ – تابع Replace – مدیریت خطای فرمول نویسی

پس بصورت کلی، مکان عبارت PDC بعد از @ رو پیدا کردیم و بعد با تابع Replace با عبارت PDCA جایگزین کردیم و در نهایت هم با Iferror، خطاها رو مدیریت کردیم.

در آخر هم همه این فرمول ها میتونه در یک سلول و بدون سلول کمکی نوشته بشه. یعنی:

=IFERROR ( REPLACE ( A1 , SEARCH ( “pdc”, A1 , SEARCH(“@”,A1) +1 ) , ۳ , “PDCA”) , A1)

در این مقاله کاربردهای تابع Replace رو دیدیم. برای درک بهترِ این مقاله، پیشنهاد میکنیم مقالات مربوط به Substitute, find/Search و مدیریت خطا رو هم مطالعه کنید.