تابع TRIM در اکسل – حذف فاصله های اضافی از متن به زبان ساده
تابع TRIM یکی از توابع متنی در Excel است که فاصلههای اضافی در ابتدا و انتهای عبارتهای متنی یا فاصلههای اضافی بین کلمات در سلول را حذف میکند. وجود فاصلههای اضافی در نمایش منظم دادهها، تحلیل دادهها و بهخصوص جستجوی دقیق عبارتهای متنی با تابع VLOOKUP یا تابع XLOOKUP و برنامهنویسی VBA مشکلساز است زیرا در برخی از این حالتها مانند وجود فاصله اضافی در ابتدا یا انتهای متن، با وجود اینکه ممکن است ظاهر عبارت از نظر کاربر یکسان باشد، اما اکسل بهدلیل عدم تطابق کامل آنها، پیام خطا نشان میدهد. بنابراین تحلیلگران داده قبل از انجام کار با این تابع جدول را از نظر وجود فاصله اضافی پاکسازی میکنند. برای آشنایی بیشتر با تابع TRIM در اکسل در این مطلب از مجله فرادرس روش کار با آن را در دو سطح ساده و پیشرفته همراه مثال یاد میگیریم.
- با ساختار کلی فرمول تابع TRIM در اکسل آشنا خواهید شد.
- فرمولنویسی ساده با تابع TRIM را همراه مثال یاد خواهید گرفت.
- فرمولنویسی پیشرفته با تابع TRIM را همراه مثال یاد خواهید گرفت.
- با مشکلات رایج کار با تابع TRIM آشنا خواهید شد.
ساختار کلی فرمول تابع TRIM در اکسل
فرمول تابع TRIM در اکسل بسیار ساده است و فقط یک آرگومان اجباری دارد. در حالت کلی ساختار این تابع به شکل زیر است.
=TRIM(text)که در این فرمول آرگومان text عبارت متنی دارای فاصله اضافی است.
البته با توجه به اینکه متن مورد نظر داخل یک سلول یا یک محدوده از جدول اکسل قرار داشته باشد، فرمول بهصورت زیر هم نوشته میشود.
- فرمول نمونه برای متن داخل سلول فرضی A1
=TRIM(A1)- فرمول نمونه برای متن داخل محدوده فرضی A1:A5
=TRIM(A1:A5)در فرمولنویسیهای ساده هنگام کار با تابع TRIM مشکلی نخواهیم داشت. اما اگر از این تابع همراه سایر توابع اکسل استفاده کنیم، نیاز به فرمولنویسیهای پیشرفته داریم. بنابراین یادگیری ترکیب این توابع همراه یکدیگر اهمیت دارد. در ادامه بحث برخی از این موارد را توضیح میدهیم، اما برای یادگیری نکات تکمیلی و آشنایی حرفهای با این کاربردها پیشنهاد میکنیم فیلم آموزش استفاده از توابع و فرمولنویسی در اکسل در فرادرس را مشاهده کنید.
مثال فرمول نویسی ساده با تابع TRIM در اکسل
فاصلههای اضافی در متنها معمولا زمانی بیشتر پیش میآید که اطلاعات را از جای دیگر داخل کاربرگ اکسل کپی کنیم. برای حذف این فاصلهها از دو مدل فرمولنویسی ساده یا پیشرفته استفاده میکنیم. در روش ساده به سه شکل زیر میتوانیم فاصلههای اضافی را با تابع TRIM حذف کنیم.
- تایپ دقیق متن در فرمول
- نوشتن نام یک سلول در فرمول
- نوشتن نام محدوده سلول در فرمول
در ادامه بحث، روش کار با هر یک را همراه مثال توضیح میدهیم.
مثال ۱: تایپ دقیق متن
این حالت سادهترین روش حذف فاصله است که فقط برای عبارتهای متنی به تعداد کم کاربرد دارد. در این حالت متن را همراه علامت گیومه " " مینویسیم. بهعنوان مثال اگر بخواهیم فاصله اضافی بین متن «آموزش فرمول نویسی» را حذف کنیم، فرمول را به شکل زیر مینویسیم.
مثال ۲: نوشتن نام یک سلول در فرمول
در جدول فرضی زیر اسامی چند آموزش درسی را داریم. همانطور که مشخص در این فهرست نامنظم، فاصله اضافی در ابتدا، انتها و بین هر کلمه داریم.
برای حذف فاصلهها در این حالت ابتدا فرمول =TRIM(A2) را در اولین سلول مینویسیم.
سپس با کشیدن گوشه سمت چپ پایین سلول به سمت پایین، فرمول را در بقیه سلولها کپی میکنیم. همان طور که در تصویر زیر مشخص است، با حذف فاصلههای اضافی، همه متنها از راست به چپ در جدول قرار میگیرند.
مثال ۳: نوشتن نام محدوده سلول در فرمول
در روش دیگر فرمولنویسی، بهجای کپی کردن فرمول میتوانیم محدوده سلول مربوط به نامها را انتخاب کنیم و در اولین سلول بنویسم.
سپس با زدن دکمه ENTER فرمول بهصورت خودکار در سلولهای دیگر اجرا میشود و نتیجه نهایی مانند حالت اول به شکل زیر درمیآید.
برای نصب اپلیکیشن رایگان مجله فرادرس، کلیک کنید.
یادگیری فرمولنویسی مقدماتی تا پیشرفته اکسل با فرادرس
تابع TRIM یکی از توابع پرکاربرد اکسل است که بیشتر همراه سایر توابع اکسل برای منظم کردن دادهها بهکار میرود. یادگیری کامل کاربردهای این تابع که در ادامه بحث برخی از مثالهای پیشرفته آن را توضیح میدهیم، نیاز به درک کامل فرمولنویسی و کارکرد سایر توابع اکسل دارد. به این شکل میتوانیم استفاده درستی از قابلیتهای این تابع داشته باشیم.
در این مسیر هر چند منابع مختلفی در دسترس است، اما مجموعه فیلمهای آموزشی تهیه شده در فرادرس راهنمایی جامع و یکپارچه هستند. با مشاهده این مجموعهها یادگیری مهارت فرمولنویسی با توابع اکسل ساده و سریع است.
پیشنهاد اول برای دسترسی سریع به مهمترین توابع اکسل مشاهده فیلمهای آموزشی زیر است.
- فیلم آموزش استفاده از توابع و فرمولنویسی در اکسل همراه گواهینامه در فرادرس
- فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس
- فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس
در مرحله بعد هم علاقهمندان میتوانند مورد دلخواه خود را بر حسب نیاز از دو مجموعه فیلم آموزش زیر انتخاب کنند.
- مجموعه فیلم آموزش مقدماتی تا پیشرفته اکسل در فرادرس
- مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس
مثال فرمولنویسی پیشرفته با تابع TRIM در اکسل
فرمولنویسی پیشرفته با تابع TRIM در اکسل زمانی نیاز است که بخواهیم کارهای تکمیلی را همراه سایر توابع انجام دهیم. در این بخش پنج حالت پرکاربرد زیر را با توابع مختلف اکسل توضیح میدهیم.
- حذف فاصله اضافی بین اعداد
- حذف فاصله اضافی و علامتها
- شمارش تعداد کلمات عبارت متنی
- حذف فاصله بدون شکست
- حدف کاراکترهای غیر قابل چاپ
مثال ۱: حذف فاصله اضافی بین اعداد
تابع TRIM فقط برای حذف فاصله اضافی عبارتهای متنی است. بنابراین در صورت استفاده برای اعداد، آنها را نیز بهعنوان متن درنظر میگیرد که مشکلاتی در انجام محاسبههای مختلف ایجاد میکند. حتی برخی مواقع ممکن است با پیام خطای #VALUE! روبرو شویم.
بهعنوان مثال اگر بخواهیم فاصلههای ابتدا و انتهای اعداد در جدول تصویر زیر را حذف کنیم، در ظاهر تابع TRIM بدون مشکل اینکار را انجام میدهد.
البته با این تفاوت که همه اعداد فارسی را راستچین میکند و مهمتر از همه اینکه بهدلیل در نظر گرفتن اعداد بهعنوان متن، هیچ عملیات ریاضی را انجام نمیدهد. یعنی اگر بخواهیم مجموع اعداد را بعد استفاده از تابع TRIM محاسبه کنیم، نتیجه عملیات را صفر نشان میدهد.
برای رفع این مشکل از تابع VALUE همراه با تابع TRIM استفاده میکنیم و فرمول زیر را مینویسیم.
=VALUE(TRIM(A2))با این کار تابع VALUE فرمت سلولها را در حالت عدد حفظ میکند.
بعد از انجام این کار برای همه سلولها، محاسبات ریاضی مانند جمع بدون مشکل انجام میشود.
مثال ۲: حذف فاصله اضافی و علامتها
تابع TRIM برای حذف علامتهایی مانند کاما، گیومه، خط فاصله، سمیکالون داخل متن نیز کاربرد دارد. بنابراین اگر بخواهیم علاوه بر فاصلههای اضافی، این علامتها را نیز حذف کنیم، از تابع SUBSTITUTE همراه تابع TRIM استفاده میکنیم.
بهعنوان مثال در جدول زیر میخواهیم علامتهای کاما را حذف کنیم.
ابتدا فرمول زیر را برای اولین سلول مینویسیم.
=TRIM(SUBSTITUTE(A2,",",""))در این فرمول لازم است علامت , را داخل گیومه بنویسیم. به این شکل ابتدا تابع SUBSTITUTE با پیدا کردن هر علامت کاما آن را از طریق دستور "" که نشاندهنده پاکسازی علامت است، حذف میکند. سپس تابع TRIM فاصلههای اضافی را نیز پاک میکند.
بعد از اجرای فرمول و کپی کردن آن در سلولهای دیگر، نتیجه نهایی به شکل تصویر زیر درمیآید.
مثال ۳: شمارش تعداد کلمات عبارت متنی
یکی از کاربردهای جالب تابع TRIM شمارش تعداد کلمات یک متن با استفاده از تابع LEN و تابع SUBSTITUTE است. بهعنوان مثال در تصویر زیر برای شمارش تعداد کلمات عبارت «اپلیکیشن مجله فرادرس» فرمول زیر را مینویسیم.
=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1
این فرمول برای شمارش تعداد کلمات به شکل زیر عمل میکند.
۱. ابتدا تابع TRIM فاصلههای اضافی در سلول را حذف میکند.
=TRIM(A2)
۲. اولین تابع LEN تعداد کاراکترهای عبارت بدون فاصله اضافی را میشمارد.
=LEN(TRIM(A2))۳. تابع SUBSTITUTE همه فاصلهها بین حروف را پاک میکند و یک رشته متنی که کاراکترهای آن کاملا به هم چسبیدهاند را بهعنوان خروجی نمایش میدهد.
=SUBSTITUTE(TRIM(A2), " ", "")
۴. دومین تابع LEN تعداد کاراکترهای رشته متنی به هم چسبیده را میشمارد.
=LEN(SUBSTITUTE(TRIM(A2), " ", ""))۵. تفاضل خروجی دو تابع LEN تعداد کاراکترهای واقعی عبارت است.
=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), "" "", """"))
۶. معمولا تعداد کلمات یکی بیشتر از تعداد کاراکترهای واقعی است. بنابراین با جمع کردن نتیجه با عدد «۱» تعداد کل کلمات عبارت بهدست میآید.
"=LEN(TRIM(A2)) -LEN(SUBSTITUTE(TRIM(A2), "" "", """")) + 1"
نتیجه نهایی هر بخش از فرمول به شکل تصویر زیر است.
در مطلب زیر از مجله فرادرس نکات تکمیلی روشهای شمارش تعداد کاراکترها در اکسل را توضیح دادهایم.
مثال ۴: حذف فاصله بدون شکست
تابع TRIM فقط فاصلههایی که با دکمه «Space» ایجاد شدهاند را حذف میکند. اما زمانهایی که متنی را از وبسایتها داخل اکسل کپی میکنیم یا ورودی دادهها، دیتابیس یک نرمافزار است، امکان انتقال فاصله بدون شکست (Non Breaking Space) در جدول اکسل وجود دارد. از نظر ظاهری این فاصله هیچ تفاوتی با فاصله اضافی ایجاد شده از طریق «Space» ندارد. بنابراین اگر آن را حذف نکنیم، در صورت استفاده از توابع جستجو، توابع شمارشی یا توابع مقایسهای اکسل خطای محاسباتی داریم. زیرا اکسل فاصله بدون شکست را نیز در محاسبه خود با توابع مختلف مانند فاصله اضافی معمولی میشمارد.
فاصله بدون شکست یکی از کاراکترهای اکسل با کد «۱۶۰» است. بنابراین برای حذف آن از تابع SUBSTITUTE همراه تابع TRIM استفاده میکنیم. فرمول نمونه زیر برای انجام این کار به شکل زیر است.
=TRIM(SUBSTITUTE(A1, CHAR(160), " "))در این فرمول تابع SUBSTITUTE به محض تشخیص کاراکتر با کد «۱۶۰» آن را با فاصله معمولی جایگزین میکند. سپس با تابع TRIM این فاصله حذف میشود.
برای آشنایی بیشتر با روش فرمولنویسی با تابع SUBSTITUTE پیشنهاد میکنیم فیلم آموزش رایگان جستجو و جایگزینی با تابع SUBSTITUTE در فرادرس را مشاهده کنید.
مثال ۵: حذف کاراکترهای غیر قابل چاپ
کاراکترهای غیر قابل چاپ (Non-Printable Charachters) کاراکترهایی هستند که در ظاهر داخل جدول اکسل نمایش داده نمیشوند، اما هنگام پردازش دادهها یا محاسبه مشکل ایجاد میکنند. این کاراکترها نیز موقع کپی کردن متن از وبسایتها داخل سلول اکسل یا انتقال دادهها از نرمافزارهای مختلف در جدول اکسل ایجاد میشوند.
هر یک از این دادهها کد مخصوص به خود را دارند. تابع TRIM به تنهایی قادر به حذف این کاراکترها نیست. بنابراین برای پاکسازی جدول از تابع CLEAN همراه با تابع TRIM استفاده میکنیم. فرمول نمونه برای حذف این کاراکترها به شرح زیر است.
=TRIM(CLEAN(A1))مشکلات رایج در کار با تابع TRIM و روش رفع آنها
اگر چه تابع TRIM بسیار ساده است، با این حال برخی مواقع احتمال دارد با پیامهای خطا روبرو شویم. موارد زیر رایجترین مشکلات کار با این تابع و ترفندهای برطرف کردن آنها است.
پیام خطای !VALUE#
دلیل ایجاد این خطا، استفاده از تابع TRIM برای حذف فاصلههای اضافی بین اعداد است. برای رفع این مشکل همانطور که مثالهای قبلی توضیح دادیم، از تابع VALUE همراه تابع TRIM استفاده میکنیم. به عنوان مثال فرمول =VALUE(TRIM(A2)) را مینویسیم.
مشکل کار با دادههای زیاد
اکسل هنگام کار با تابع TRIM در عبارتهای متنی بسیار زیاد کند عمل میکند. بنابراین تا حد امکان از تابع TRIM برای فایلهای حجیمی که فاصلههای اضافی زیادی دارد، استفاده نمیکنیم. همچنین اگر منبع دادههای وروودی به اکسل یک فایل CSV یا خروجی نرمافزار دیگر است، بهترین کار حذف فاصلههای اضافی از روشهای دیگر مانند استفاده از دستورهای برنامهنویسی یا Power Query است.
جمعبندی پایانی
تابع TRIM یکی از توابع کاربردی اکسل برای حذف فاصلههای اضافی ایجاد شده با «Space» است. حذف این فاصلهها در جدول برای جلوگیری از ایجاد خطای محاسباتی هنگام تحلیل دادهها و استفاده از سایر توابع اکسل بسیار اهمیت دارد. در این مطلب از مجله فرادرس فرمولنویسی ساده و پیشرفته تابع TRIM در اکسل را همراه مثال یاد گرفتیم. همچنین با رایجترین مشکلات هنگام کار و روش رفع آنها آشنا شدیم.
با وجود ساده بودن فرمول تابع TRIM علاقهمندان به استفاده از همه امکانات این تابع در ترکیب با سایر توابع میتوانند فیلم آموزش استفاده از توابع و فرمولنویسی در اکسل در فرادرس را مشاهده کنند.
