تابع REPLACE در اکسل برای جایگزین کردن بخشی از متن + مثال

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

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

فرمول کلی تابع REPLACE در اکسل چهار آرگومان اجباری به شرح زیر دارد:

=REPLACE(old_text, start_num, num_chars, new_text)

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

• Old_text : متن اصلی یا آدرس سلول در جدول که می‌خواهیم با چند کاراکتر جایگزین کنیم.
• Start_num : موقعیت اولین کاراکتر در متن اصلی که می‌خواهیم از آنجا با یک یا چند کاراکتر جایگزین کنیم.
• Num_chars : تعداد کاراکترهایی که می‌خواهیم جایگزین کنیم.
• New_text : متنی که جایگزین متن اصلی است.

در این فرمول اگر آرگومان‌های Start_num و Num_chars را عدد منفی یا مقداری با فرمت غیر عدد تعریف کنیم، با پیام خطای #VALUE! روبرو می‌شویم. از آنجا کاربرد درست تابع REPLACE در اکسل نیاز به رعایت ترفندهایی خاص دارد، یادگیری اصول فرمول‌نویسی ضروری است. ضمن اینکه در سطح پیشرفته لازم است مهارت کافی در فرمول‌نویسی‌ پیچیده و ترکیبی تابع REPLACE با سایر توابع اکسل را نیز داشته باشیم. بنابراین پیشنهاد می‌کنیم در کنار مطالعه ادامه مطلب، فیلم آموزش استفاده از توابع و فرمول‌نویسی اکسل در فرادرس را مشاهده کنید.

مثال فرمول نویسی ساده با تابع REPLACE در اکسل

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

1. جایگزین کردن یک کاراکتر از متن
2. جایگزین کردن چند کاراکتر
3. جایگزین کردن اولین حرف متن در یک ستون
4. حذف بخش اول متن در یک ستون
5. جایگزین کردن اعداد در یک متن

در ادامه، در مورد هریک توضیح می‌دهیم. پیش از آن، پیشنهاد می‌کنیم برای آشنایی با فرایند کلی جستجو و جایگزینی در اکسل، ویدیوی آموزشی رایگان زیر را از فرادرس تماشا کنید:

۱. جایگزین کردن یک کاراکتر از متن

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

همان‌طور که مشخص است کلمه «مغیمی» اشتباه املایی دارد. برای ویرایش آن یکی از دو فرمول زیر را می‌نویسیم.

=REPLACE("ق",8,1,"ساناز مغیمی")

=REPLACE(A3,8,1,"ق")

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

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

• اولین آرگومان متن اصلی است که می‌خواهیم آن‌ را ویرایش کنیم.
• حرف «غ» هشتمین کاراکتر از کلمه «ساناز مغیمی» است. بنابراین عدد «۸» را به‌عنوان دومین آرگومان می‌نویسیم. البته لازم است توجه کنیم که هر فاصله در فرمول به‌عنوان یک کاراکتر حساب می‌شود.
• از آنجا که فقط حرف «غ» را باید جایگزین کنیم، سومین آرگومان یا همان تعداد کاراکتر مورد نظر برای جایگزینی، عدد «۱» است.
• آخرین آرگومان نیز متن مورد نظر برای جایگزینی است که به شکل "ق" در فرمول می‌نویسیم.

بنابراین نتیجه نهایی به شکل زیر خواهد بود.

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

فرض می‌کنیم در مثال قبل، اسم «ساناز محمدی» به اشتباه «ساناز مغیمی» نوشته شده باشد. بنابراین لازم است سه کاراکتر را جایگزین کنیم. در این حالت آرگومان سوم و چهارم از فرمول را به ترتیب به عدد «۳» و "حمد" تغییر می‌دهیم. بنابراین نتیجه نهایی به شکل تصویر زیر درمی‌آید.

۳. جایگزین کردن اولین حرف متن در یک ستون

این حالت نمونه خوبی برای آشنایی بیشتر با یک حالت کاربردی استفاده از تابع REPLACE در اکسل است. فرض می‌کنیم در ستون A نشانی محل ذخیره چند فایل در کامپیوتر را مانند تصویر زیر داریم.

