تابع Trim در اکسل

حذف فاصله های اضافی با تابع TRIM

حتما تا حالا براتون پیش آمده که دو تا ستون رو برای پیدا کردن داده های تکراری با هم مقایسه کردین و با وجود اینکه مطمئن بودین که داده تکراری دارین ولی فرمول هیچ خروجی نشون نداده یا اینکه خروجی تابع VLOOKUP که کاملا درست هم نوشته شده، خطای N/A باشه. یکی از علت های این مسئله این هست که داده ها حاوی فاصله های اضافی باشن.این فاصله های اضافی می تونه در ابتدا، میانه و یا انتهای متن یا عدد درج شده در یک سلول باشه. در این مقاله قصد داریم روش های سریع و ساده برای حذف فاصله های اضافی در اکسل رو با استفاده از تابع TRIM معرفی کنیم و همچینین دلایل اینکه چرا بعضی اوقات تابع TRIM نتیجه درست رو نشان نمیده رو توضیح بدیم.

معرفی تابع TRIM

از تابع TRIM در اکسل برای حذف فاصله های اضافی در متن استفاده می شه. این تابع به استثنای یک فاصله در میانه متن، تمامی فاصله های اضافی ابتدا، میانه و انتهایی متن رو حذف می کنه. تابع TRIM به شکل زیر نوشته میشه.

=TRIM(text)

که آرگومان text همان سلولی هست که متن در اون قرار گرفته و قصد داریم فاصله های اضافی اون رو حذف کنیم. توجه داشته باشید، منظور از متن هر عبارتی میتونه باشه (عدد، متن، ترکیب عدد و متن)

به طور مثال در شکل زیر برای حذف فاصله های اضافی از سلول A1 ، فرمول رو به شکل زیر می نویسیم:

شکل ۱- نحوه استفاده از تابع TRIM

همان طور که در شکل بالا مشخص هست متن نوشته شده در سلول A1 در ابتدا، انتها و وسطش فاصله اضافی داره و تابع TRIM تمام فاصله های اضافی رو حذف کرده به استثنای یک فاصله بین دو عبارت Excel و Pedia که جز فاصله های اضافی محسوب نمیشه و استاندارد هست.

حالا که با تابع TRIM آشنا شدیم، چند مثال از کاربردهای مختلف تابع TRIM رو برای حذف فاصله ها بررسی می کنیم.

حذف فاصله های اضافی در یک ستون

مطابق شکل زیر ستونی از اسامی افراد داریم که در ابتدا و انتهای هر اسم فاصله اضافی وجود داره. همچنین در بین اسم و فامیل هم فاصله اضافی درج شده. می خواهیم با استفاده از تابع TRIM تمام فاصله های اضافی این ستون رو حذف کنیم. برای انجام این کار مراحل زیر رو طی می کنیم:

شکل ۲- حذف فاصله های اضافی در یک ستون

1. در سلول B2 فرمول زیر رو می نویسیم:

=TRIM(A2)

2. فرمول نوشته شده در سلول B2 رو به سمت پایین درگ می کنیم. با اینکار فرمول نوشته شده در سلول B2 در تمام سلول های بعدی هم کپی میشه. با انجام این کار حالا دو تا ستون داریم که در یکی از اون ها داده ها با فاصله های اضافی درج شده (ستون A) و در یکی دیگه (ستون B) تمام فاصله های اضافی حذف شده.
3. حالا داده های ستون B رو در ستون A کپی می کنیم. ولی باید دقت داشته باشیم که اگر داده ها رو به صورت معمولی کپی کنیم فرمولی که در ستون B نوشتیم به خطا می خوره. برای حل این مشکل باید بعد از کپی کردن داده های ستون B در ستون A از گزینه Paste Special>Value استفاده کنیم. با این کار فقط نتیجه فرمول موجود در ستون B رو در A کپی کردیم. برای روشن تر شدن موضوع به ویدئو زیر دقت کنید.

حذف فاصله ابتدایی در ستون حاوی داده های عددی با استفاده از تابع TRIM

