جداسازی متن در اکسل – آموزش تصویری و کامل

۱۱۳۵۹ بازدید
آخرین به‌روزرسانی: ۱۰ دی ۱۴۰۱
زمان مطالعه: ۵ دقیقه
جداسازی متن در اکسل – آموزش تصویری و کامل

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

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

تابع TEXTBEFOR برای جداسازی متن در اکسل

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

1TEXTBEFORE(text, delimiter, instance, match_mode, match_end, if_not_found)

دو آرگومان اول این تابع، ضروری هستند. text می‌تواند یک متن باشد یا ارجاع به سلولی که متنی درون آن قرار دارد. delimiter نیز نقطه‌ای است که می‌خواهیم متن قبل از آن را استخراج کنیم. باقی آرگومان‌ها نیز اختیاری بوده و به شکل زیر هستند:‌

  • instance : اگر بیش از یک مورد از نقطه جداکننده وجود داشت و ما مورد خاصی را می‌خواستیم، از این آرگومان استفاده خواهیم کرد.
  • match_mode : این آرگومان دو حالتی بوده و دو عدد «0» و «1» را می‌پذیرد. عدد «0» برای اینکه فرمول به حروف بزرگ و کوچک انگلیسی متن حساس باشد و عدد «1» برای اینکه حساسیتی وجود نداشته باشد. حالت پیش فرض روی عدد «0» است.
  • match_end : این آرگومان نیز دو حالتی است. عدد «0» برای شرایطی که نقطه جداکننده با انتهای متن مطابقت نداشته باشد و عدد «1» در شرایطی که نقطه جداکننده مطابقت داشته باشد. حالت پیش فرض فرمول، عدد «1» است.
  • if_not_found : اگر مقدار وارد شده در فرمول، یافت نشد، از این آرگومان استفاده می‌کنیم تا نتیجه خاصی را به جای خطا، نشان دهد.

در ادامه با بررسی مثال‌های مختلف، بیشتر با کارکرد هر آرگومان آشنا خواهیم شد. ابتدا تابع TEXTBEFORE را در مثالی ساده به کار می‌گیریم.

در این مثال، می‌خواهیم در متن سلول A2، تمام نوشته‌های پیش از عبارت «را» استخراج و درون سلول A5 نشان داده شوند. برای این کار کافی است فرمول زیر را در سلول A5 وارد کنیم.

1=TEXTBEFORE(A2,"را")
استفاده از تابع TEXTBEFOR FVHD برای جداسازی متن در اکسل

در ادامه کمی فرمول را گسترده‌تر کرده و از آن برای نمایش کل نوشته‌های پیش از دومین عبارت «متن» استفاده می‌کنیم. در این فرمول می‌توانید عدد «2» را با عدد مد نظر خود پر کنید. مثلا برای پنجمین عبارت، عدد «5» را قرار دهید. همچنین به جای «Text»، عبارت «متن» را قرا دهید.

1=TEXTBEFOR(A2,"Text",2)
استخراج متن بعد از نقطه جدا کننده

در فرمول بعد، آرگومان match_mode را وارد می‌کنیم تا نسبت به حروف بزرگ و کوچک حساس باشد (این ویژگی برای متون انگلیسی و لاتین کاربرد دارد). دقت کنید که جای سومین آرگومان در این فرمول خالی مانده است (قسمت , ,) و همچنین به جای عبارت «Text» می‌نویسیم «متن».

1=TEXTBEFORE(A2,"Text",,0)
TEXTBEFOR حساس به حروف

تابع TEXTAFTER برای جداسازی متن در اکسل

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

1TEXTAFTER(text, delimiter, instance, match_mode, match_end, if_not_found).

مانند تابع قبلی، وارد کردن دو آرگومان اول تابع TEXTAFTER نیز ضروری است. به همین شکل آرگومان text می‌تواند خود متن یا ارجاع به سلول حاوی متن باشد. آرگومان delimiter نیز برای وارد کردن نقطه جداکننده است.

به غیر از این‌ها، سه آرگومان اختیاری دیگر نیز در فرمول تابع TEXTAFTER هستند که درست مانند TEXTBEFORE عمل می‌کنند. در ادامه به کمک چند مثال، این تابع را بیشتر خواهیم شناخت.

در اولین مثال، می‌خواهیم تمام نوشته‌های پس از عبارت «از» را در سلول A5 نشان دهیم. برای این کار از فرمول زیر استفاده می‌کنیم:

1=TEXTAFTER(A2,"از")
استفاده از تابع TEXTAFTER برای جداسازی متن در اکسل

مثال بعدی تمام نوشته‌ها را پس از دومین عبارت «متن» نشان خواهد داد. توجه داشته باشید که می‌توانید عدد «2» را بر حسب نیاز خود تغییر دهید و باید به جای «Text» بنویسید «متن».