به‌دلیل جابجایی فایل‌ها، در حال حاضر همه فایل‌ها به درایو «F» منتقل شده‌اند. برای اصلاح نشانی‌ها در جدول اکسل لازم است حرف اول همه سلول‌ها در ستون A را تغییر دهیم. بنابراین در سلول B2 فرمول زیر را می‌نویسیم.

=REPLACE(A2,1,1,"F")

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

نتیجه نهایی به شکل زیر خواهد بود.

۴. حذف بخش اول متن در یک ستون

در جدول فرضی زیر می‌خواهیم سه حرف اول و علامت نقطه از هر نشانی اینترنتی، یعنی عبارت «.www» را حذف کنیم.

برای این کار ابتدا در سلول B2 فرمول زیر را می‌نویسیم.

=REPLACE(A2,1,4," ")

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

بنابراین نتیجه نهایی به شکل زیر درمی‌آید.

فیلم آموزش جست‌وجو و جایگزینی متن در اکسل در فرادرس

کلیک کنید

۵. جایگزین کردن اعداد در متن

تابع REPLACE در اکسل برای جایگزین کردن اعداد در یک رشته متنی هم کاربرد دارد. فرض می‌کنیم در جدول زیر می‌خواهیم اعداد میانه کدهای محصول در ستون A را با عدد «۱۴۰۵» جایگزین کنیم.

در این حالت اولین کاراکتر برای جایگزینی در موقعیت پنجم از رشته متنی قرار دارد. تعداد کاراکترهای قابل جایگزینی هم چهار عدد است. بنابراین فرمول زیر را در سلول B2 می‌نویسیم.

=REPLACE(A2,5,4,"1405")

بعد از کپی کردن فرمول در بقیه ردیف‌ها، نتیجه به شکل زیر درمی‌آید.

یادگیری فرمول‌نویسی مقدماتی تا پیشرفته اکسل همراه فرادرس

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

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

• فیلم آموزش استفاده از توابع و فرمول‌نویسی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش محاسبات آماری در اکسل همراه گواهینامه در فرادرس
• فیلم آموزش مدل‌سازی مالی در اکسل همراه گواهینامه در فرادرس

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

• مجموعه فیلم آموزش توابع و فرمول‌ در اکسل در فرادرس
• مجموعه فیلم آموزش اکسل مقدماتی تا پیشرفته در فرادرس

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

مثال فرمول نویسی ترکیبی با تابع REPLACE در اکسل

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

فیلم آموزش توابع و فرمول‌نویسی در Excel در فرادرس

کلیک کنید

1. جایگزین کردن تاریخ
2. تابع REPLACE تودرتو
3. بزرگ کردن اولین حرف انگلیسی
4. پوشاندن بخشی از متن
5. حذف شرطی بخشی از متن
6. جایگزین کردن بخشی از متن با موقعیت متفاوت کاراکتر در هر سلول

۱. جایگزین کردن تاریخ

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

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

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

=REPLACE(TEXT(A2, "dd-mmm-yy"), 4, 3, "Nov")

بنابراین نتیجه به شکل زیر درمی‌آید.

البته تبدیل تاریخ به متن با تابع TEXT زمانی مناسب است که نخواهیم از آن برای محاسبات استفاده کنیم. در غیر این صورت برای محاسبات بعدی با استفاده از تابع DATEVALUE بار دیگر تاریخ‌ها را به فرمت اصلی خود برمی‌گردانیم. فرمول نهایی در این حالت به شکل زیر است.

=DATEVALUE(REPLACE(TEXT(A2, "dd-mmm-yy"), 4, 3, "Nov"))

در مطلب زیر روش کار با تابع DATEVALUE را به‌طور کامل توضیح داده‌ایم.

۲. تابع REPLACE تودرتو

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

فیلم آموزش جست‌وجو و جایگزینی متن در اکسل در فرادرس

کلیک کنید

به‌عنوان مثال جدولی از کد چند کالا مانند تصویر زیر داریم.