گاهی اوقات وقتی از یک نرم افزار خروجی اکسل می گیریم، داده های عددی فرمت Text دارن و با فاصله اضافی وارد شدن. در این مثال مطابق شکل زیر فاصله اضافی در ابتدای داده ها قرار گرفته. برای حذف این فاصله های اضافی از تابع TRIM استفاده می کنیم ولی استفاده صرف از تابع TRIM در این حالت کارساز نیست. مطابق شکل ۳ چون داده های خروجی تابع TRIM از جنس عدد نیستن، نمی تونیم روی داده ها عملیات ریاضی (جمع، تفریق، ضرب و تقسیم) انجام بدیم. برای اینکه خروجی تابع TRIM رو به عدد تبدیل کنیم از ترکیب TRIM با تابع VALUE استفاده می کنیم و مطابق شکل زیر در سلول C2 فرمول زیر رو می نویسیم و به سمت پایین درگ می کنیم. (برای دیدن روش های بیشتر، مقاله مربوط به ۴ روش تبدیل متن به عدد رو مطالعه کنید)

=VALUE(TRIM(C2))

 مشاهده می کنیم که حاصل جمع اعداد محدوده C2:C5 صفر نشده.

شکل ۳- حذف فاصله اضافی در ابتدای داده های عددی

نکته:
اکسل به طور خودکار فاصله ابتدایی داده های عددی رو حذف می کنه یعنی اگر عددی رو با فرمت Number یا General و با فاصله ابتدایی وارد کنیم و Enter بزنیم اکسل فاصله رو حذف کرده و خاصیت عددی رو حفظ میکنه. مطابق ویدئو زیر

حذف فاصله اضافی ابتدای یک رشته متنی (با حفظ فاصله اضافی بین متن)

گاهی اوقات برای اینکه متن واضح تر باشه در بین بخش های مختلف اون از بیش از یک فاصله استفاده می کنیم و نمی خواهیم که این فاصله های بین متن حذف بشه و فقط قصد از بین بردن فاصله های ابتدایی متن رو داریم. برای مثال در شکل زیر می خواهیم فقط فاصله های اضافی ابتدایی رو حذف کنیم.

شکل ۴-  داده هایی با فاصله های ابتدایی و میانی

همان طور که قبلا هم گفتیم تابع TRIMبه جز یک فاصله بین دو عبارت یک متن، باقی فاصله ها رو اضافی در نظر می گیره و حذفشون می کنه پس در داده های شکل بالا نمی تونیم فقط از تابع TRIM استفاده کنیم. برای اینکار از فرمول زیر استفاده می کنیم که ترکیبی از چند تابع هست:

=MID (A2, FIND(MID(TRIM(A2),1,1), A2), LEN(A2))

نحوه عملکرد فرمول:

در فرمول بالا از ترکیب توابع FIND ,MID ,LEN و TRIM استفاده کردیم که در ادامه تمام قسمت های فرمول رو به صورت جداگانه توضیح می دیم.

TRIM(A2): این قسمت از فرمول بالا تمامی فاصله های اضافی عبارت موجود در سلول A2 رو حذف می کنه و فقط یک فاصله بین دو بخش عبارت رو نگه می داره خروجی این قسمت از فرمول مطابق شکل زیر هست. همانطور که در شکل زیر مشخص هست تمام فاصله های ابتدایی حذف شدن و فقط یک فاصله بین دو عبارت باقی مونده.

شکل ۵- خروجی تابع TRIM

MID(TRIM(A2),1,1): با این فرمول می خواهیم اولین کاراکتر در متن رو بعد از حذف فاصله های اضافی مشخص کنیم. برای این منظور از تابع MID استفاده می کنیم. آرگومان های تابع MID به شکل زیر هست:

=MID (text, start_num, num_chars)

از تابع TRIM (که در مرحله قبل توضیح دادیم) در تابع MID و به جای آرگومان text استفاده می کنیم و چون اولین کاراکتر از ابتدای متن (از سمت چپ متن) رو می خواهیم به جای آرگومان های start_num و num_chars عدد یک رو قرار می دیم. خروجی این بخش از فرمول در شکل زیر نمایش داده شده. همان طور که مشخص هست تمام عبارت ها، اولین کاراکترشون صفر هست.

شکل ۶- خروجی ترکیب دو تابع MID و TRIM

FIND(MID(TRIM(A2),1,1), A2): در این قسمت از فرمول می خواهیم مشخص کنیم که کاراکتر صفر که در شکل ۵ نشان داده شده در متن سلول A2 کاراکتر چندم هست. برای اینکار از تابع FIND استفاده می کنیم. در واقع با این کار میفهمیم که چند اسپیس قبل از صفر وجود داره. اگر نتیجه فرمول بشه ۸. یعنی صفر، هشتمین کاراکتر در سلول هست که به این معنی خواهد بود که هفت اسپیس قبل از صفر در سلول وجود داره.

