آشنایی با تابع طول رشته LEN اکسل

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

در چنین شرایطی یکی از ساده‌ترین و کاربردی‌ترین توابع اکسل، تابع LEN است.

تابع LEN جزو توابع متنی اکسل است. این تابع تعداد کاراکترهای یک رشته‌ی متنی معین را می‌شمارد.

این تابع تنها یک آرگومان دارد. این آرگومان یک رشته‌ی متنی است که می‌خواهید طول آن را بیابید که اگر مستقیماً وارد شود باید داخل دابل کوتیشن (“”) باشد.

از این تابع برای یافتن طول اعداد نیز می‌توان استفاده کرد، در این صورت نیازی به استفاده از دابل کوتیشن نیست.

(”رشته”)Len=

این تابع تمام کاراکترها را می‌شمارد؛ یعنی:

حروف
اعداد
فاصله‌ها
علائم
سایر کاراکترهای موجود در متن

یک مثال ساده از تابع LEN

فرض کنید در سلول A2 عبارت زیر را داریم:

Excel

اگر بنویسیم:

=LEN(A2)

نتیجه برابر با 5 خواهد بود، زیرا کلمه Excel شامل پنج کاراکتر است.

حتی اگر متن فارسی باشد، تابع LEN تعداد کاراکترها را محاسبه می‌کند.

مثلاً:

=LEN("اکسل")

نتیجه:

LEN فاصله‌ها را هم حساب می‌کند

یکی از نکات مهم درباره تابع LEN این است که فاصله نیز یک کاراکتر محسوب می‌شود.

مثلاً:

=LEN("Hello World")

نتیجه برابر با 11 است.

چرا؟

کلمه Hello پنج کاراکتر، فاصله یک کاراکتر و World نیز پنج کاراکتر دارد:

5 + 1 + 5 = 11

بنابراین اگر در داده‌های شما فاصله اضافی وجود داشته باشد، نتیجه LEN ممکن است بیشتر از چیزی باشد که انتظار دارید.

کاربرد LEN برای بررسی کد ملی

یکی از کاربردهای جالب LEN، کنترل طول داده‌های واردشده است.

فرض کنید کد ملی افراد در محدوده A2:A100 قرار دارد و می‌خواهیم بررسی کنیم آیا مقدار واردشده 10 کاراکتر دارد یا خیر.

می‌توانیم از فرمول زیر استفاده کنیم:

=IF(LEN(A2)=10,"صحیح","بررسی شود")

اگر طول مقدار موجود در A2 برابر 10 باشد، عبارت «صحیح» نمایش داده می‌شود؛ در غیر این صورت «بررسی شود» نمایش داده خواهد شد.

البته برای کنترل واقعی کد ملی، فقط تعداد کاراکترها کافی نیست و باید اعتبار خود کد نیز بررسی شود. اما LEN برای کنترل اولیه طول داده بسیار کاربردی است.

ترکیب LEN با IF

یکی از کاربردهای رایج LEN، استفاده از آن در کنار تابع IF است.

فرض کنید می‌خواهیم بررسی کنیم یک شماره پرسنلی دقیقاً 6 کاراکتر دارد یا خیر:

=IF(LEN(A2)=6,"Correct","No Correct")

این روش برای کنترل ورود اطلاعات در فایل‌های اکسل بسیار مفید است.

مثلاً اگر اطلاعاتی مانند:

کد پرسنلی
کد دانشجویی
کد کالا
شماره پرونده
کد محصول

داریم، می‌توانیم طول آن‌ها را کنترل کنیم.

تفاوت LEN و تعداد ارقام یک عدد

یک نکته مهم این است که LEN با مقدار ذخیره‌شده در سلول کار می‌کند و نه با ظاهر نمایشی آن.

مثلاً فرض کنید عدد 12345 در سلول A2 قرار دارد:

=LEN(A2)

نتیجه:

اما اگر برای نمایش عدد، قالب خاصی تعیین کرده باشیم، باید دقت کنیم که LEN الزاماً آن چیزی را که روی صفحه می‌بینیم شمارش نمی‌کند.

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

مثلاً کدی مانند:

از نظر کاربردی ممکن است یک کد شش‌رقمی باشد، اما اگر اکسل آن را به‌صورت عدد ذخیره کند، صفرهای ابتدایی حذف می‌شوند و مقدار به شکل 1245 درمی‌آید.

در این حالت نتیجه LEN نیز 4 خواهد بود.

ترکیب LEN و TRIM برای حذف فاصله‌های اضافی

یکی از مشکلات رایج در فایل‌های اکسل، وجود فاصله‌های اضافی در ابتدا یا انتهای متن است.

برای مثال ممکن است در یک سلول نوشته شده باشد:

  Excel

تابع LEN این فاصله را هم حساب می‌کند.

برای حذف فاصله‌های اضافی می‌توانیم از تابع TRIM استفاده کنیم:

=LEN(TRIM(A2))

در این حالت ابتدا فاصله‌های اضافی توسط TRIM اصلاح می‌شوند و سپس تعداد کاراکترها توسط LEN محاسبه می‌شود.

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

پیدا کردن سلول‌های خالی با LEN

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

مثلاً:

=IF(LEN(A2)=0,"empty", "full")