اگر بخواهیم سه کاراکتر اول را با عبارت «PRM» و چهار کاراکتر میانی را با عدد «۱۴۰۵» جایگزین کنیم، فرمول زیر را در سلول B2 می‌نویسیم.

=REPLACE(REPLACE(A2,1,3,"PRM"),5,4,"1405")

در این فرمول تابع REPLACE داخلی، سه کاراکتر اول را تغییر می‌دهد. سپس دومین تابع چهار کاراکتر از موقعیت پنجم در رشته متنی را با «1405» جایگزین می‌کند.

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

کاربرد دیگر تابع REPLACE تودرتو جدا کردن ارقام یک عدد با علامت «/» است. به‌عنوان مثال، جدولی شامل چند تاریخ پشت سرهم مانند تصویر زیر داریم.

اگر بخواهیم این اعداد را مانند فرمت تاریخ به ترتیب با علامت «/» جدا کنیم،‌فرمول زیر را در سلول B2 می‌نویسیم.

=REPLACE(REPLACE(A2,5,0,"/"),8,0,"/")

در این فرمول بعد از موقعیت پنجم و هشتم متن، علامت «/» قرار می‌گیرد.

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

۳. بزرگ کردن اولین حرف انگلیسی

برای بزرگ کردن اولین حرف انگلیسی در عبارت‌های متنی اکسل از تابع UPPER و تابع LEFT همراه با تابع REPLACE استفاده می‌کنیم.

به‌عنوان مثال در جدول فرضی زیر اسامی افراد یک کلاس را به زبان انگلیسی داریم.

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

=REPLACE(A2,1,1,UPPER(LEFT(A2)))

در این فرمول ابتدا تابع LEFT اولین حرف انگلیسی که در سمت چپ قرار دارد را مشخص می‌کند. سپس تابع UPPER آن را به حرف بزرگ تبدیل می‌کند. در نهایت تابع REPLACE خروجی را جایگزین اولین حرف در اولین موقعیت رشته متنی می‌کند.

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

۴. پوشاندن بخشی از متن

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

اگر بخواهیم فقط چهار حرف آخر این شماره‌ها مشخص باشند، تابع REPLACE را همراه با تابع REPT و تابع LEN استفاده می‌کنیم. برای این کار فرمول زیر را در سلول B2 می‌نویسیم.

=REPLACE(A2,1,LEN(A2)-4,REPT("*",LEN(A2)-4))

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

• ابتدا LEN(A2) تعداد کاراکترهای سلول A2 را می‌شمارد.
• بخش LEN(A2)-4 چهار کاراکتر آخر را جدا می‌کند و باقی‌مانده آن از کل کاراکترهای شمارش شده را به‌عنوان خروجی نشان می‌دهد.
• REPT("*",LEN(A2)-4) به تعداد خروجی مرحله قبل با تابع REPT علامت «*» تولید می‌کند.
• تابع REPLACE از اولین کاراکتر در سلول A2 شروع می‌کند و به تعداد ستاره تولید شده در مرحله قبل، کاراکترها را جایگزین می‌کند.

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

در مطلب زیر از مجله فرادرس نکات فرمول‌نویسی با تابع REPT را توضیح داده‌ایم که پیشنهاد می‌کنیم برای تکمیل بحث آن را مطالعه کنید:

۵. حذف شرطی بخشی از متن

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

فیلم آموزش جستجو و جایگزینی در اکسل در فرادرس

کلیک کنید

برای درک بهتر روش کار جدول فرضی زیر را در نظر می‌گیریم.

همان‌طور که مشخص است، در این جدول همه وب‌سایت‌ها کاراکتر «.www» را ندارند. بنابراین برای حذف این عبارت از همه متن‌ها نمی‌توانیم از فرمول ساده REPLACE استفاده کنیم. در این حالت با استفاده از تابع IF و تابع LEFT فقط متن‌هایی را برای حذف کاراکترهای اول به تابع REPLACE معرفی می‌کنیم که شامل عبارت «.www» باشند.

فرمول ترکیبی مورد استفاده به شرح زیر است.

=IF(LEFT(A2,4)="www.",REPLACE(A2,1,4,""),A2)

