بریدن و چسباندن متن بدون FIND های تو در تو
تا چند سال پیش جدا کردن بخش وسط یک کد مثل «KSH-1403-0587» فرمولی از LEFT، MID و چند FIND تو در تو می‌خواست. Excel 365 توابعی دارد که همین کار را خوانا و کوتاه انجام می‌دهند. جدول زیر نسخه‌ی مورد نیاز هر تابع را نشان می‌دهد:
| تابع | کار | نسخه |
|---|---|---|
| CONCAT | چسباندن محدوده‌ها | 2019 به بعد |
| TEXTJOIN | چسباندن با جداکننده و نادیده‌گرفتن خالی‌ها | 2019 به بعد |
| TEXTBEFORE / TEXTAFTER | متن قبل یا بعد از یک جداکننده | 365 (و 2024) |
| TEXTSPLIT | شکستن متن به چند ستون یا سطر (spill) | 365 (و 2024) |
| TEXT | تبدیل عدد یا تاریخ به متن با قالب | همه |
| LEFT، RIGHT، MID، FIND، SEARCH، LEN | ابزارهای کلاسیک | همه |
مثال‌ها روی کد فاکتور «KSH-1403-0587»
=TEXTBEFORE(A2, "-") → KSH
=TEXTAFTER(A2, "-") → 1403-0587
=TEXTAFTER(A2, "-", -1) → 0587 (عدد منفی: از انتها بشمار)
=TEXTBEFORE(TEXTAFTER(A2,"-"),"-") → 1403
=TEXTSPLIT(A2, "-") → KSH | 1403 | 0587 (سه ستون)
=TEXTSPLIT(A2, {"-","/"}) → چند جداکننده هم‌زمان
=TEXTBEFORE(A2, "#", , , , A2) → اگر # نبود، کل متن (آرگومان if_not_found)
معادل کلاسیک برای Excel 2016/2019 (بخش وسط):
=MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)TEXTJOIN: فهرست‌سازی
=TEXTJOIN("، ", TRUE, B2:B20) ← نام‌ها با ویرگول فارسی
=TEXTJOIN("، ", TRUE, UNIQUE(FILTER(B:B, C:C="کاشان"))) ← نمایندگان یکتای کاشان (365)
=TEXTJOIN(CHAR(10), TRUE, B2:B6) ← هر نام در یک خط (Wrap Text روشن)TEXT: عدد داخل جمله
وقتی عدد را با & به متن می‌چسبانید، قالب عددی از بین می‌رود: ="فروش: "&E1 عدد را بدون جداکننده‌ی هزارگان نشان می‌دهد. TEXT همان کدهای قالب سفارشی درس ۳ را می‌پذیرد:
="مجموع فروش مرداد: "&TEXT(E1,"#,##0")&" تومان"
="تاریخ گزارش: "&TEXT(TODAY(),"[$-fa-IR,16]yyyy/mm/dd")
="رشد: "&TEXT(G1,"0.0%;-0.0%")
=TEXT(E1,"[$-3000000]#,##0") ← با ارقام فارسینکتههایی که کمتر کسی می‌داند
- TEXTSPLIT دو جداکننده‌ی جدا می‌گیرد: ستونی و سطری.
=TEXTSPLIT(A2, ",", ";")متنی مثل «a,b;c,d» را به یک جدول ۲×۲ تبدیل می‌کند. - FIND به بزرگی و کوچکی حروف حساس است و wildcard نمی‌پذیرد؛ SEARCH برعکس. برای متن فارسی تفاوتی در حروف بزرگ و کوچک نیست، اما wildcard مهم است.
- TEXTJOIN حداکثر 32٬767 کاراکتر (سقف یک سلول) خروجی دارد و اگر بیشتر شود #VALUE! می‌دهد؛ برای فهرست‌های طولانی مراقب باشید.
- LEN نیم‌فاصله را یک کاراکتر می‌شمارد؛ «کتاب‌ها» ۶ کاراکتر است نه ۵. در اعتبارسنجی طول متن (مثلاً عنوان کالا حداکثر ۵۰ حرف) این را در نظر بگیرید.
- TEXTBEFORE و TEXTAFTER آرگومان match_mode دارند (1 = بدون حساسیت به حروف) و آرگومان match_end که انتهای متن را هم «جداکننده» فرض می‌کند؛ برای متن‌هایی که گاهی جداکننده ندارند مفید است.