فصل ۳: توابع کلیدی — منطقی، شرطی، متنی، تاریخ و گرد کردن

پاک‌سازی متن فارسی: ی و ک عربی، فاصله‌ی نشکن، کاراکترهای نامرئی و ارقام فارسی

دو «کاشان» که با هم برابر نیستند

VLOOKUP پیدا نمی‌کند، Pivot دو ردیف «علی رضایی» نشان می‌دهد و Remove Duplicates تکراری‌ها را حذف نمی‌کند. در داده‌های فارسی علت تقریباً همیشه یکی از این‌هاست: «ی» و «ک» عربی، فاصله‌ی اضافه یا نشکن، کاراکترهای جهت‌دهی نامرئی یا ارقام فارسی. چشم تفاوتی نمی‌بیند؛ اکسل کد یونیکد را مقایسه می‌کند.

کاراکترهای مشکل‌ساز

کاراکترکد (دهدهی)جایگزین درست
ي (یای عربی)1610ی فارسی 1740
ك (کاف عربی)1603ک فارسی 1705
ى (الف مقصوره)1609ی فارسی 1740
فاصله‌ی نشکن (NBSP)160فاصله‌ی معمولی 32
RLM / LRM (نشانه‌ی جهت)8207 / 8206حذف
کشیده (ـ)1600حذف
نیم‌فاصله (ZWNJ)8204درست است؛ نگه دارید
ارقام فارسی ۰ تا ۹1776 تا 1785ارقام 0 تا 9
ارقام عربی ٠ تا ٩1632 تا 1641ارقام 0 تا 9

تشخیص: کاراکترهای یک سلول را ببینید

=LEN(A2)                                   ← اگر از تعداد حروف دیدنی بیشتر است، چیزی پنهان است
=UNICODE(MID(A2, SEQUENCE(LEN(A2)), 1))    ← (365) کد همه‌ی کاراکترها را زیر هم می‌ریزد
=CODE(RIGHT(A2))                            ← (همه‌ی نسخه‌ها) کد کاراکتر آخر؛ 160 یعنی NBSP

درمان با فرمول

ی و ک (همه‌ی نسخه‌ها):
=SUBSTITUTE(SUBSTITUTE(A2, UNICHAR(1610), UNICHAR(1740)), UNICHAR(1603), UNICHAR(1705))

فاصله‌ها و کاراکترهای کنترلی:
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

تبدیل کامل با LET و REDUCE (Excel 365):
=LET(
  yk, SUBSTITUTE(SUBSTITUTE(A2, UNICHAR(1610), UNICHAR(1740)), UNICHAR(1603), UNICHAR(1705)),
  dg, REDUCE(yk, SEQUENCE(10,,0), LAMBDA(s,i,
        SUBSTITUTE(SUBSTITUTE(s, UNICHAR(1776+i), i), UNICHAR(1632+i), i))),
  nd, SUBSTITUTE(SUBSTITUTE(dg, UNICHAR(8207), ""), UNICHAR(8206), ""),
  TRIM(SUBSTITUTE(nd, CHAR(160), " "))
)

ارقام فارسی به عدد:  =--(فرمول بالا)

در فصل ۴ همین فرمول را با LAMBDA به یک تابع اختصاصی به نام CLEANFA تبدیل می‌کنیم و در فصل ۸ یک ماکرو برای اصلاح درجای داده می‌نویسیم. برای حجم بالا یا داده‌ای که مرتب از سامانه می‌آید، Power Query (فصل ۵) بهترین جاست.

نکته‌هایی که کمتر کسی می‌داند

  • TRIM فقط فاصله‌ی معمولی (کد 32) را حذف می‌کند و فاصله‌های تکراری میان کلمات را به یکی می‌رساند؛ NBSP که از وب و تلگرام می‌آید دست‌نخورده می‌ماند.
  • CLEAN فقط کاراکترهای کنترلی 0 تا 31 را پاک می‌کند؛ RLM، LRM و فاصله‌های عرض‌صفر یونیکد را نمی‌شناسد.
  • در پنجره‌ی Find & Replace (Ctrl+H) می‌توانید با نگه‌داشتن Alt و تایپ 0160 روی صفحه‌کلید عددی، فاصله‌ی نشکن را در کادر Find وارد کنید و یکجا جایگزین کنید.
  • Find & Replace اکسل گزینه‌ی «Match entire cell contents» دارد؛ بدون آن، جایگزینی «ي» در یک ستون کد لاتین هم ممکن است اثر ناخواسته بگذارد. همیشه اول ستون را انتخاب کنید.
  • مرتب‌سازی الفبایی فارسی به کد یونیکد وابسته است؛ «کریمی» با کاف عربی جدا از «کریمی» با کاف فارسی مرتب می‌شود. قبل از هر Sort یا Pivot، یکدست‌سازی را انجام دهید.
درس قبلی درس بعدی

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