1=TEXTAFTER(A2,"text",2)
استخراج متن از دومین نقطه جدا کننده

و در نهایت از آرگومان match_mode برای فعال سازی حساسیت به حروف کوچک و بزرگ در فرمول استفاده می‌کنیم (این آرگومان برای زبان‌های انگلیسی و لاتین کاربرد دارد).

1=TEXTAFTER(A2,"Text",,0)
TEXTAFTER حساس به حروف

تابع TEXTSPLIT برای جداسازی متن در اکسل

به کمک تابع TEXTSPLIT می‌توانیم متن را بر اساس یک نقطه جداکننده مانند فاصله (Space)، تکه‌تکه و در ستون یا ردیف‌های مختلف پخش کنیم. فرمول این تابع به شکل زیر است:

1TEXTSPLIT(text, column_delimiter, row_delimiter, ignore, match_mode, pad_with)

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

  • ignore : یک آرگومان دو حالتی است. عبارت «False» را زمانی استفاده می‌کنیم که دو نقطه جداکننده متوالی داریم، در این شرایط این آرگومان یک ستون خالی ایجاد خواهد کرد. حالت پیش فرض آن در فرمول، «True» است.
  • match_mode : در حالت پیش‌فرض، با حساسیت به حروف بزرگ و کوچک انگلیسی به دنبال نقطه جداکننده می‌گردد.
  • pad_with : برای تکمیل نتیجه، مقداری را در این آرگومان وارد می‌کنیم، در غیر این صورت خطای N/A# نشان داده می‌شود. البته می‌توانید از ویژگی داخلی این برنامه برای پنهان کردن خطاها در اکسل نیز استفاده کنید.

در ادامه با بررسی مثال‌های مختلف، به نحوه عملکرد این تابع برای جداسازی متن در اکسل می‌پردازیم:

در مثال اول، رشته متنی موجود در سلول A2 را به کمک عملگر «فاصله» (Space) به جای آرگومان column_delimiter ، در چند ستون پخش می‌کنیم. سپس می‌توانید با فریز کردن ردیف ها و ستون ها در اکسل، آن‌ها را همیشه جلوی چشم نگاه دارید. فرمول تابع مانند زیر خواهد بود:

1=TEXTSPLIT(A2," ")
نمایش تکه‌های متن در ستون‌های مختلف اکسل

در ادامه، برای تکه‌تکه کردن متن و پخش آن‌ها در چند ردیف، از کاراکتر «فاصله» به جای آرگومان column_delimiter استفاده می‌کنیم:

1=TEXTSPLIT(A2,," ")

توجه داشته باشید که در فرمول بالا، جای آرگومان column_delimiter را خالی گذاشته‌ایم و پس از آن، row_delimiter را وارد کرده‌ایم. همچنین می‌توانید در نهایت به کمک ادغام سلول ها در اکسل (Merge)، این سلول‌ها را با هم یکی کنید.

نمایش تکه‌های متن در ردیف‌های مختلف اکسل

در مثال بعدی، فقط نوشته‌های پس از عبارت نقطه ویرگول (؛) را در ستون دیگری نمایش خواهیم داد:

1=TEXTSPLIT(A2,"؛")
جداسازی متن پس از نقطه ویرگول

در ادامه نیز فقط نوشته‌های بعد از عبارت نقطه ویرگول (؛) را در یک ردیف دیگر نمایش خواهیم داد:

1=TEXTSPLIT(A2,,"؛")
جداسازی متن پس از نقطه ویرگول در ردیف

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

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

آیا می‌شود بدون توابع LEFT و RIGHT، متن را در اکسل جدا کرد؟

بله، کمپانی مایکروسافت در سال 2022 چند تابع جدید برای جداسازی متن در اکسل معرفی کرده است. این توابع شامل TEXTBEFORE ،TEXTAFTER و TEXTSPLIT هستند.

جدا کردن نام و نام خانوادگی در اکسل چطور انجام می‌شود؟

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

سخن نهایی

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

بر اساس رای ۱۱ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
howtogeek
۲ دیدگاه برای «جداسازی متن در اکسل – آموزش تصویری و کامل»

سلام
من میخواستم که چند کلمه رو از یک متن جدا کنم
این کلمه همیشه بین دو کلمه خاص قرار دارند
مثال
شما آقای محمد حسنی برنده جایزه شدید
من میخوام که از متن بالا محمد حسنی (نام و نام خانوادگی) جدا بشه
نام و نام خانوادگی متغییر است(تعداد حروف متغییر است) تعداد زیادی نام و نام خانوادگی هست

راه حلی برای من دارید؟
ممنون

سلام خانم مهندس ، این توابع از چه ورژنی به بعد در اکسل موجوده ، اکسل من 2019 ولی این توابع رو نداره.

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *