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

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

ساختار تابع TEXTSPLIT

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

=TEXTSPLIT(متن, جداکننده_ستون, [جداکننده_ردیف], [ignore_empty], [match_mode], [pad_with])

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

=TEXTSPLIT(A2,";")

به‌محض زدن Enter، اکسل چهار قطعه متن را در چهار ستون کنار هم «سرریز» (Spill) می‌کند؛ نیازی به کپی کردن فرمول در ستون‌های بعدی نیست، چون خود TEXTSPLIT یک آرایه پویا برمی‌گرداند.

جداسازی دوبعدی؛ هم ستون هم ردیف با دو جداکننده

گاهی یک سلول تکی، چند رکورد کامل را با هم نگه می‌دارد؛ مثلا لاگ اضافه‌کار یک پرسنل که هر رکورد به‌صورت «تاریخ:ساعت» و رکوردها با علامت | از هم جدا شده‌اند. با دادن هر دو جداکننده به TEXTSPLIT، می‌شود این متن فشرده را مستقیم به یک جدول واقعی چندردیفی تبدیل کرد:

=TEXTSPLIT(B2,":","|")

آرگومان دوم (جداکننده_ستون) این‌جا «:» است و بین تاریخ و ساعت هر رکورد را جدا می‌کند؛ آرگومان سوم (جداکننده_ردیف) «|» است و هر رکورد را در یک ردیف جدید می‌گذارد.

نکته مهم؛ ignore_empty با فیلدهای جا افتاده

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

مقدار پیش‌فرض FALSE است؛ یعنی ستون خالی کد پرسنلی در نتیجه باقی می‌ماند و بقیه ستون‌ها سر جای درست خودشان هستند. اگر آرگومان چهارم را TRUE بگذارید، آن ستون خالی کامل حذف می‌شود و ستون‌های بعدی (بخش و سمت) یک خانه به چپ جابجا می‌شوند؛ برای جدولی که سرستون‌های ثابت دارد (نام، کد پرسنلی، بخش، سمت) همین جابجایی می‌تواند داده را زیر سرستون اشتباه بیندازد، پس معمولا بهتر است FALSE بماند و فیلد خالی به‌صورت جداگانه اصلاح شود.

=TEXTSPLIT(A2,";") در برابر =TEXTSPLIT(A2,";",,TRUE)

نکات کاربردی

• TEXTSPLIT فقط در Excel 365 در دسترس است؛ نتیجه یک آرایه پویا (Spill) است و فرمول فقط در یک سلول نوشته می شود.
• اگر جلوی مسیر سرریز، سلول دیگری پر باشد، اکسل به‌جای نتیجه، خطای #SPILL! نشان می دهد؛ قبل از نوشتن فرمول باید ستون های کناری خالی باشند.
• match_mode با مقدار ۱ جستوجوی جداکننده را نسبت به حروف بزرگ و کوچک انگلیسی بی تفاوت می کند؛ برای متن های کاملا فارسی معمولا لازم نیست.
• pad_with مقداری است که جای خانه های خالی ناشی از طول متفاوت ردیف ها (مثلا وقتی TEXTSPLIT روی چند سلول با تعداد فیلد متفاوت اجرا شود) به‌جای خطای N/A می نشیند؛ می توان آن را صفر یا خط تیره گذاشت.
• برعکس TEXTSPLIT، برای چسباندن چند ستون به یک سلول از TEXTJOIN استفاده می شود.

📄 دانلود فایل اکسل نمونه «جداسازی متن خام با TEXTSPLIT» — شامل جداسازی متن خام کارگزینی به چند ستون، اسپیل دوبعدی لاگ فشرده اضافه کار، و مقایسه ignore_empty TRUE و FALSE.

منبع : سایت حسابداری بیمارستان
کانال تلگرام @hospitalaccounting