اگر سلول A2 خالی باشد، نتیجه «empty» خواهد بود.

البته در بسیاری از موارد می‌توان از ISBLANK نیز استفاده کرد، اما LEN زمانی مفید است که بخواهیم طول محتوای متنی را نیز در همین فرایند بررسی کنیم.

شمارش تعداد کاراکترهای یک متن مستقیم

لزومی ندارد همیشه آدرس سلول را وارد کنیم. می‌توانیم مستقیماً یک متن را نیز به LEN بدهیم:

=LEN("آموزش اکسل")

در این حالت اکسل تعداد تمام کاراکترهای موجود در عبارت، از جمله فاصله بین کلمات را محاسبه می‌کند.

LEN در داده‌های فارسی

تابع LEN برای متن فارسی نیز قابل استفاده است و تعداد کاراکترهای متن را محاسبه می‌کند.

برای مثال:

=LEN("آموزش اکسل")

در این عبارت حروف و فاصله بین دو کلمه نیز در تعداد نهایی لحاظ می‌شوند.

بنابراین هنگام کار با اطلاعات فارسی، اگر هدف شما کنترل طول یک متن یا کد است، LEN می‌تواند ابزار بسیار مناسبی باشد.

LEN برای محدود کردن طول متن

فرض کنید می‌خواهیم بررسی کنیم توضیحات واردشده در سلول A2 بیشتر از 100 کاراکتر نباشد:

=IF(LEN(A2)<=100,"مجاز","بیش از حد مجاز")

این فرمول می‌تواند برای کنترل اطلاعات در فرم‌های اکسل یا فایل‌های ثبت اطلاعات استفاده شود.

مثلاً می‌توانیم برای فیلدهایی مانند:

توضیحات محصول
عنوان کالا
نام مشتری
توضیحات سفارش
کد کالا

حداکثر طول مشخص کنیم.

ترکیب LEN با RIGHT و LEFT

تابع LEN معمولاً در کنار توابع متنی دیگر نیز استفاده می‌شود.

برای مثال، فرض کنید بخواهیم آخرین کاراکتر یک متن را استخراج کنیم. می‌توانیم از ترکیب LEN و RIGHT استفاده کنیم:

=RIGHT(A2,LEN(A2))

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

همچنین در ترکیب با LEFT، RIGHT، MID، FIND و SEARCH می‌توان فرمول‌های بسیار کاربردی برای پردازش متن ایجاد کرد.

تفاوت LEN و LENB

در نسخه‌های جدید اکسل، تابع LEN برای شمارش کاراکترها استفاده می‌شود.

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

در کارهای معمول اکسل، به‌خصوص هنگام کار با متن فارسی، معمولاً LEN همان تابعی است که به آن نیاز داریم.

چند مثال کاربردی از LEN

بررسی طول کد کالا

=IF(LEN(A2)=8,"کد صحیح","کد را بررسی کنید")

بررسی حداقل طول رمز عبور

=IF(LEN(A2)>=8,"مجاز","رمز کوتاه است")

بررسی طول شماره دانشجویی

=IF(LEN(A2)=10,"صحیح","بررسی شود")

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

=IF(LEN(A2)>0,"وارد شده","خالی")

شمارش متن بعد از حذف فاصله‌های اضافی

=LEN(TRIM(A2))

نکته مهم درباره LEN و فاصله‌های اضافی

گاهی ممکن است دو متن از نظر ظاهری یکسان به نظر برسند، اما یکی از آن‌ها دارای فاصله اضافی باشد.

مثلاً:

Excel

Excel

در نگاه اول تفاوت زیادی دیده نمی‌شود، اما نتیجه LEN برای آن‌ها متفاوت خواهد بود.

به همین دلیل اگر داده‌ها از سیستم دیگری وارد اکسل شده‌اند، بهتر است قبل از بررسی طول متن، وجود فاصله‌های اضافی را نیز در نظر بگیریم.

جمع‌بندی

تابع LEN یکی از ساده‌ترین توابع متنی اکسل است، اما کاربردهای آن بسیار بیشتر از چیزی است که در نگاه اول به نظر می‌رسد.

این تابع به شما کمک می‌کند:

تعداد کاراکترهای یک متن را محاسبه کنید.
طول کدها را کنترل کنید.
اطلاعات واردشده را اعتبارسنجی اولیه کنید.
داده‌های خالی یا دارای اطلاعات را تشخیص دهید.
متن‌های طولانی را شناسایی کنید.
در کنار توابعی مانند IF، TRIM، LEFT، RIGHT و MID فرمول‌های کاربردی بسازید.

ساختار اصلی تابع را نیز کافی است به خاطر بسپارید:

=LEN(text)

هر زمان که در اکسل از خودتان پرسیدید «این سلول چند کاراکتر دارد؟»، تابع LEN یکی از اولین ابزارهایی است که باید سراغ آن بروید.

پیشنهاد برای یادگیری بیشتر

اگر با توابع متنی اکسل کار می‌کنید، بعد از یادگیری LEN بهتر است سراغ توابع LEFT، RIGHT، MID، FIND، SEARCH و TEXT بروید. ترکیب این توابع می‌تواند در پاک‌سازی و استخراج اطلاعات از داده‌های متنی بسیار قدرتمند باشد.