تمرین ۲۶: پاکسازی داده‌ها قبل از تفکیک با توابع MID, SUBSTITUTE, ABS و COLUMN

این یک مسئله جالب برای “دست‌ورزی با متن” (String Manipulation) است. استفاده همزمان از این چهار تابع معمولاً برای سناریوهایی است که نیاز به پاکسازی داده‌ها قبل از تفکیک داریم و می‌خواهیم فرمول نسبت به موقعیت ستون‌ها انعطاف‌پذیر باشد.

---

📝 سناریو: تکمیل فرم‌های بانکی (حروف جداگانه)

فرض کنید شما لیست اسامی مشتریان را دارید. برخی از این اسامی دارای فاصله (Space) یا خط فاصله (-) هستند. برای چاپ روی فرم‌های بانکی خاص، باید:

1. تمام فاصله‌ها و علائم اضافی حذف شوند.
2. هر حرف داخل یک سلول جداگانه قرار گیرد.

ما می‌خواهیم فرمولی بنویسیم که در اولین سلول جدول نوشته شود و وقتی آن را به سمت چپ می‌کشیم (Drag)، حروف را یکی‌یکی استخراج کند.

ساختار جدول

ردیف	A (نام کامل ورودی)	B	C	D	E	F
1	Full Name	Ch1	Ch2	Ch3	Ch4	Ch5
2	Ali Reza	A	l	i	R	e
3	Sara-J	S	a	r	a	J

---

💡 راه حل و فرمول

در سلول B2 فرمول زیر را بنویسید و آن را به سمت چپ (تا جایی که نام‌ها تمام شوند) بکشید:

=MID(SUBSTITUTE($A2, " ", ""), ABS(COLUMN() - COLUMN($A2)), 1)

(نکته: اگر می‌خواهید خط تیره “-” را هم حذف کنید، باید از یک SUBSTITUTE تودرتو استفاده کنید، اما برای سادگی اینجا فقط فاصله ” ” حذف شده است).

---

🔍 تحلیل فرمول (هر تابع چه کاری انجام می‌دهد؟)

بیایید این فرمول ترکیبی را کالبدشکافی کنیم:

1. SUBSTITUTE($A2, " ", ""):

   • وظیفه: پاکسازی داده.

   • این تابع متن داخل سلول A2 (“Ali Reza”) را می‌گیرد و تمام فاصله‌ها (” “) را با هیچ (“”) جایگزین می‌کند.

   • نتیجه: “AliReza” (حالا یک رشته تمیز برای تفکیک داریم).

2. COLUMN() - COLUMN($A2):

   • وظیفه: تولید شماره ایندکس پویا.

   • ما در ستون B هستیم. COLUMN() مقدار 2 را برمی‌گرداند.

   • ستون مرجع ما A است. COLUMN($A2) مقدار 1 را برمی‌گرداند.

   • حاصل تفریق: 2 - 1 = 1. (این یعنی حرف اول را می‌خواهیم).

   • وقتی فرمول به ستون C برود: 3 - 1 = 2. (حرف دوم را می‌خواهیم).

3. ABS(...):

   • وظیفه: تضمین عدد مثبت (قدر مطلق).

   • در این سناریو، ABS تضمین می‌کند که حتی اگر به اشتباه فرمول جابجا شود یا منطق ستون‌بندی تغییر کند، ما همیشه یک عدد مثبت برای جایگاه حرف داشته باشیم (چون تابع MID با اعداد منفی کار نمی‌کند).

   • نتیجه ABS(1) همان 1 است.

4. MID(متن_تمیز, شماره_حرف, 1):

• وظیفه: استخراج نهایی.
• حالا تابع می‌گوید: از متن “AliReza”، برو به جایگاه 1 و 1 عدد حرف را جدا کن.
• نتیجه در سلول B2: “A”
• در سلول C2 (جایگاه 2): “l”

خلاصه عملکرد

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