خروجی این قسمت از فرمول در شکل زیر نشون داده شده:

شکل ۷- خروجی ترکیب توابع FIND با MID و TRIM

MID (A2, FIND(MID(TRIM(A2),1,1), A2), LEN(A2)): حالا دوباره از تابع MID استفاده می کنیم که در اون آرگومان text روسلول A2 تعریف کردیم، آرگومان start_num رو تابع FIND و آرگومان num_chars رو تابع LEN(A2) (تابع LEN طول رشته متنی رو محاسبه می کنه) تعریف کردیم. در واقع با این کار به تابع MID میگیم که از مکان اولین کاراکتر (صفر) تا انتهای متن رو جدا کن.

نهایتا خروجی فرمول مطابق شکل زیر در ستون E نمایش داده شده. همان طور که مشخص هست فاصله های میانی متن که بیشتر از یکی هم هستند حفظ شدند و فاصله های ابتدایی متن حذف شدند.

شکل ۸- خروجی نهایی فرمول

حالتی که در این مثال بررسی کردیم، نمونه ای از ترکیب توابع و فرمول نویسی ترکیبی هست که قدرت توابع متنی رو بیشتر میکنه. از این نمونه ها در مقالات مربوط به MID و FIND و … قبلا بررسی کردیم.

شمارش فاصله های اضافی در یک سلول

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

1. با استفاده از تابع LEN طول رشته متنی رو بدست میاریم؛
2. با استفاده از تابع TRIM فاصله های اضافی موجود در سلول حاوی متن رو حذف می کنیم؛
3. بعد از حذف فاصله های اضافی دوباره با تابع LEN طول رشته متنی رو بدست میاریم؛
4. از تفاضل اعداد به دست آمده در مراحل ۳ و ۱ تعداد فاصله های اضافی موجود در سلول بدست میاد.

برای روشن شدن موضوع مراحل بالا در شکل زیر نشان داده شده.

شکل ۹- حذف فاصله های اضافی در سلول

فرض کنید در شکل ۸ می خواهیم تعداد تمام فاصله ها رو در سلول های A2 تا A6 به دست بیاریم. برای اینکار به شکل زیر عمل می کنیم:

1. با استفاده از تابع LEN طول رشته متنی رو بدست میاریم؛ LEN(A2)
2. با استفاده از تابع SUBSTITUTE تمام فاصله ها رو (از علامت ” ” استفاده می کنیم) جایگزین “” (به معنی هیچ مقداری) می کنیم؛ SUBSTITUTE(A2,” “,””). (با این کار هم فاصله ها از بین میره و همه کاراکترها به هم میچسبه)
3. با استفاده از تابع LEN طول رشته متنی بدست آمده از مرحله قبل رو بدست میاریم؛ LEN(SUBSTITUTE(A2,” “,””))
4. از تفاضل اعداد بدست آمده از مراحل ۱ و ۳ تعداد کل فاصله های موجود مطابق شکل زیر بدست میاد.

شکل ۱۰- حذف تمام فاصله ها در سلول

مشخص کردن سلول هایی حاوی فاصله اضافی

در شرایطی که با داده های حساس و مهم سر و کار داریم و هر گونه تغییر یا حذف اطلاعات رو باید با تامل و دقت بیشتری انجام بدیم بهتره قبل از حذف فاصله های اضافی، اول مشخص کنیم که چه سلول های فاصله اضافی دارن. برای این منظور از فرمول زیر در ابزار Conditional Formatting استفاده می کنیم.

=LEN($A2)>LEN(TRIM($A2))

در این فرمول هر سلولی که (در اینجا سلول A2) طول رشته متنی اون از طول رشته متنی بعد از اعمال تابع TRIM بیشتر باشه به عنوان سلول حاوی فاصله اضافی شناخته میشه. برای استفاده از این فرمول در Conditional Formatting به شکل زیر عمل می کنیم:

1. محدوده ای که می خواهیم Conditional Formatting رو در اون تعریف کنیم انتخاب می کنیم؛
2. از تب Home و از زیر مجموعه Styles رو Conditional Formatting کلیک می کنیم و گزینهNew Rule رو انتخاب می کنیم؛
3. در پنجره New Rule گزینه Use a formula to determine which cells to format رو انتخاب می کنیم و مطابق شکل زیر در قسمت مشخص شده فرمول بالا رو وارد می کنیم؛
4. روی دکمه Format کلیک می کنیم و فرمت دلخواه رو تعریف می کنیم و در پنجره Format Cell دکمه OK رو می زنیم. دوباره به پنجره New Formatting Rule برمی گردیم؛
5. روی دکمه OK کلیک می کنیم.

شکل ۱۱- مشخص کردن سلول های حاوی فاصله های اضافی با Conditional Formatting

محدودیت تابع TRIM در حذف فاصله ها

تابع TRIM فقط می تونه فاصله هایی رو حذف بکنه که کد کاراکتر آن ها ۳۲ هست. (این عدد رو تابع CODE بهمون میده. یعنی اگر بنویسیم Code(” “) بهمون میده ۳۲). علاوه بر این کاراکتر فاصله، کاراکتر فاصله دیگه ای هم داریم که کد اون ۱۶۰ هست که به این نوع فاصله non-breaking space گفته می شه و معمولاً در صفحات وب استفاده می شه. تابع TRIM نمی تونه فاصله هایی از این نوع رو حذف بکنه. در صورتی که داده ها حاوی این نوع از فاصله باشن، باید اول با استفاده از تابع SUBSTITUTE فاصله با کد کاراکتر ۱۶۰ رو تبدیل به فاصله با کد کاراکتر ۳۲ کنیم و بعد از تابع TRIM برای حذف فاصله های اضافی استفاده کنیم. نحوه نگارش فرمول و خروجی اون در شکل زیر نمایش داده شده.

شکل ۱۲- حذف non-breaking spaces با استفاده از ترکیب توابع SUBSTITUTE و TRIM

برای اطمینان از اینکه سلول از هر نوع کاراکتر غیر قابل چاپ (non-printable characters) پاکسازی شده، می تونیم از تابع CLEAN استفاده کنیم در این صورت فرمول به شکل زیر نوشته خواهد شد:

=TRIM (CLEAN (SUBSTITUTE (A2, CHAR (160), ” “)))

براساس فرمول بالا قبل از اینکه از تابع TRIM برای حذف فاصله های اضافی استفاده کنیم با استفاده از تابع CLEAN سلول رو از هر نوع کاراکتر غیر قابل چاپ پاکسازی کردیم.

در صورتی که از فرمول های بالا نتیجه مطلوب حاصل نشد، این امکان وجود داره که داده ها حاوی کاراکترهای غیر قابل چاپ دیگه ای به جز کاراکتر با کد های ۳۲ و ۱۶۰ باشن. در این مواقع ابتدا با توجه به موقعیت قرار گیری فاصله در متن، از یکی از فرمول های زیر (با این فرض که متن در سلول A1 قرار گرفته) برای استخراج کد کاراکتر غیر قابل چاپ استفاده می کنیم.با کمک توابعی مثل Char و CODE و بقیه توابع متنی میتونیم کاراکتر اضافی موجود رو تعیین کنیم. مثلا:

برای فاصله در ابتدای متن:

=CODE(LEFT(A1,1))

برای فاصله در انتهای متن:

=CODE(RIGHT(A1,1))

برای فاصله در میانه متن (حرف n نشان دهنده محل قرار گیری کاراکتر غیر قابل چاپ در متن هست):

=CODE (MID (A1, n, 1)))

برای مثال اگر خروجی تابع CODE عدد ۹ باشه، برای حذف این کاراکتر فرمول رو به شکل زیر می نویسیم:

=TRIM (SUBSTITUTE (A1, CHAR (9), ” “))

 در فرمول بالا با استفاده از تابع SUBSTITUTE کاراکتر با کد ۹ رو به کاراکتر با کد ۳۲ (فاصله معمولی) تبدیل کردیم تا بتونیم از طریق تابع TRIM این فاصله رو حذف کنیم.

در این مقاله کاربردهای تابع TRIM رو در حذف فاصله های اضافی بررسی کردیم و چند مثال از ترکیب تابع TRIM با توابع MID، SUBSTITUTE، LEN، CODE و CHAR ارائه کردیم. همچنین محدودیت های تابع TRIM رو در حذف فاصله ها با مثال توضیح دادیم.