در این فرمول ابتدا تابع LEFT چهار کاراکتر اول هر رشته متنی در سلول را جدا می‌کند. در صورتی‌که این کاراکترها برابر با «.www» بودند، تابع REPLACE آن چهار کاراکتر را با جای خالی جایگزین می‌کند. در غیر این صورت همان عبارت داخل سلول به‌عنوان خروجی نشان داده می‌شود.

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

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

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

فرض می‌کنیم جدولی از ایمیل مشتریان را مانند تصویر زیر داریم.

اما بعد از مدتی ایمیل تعدادی از افراد تغییر کرده است. به شکلی که در حال حاضر همه ایمیل‌های prk.com باید به klm.com تغییر پیدا کند. مشکل اصلی برای بروزرسانی ایمیل‌ها این است که اسامی افراد در هر ایمیل طول متفاوتی دارد. بنابراین نمی‌توانیم مقدار یکسانی برای آرگومان start_num تعریف کنیم. برای رفع این مشکل از تابع FIND استفاده می‌کنیم. به این شکل که ابتدا با این تابع موقعیت اولین کاراکتر برای جایگزینی با تابع REPLACE را پیدا می‌کنیم. سپس خروجی آن را برای جایگزین کردن به تابع اعلام می‌کنیم. به این شکل برای جدول مورد نظر، فرمول زیر را می‌نویسیم.

=REPLACE(A2, FIND("@prk",A2), 4, "@klm")

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

همان‌طور که می‌بینیم، موضوع جایگزینی ایمیل‌ها حل شده است. اما خروجی فرمول برای دیگر ایمیل‌ها به شکل پیام خطای #VALUE!  نمایش داده می‌شود. زیرا تابع FIND در این سلول‌ها هیچ عبارتی با نام «prk» پیدا نمی‌کند. برای رفع این مشکل از تابع IFERROR استفاده می‌کنیم. این تابع در صورت پیدا نشدن عبارت، به‌جای خروجی پیام خطا ایمیل اصلی را برمی‌گرداند.

بنابراین فرمول نهایی به شکل زیر خواهد بود.

=IFERROR(REPLACE(A2, FIND("@prk",A2), 4, "@klm"),A2)

که نتیجه آن به شکل تصویر زیر است.

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

نکات مهم در فرمول نویسی با تابع REPLACE در اکسل

برای جلوگیری از ایجاد خطا در زمان فرمول‌نویسی با تابع REPLACE در اکسل لازم است به چند نکته زیر توجه کنیم.

فیلم آموزش توابع و فرمول‌نویسی در Excel در فرادرس

کلیک کنید

۱. اگر آرگومان start_num و num_chars را به اشتباه، مقدار غیر عددی یا منفی تعریف کنیم، پیام خطای ‎#VALUE!‎ را می‌بینیم. در این حالت لازم است هر آرگومان را بار دیگر بررسی کنیم.

۲. تابع REPLACE فقط برای کار با رشته‌های متنی تعریف شده است. بنابراین اگر فرمت سلول عدد یا تاریخ باشد، نتیجه درستی از فرمول‌نویسی نمی‌گیریم. رفع این مشکل فقط در زمانی امکان دارد که قصد انجام محاسبه با عدد یا تاریخ را نداشته باشیم. در این حالت با استفاده از ابزار Text to Column فرمت سلول را طبق مراحل زیر به حالت متنی تبدیل می‌کنیم.

• بعد از انتخاب سلول مورد نظر از زبانه «Data» روی بخش «Text to Column» کلیک می‌کنیم.

• در پنجره باز شده روی دکمه «Delimited» می‌زنیم و با کلیک روی دکمه «Next» مراحل بعدی را ادامه می‌دهیم.

• در سومین مرحله با توجه به نوع داده فرمت را روی «Text» یا «Date» می‌گذاریم. سپس دکمه «Finish» را می‌زنیم.

برای یادگیری سایر کاربردهای این ابزار پیشنهاد می‌کنیم فیلم آموزش رایگان ابزار Text to column در اکسل در فرادرس را مشاهده کنید.

جمع‌بندی پایانی

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