توابع متنی در اکسل که باید بشناسید – راهنمای سریع + مثال

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

تابع متنی LEN در اکسل

LEN یکی از توابع متنی اکسل است که برای شمارش حروف مقدار مربوطه به کار می‌رود. به بیان دیگر با کمک این تابع می‌توانید ببینید که مقدار موردنظر شما چند حرف دارد و طول رشته چقدر است. شما می‌توانید متن موردنظر خود را به صورت مستقیم درون فرمول تابع LEN نوشته یا به سلول دیگری ارجاع دهید.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

فرمول این تابع به شکل زیر نوشته می‌شود:

1LEN(text)

آرگومان Text   در این فرمول برای جایگذاری رشته‌ای است که می‌خواهید طول آن را به‌دست بیاورید.

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

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

همان‌طور که می‌بینید، طول رشته «حافظ شیرازی» همراه با یک فاصله، برابر ۱۱ است. در نمونه زیر، طول رشته موردنظر، هشت است اما نتیجه فرمول LEN عدد «۵» را نشان می‌دهد.

دلیل این امر را می‌شود اینگونه توضیح داد که تابع LEN، حروف موجود در سلول را شمارش می‌کند و قالب بندی آن را در نظر نمی‌گیرد. در این مثال ما برای وارد کردن تاریخ، قالب بندی سلول در اکسل (Format Cell) را عوض کرده‌ایم.

آشنایی با FIND یکی از توابع متنی پرکاربرد اکسل

تابع FIND، موقعیت عددی شروع یک زیر رشته را در یک رشته نشان می‌دهد، از پرکاربردترین توابع اکسل که باید بدانید محسوب می‌شود. به بیانی دیگر با کمک این تابع متنی می‌توانید محل شروع یک رشته درون یک رشته دیگر را پیدا کنید. توجه داشته باشید که FIND یکی از تابع های متنی اکسل است که حروف بزرگ و کوچک انگلیسی را به صورت مجزا در نظر می‌گیرد.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

فرمول این تابع به شکل زیر نوشته می‌شود:

1FIND(find_text,within_text,[start_num])

آرگومان‌های آن نیز به شرح زیر هستند:

• Find_text  : متنی که می‌خواهید آن را از متن دیگر پیدا کنید.
• Within_text  : متنی که می‌خواهید موقعیت آن را پیدا کنید.
• [start_num]  : عددی که موقعیت شروع جست‌وجو را نشان می‌دهد.

مثال کار با تابع متنی FIND در اکسل

در بیشتر مواقع از تابع FIND در ترکیب با سایر توابع متنی و غیرمتنی اکسل استفاده می‌شود. نحوه استفاده از این تابع را نیز در مطالب قبلی مجله فرادرس یاد گرفتیم. در مثال زیر قصد داریم موقعیت نماد «:» را به کمک تابع FIND مشخص کرده و سپس با دو تابع MID و LEN، نام را از داخل سلول استخراج کنیم.

به این منظور، فرمول زیر را در سلول نتیجه می‌نویسیم:

1= MID(A2, FIND(":", A2) + 2, LEN(A2) - FIND(":", A2) - 1)

به این شکل ابتدا تابع FIND، موقعیت قرارگیری علامت «:» را مشخص می‌کند. سپس ۲ واحد به آن اضافه می‌شود تا خود این علامت و فاصله بعد از آن را پشت سر بگذاریم و به حرف بعدی برسیم.

در مرحله بعدی، تابع LEN، تعداد حروف موجود از علامت «:» تا انتهای سلول را شمرده و سپس خود علامت «:» را از شمارش حذف می‌کند. در نهایت تابع MID، رشته قرار گرفته بین حد فاصل علامت «:» و انتهای سلول را استخراج کرده و در خروجی نشان می‌دهد.

معرفی تابع متنی SEARCH در اکسل

SEARCH نیز یکی دیگر از تابع های متنی در اکسل است که موقعیت عددی شروع یک زیررشته درون رشته دیگر را نشان می‌دهد. به بیانی دیگر با کمک این تابع می‌توانید موقعیت شروع یک رشته متنی درون یک رشته دیگر را جست‌وجو کنید. توجه داشته باشید که این تابع نسبت به بزرگی و کوچکی حروف انگلیسی حساس نیست. فرمول تابع SEARCH به شکل زیر نوشته می‌شود:

1SEARCH(find_text,within_text,[start_num])

آرگومان‌های این تابع به شرح زیر هستند:

• Find_text  : متنی که می‌خواهید آن را از داخل متن دیگر پیدا کنید یا زیر رشته.
• Within_text  : متنی که می‌خواهید متن دیگر را از داخل آن پیدا کنید یا رشته اصلی.
• [start_num]  : موقعیتی در رشته اصلی که می‌خواهید جست‌وجو را از آن آغاز کنید. در صورت خالی بودن، جست‌وجو از اولین حرف رشته شروع می‌شود.

شما همچنین می‌توانید متن موردنظر را به طور مستقیم درون این فرمول بنویسید یا آن را در سلول دیگری قرار داده و سپس به آن ارجاع دهید.

مثال کار با تابع SEARCH، یکی از توابع متنی مهم در اکسل

در این مثال می‌خواهیم با کمک تابع متنی SEARCH، حرف الف را جست‌وجو کنیم. در این نمونه، آرگومان [start_num]   را روی مقدار «۱» قرار داده‌ایم تا جست‌وجو از اولین حرف شروع شود. به این منظور فرمول زیر را در سلول نتیجه می‌نویسیم.

1=SEARCH("ا",A2,1)

با انجام این کار، تابع SEARCH درون رشته جست‌وجو کرده و عدد مربوط به محل قرارگیری حرف الف را در خروجی نشان می‌دهد.

اما همان‌طور که می‌بینید، ما یک حرف الف دیگر نیز در کلمه «شیرازی» داریم. این یعنی تابع متنی SEARCH تنها قادر به نشان دادن موقعیت اولین نمایش حرف موردنظر در رشته است. برای نمایش دومین حرف الف در این مثال باید محل شروع جست‌وجو یا آرگومان [start_num]  را به شکل زیر تغییر دهیم:

1=SEARCH("ا",A2,6)

در اینجا، عدد شش نشان دهنده شروع رشته دوم یا «شیرازی» است و به فرمول می‌فهماند که جست‌وجو را از ابتدای این رشته شروع کند. در نتیجه خروجی عدد «۹» خواهد بود که نشان دهنده دومین حرف الف در رشته اشت.

معرفی تابع LEFT یکی از موثرترین توابع متنی در اکسل

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

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

بیشترین کاربرد این تابع متنی در جدا کردن نام و نام خانوادگی در اکسل است و فرمول آن به شکل زیر نوشته می‌شود:

1LEFT(text,num_chars)

آرگومان‌های این تابع به شرح زیر هستند:

• Text  : متن یا عددی که می‌خواهید حروف آن را استخراج کنید.
• [num_char]  : تعداد حروفی که قصد استخراج آن‌ها را دارید.

مثال کار با تابع متنی LEFT در اکسل

در مثال زیر می‌خواهیم پنج حرف اول رشته متنی موجود در سلول A2 را به کمک تابع LEFT استخراج کرده و در خروجی نشان دهیم. به این منظور از فرمول زیر استفاده می‌کنیم:

1=LEFT(A2,5)

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

البته باید توجه داشته باشید که اگر رشته موردنظر فارسی باشد، جست‌وجو به جای سمت چپ از سمت راست شروع می‌شود. به این دلیل که فارسی برعکس انگلیسی از سمت راست به چپ نوشته می‌شود و این مورد در عملکرد توابع متنی در اکسل تاثیرگذار است.

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

فرمول نهایی به شکل زیر است:

1=LEFT(A2,LEN(A2)-FIND(" ",A2)-1)

در این فرمول ابتدا تابع FIND، موقعیت اولین فاصله را در رشته موجود در سلول A2 پیدا می‌کند. سپس موقعیت آن را نشان می‌دهد و اگر فاصله‌ای در رشته نباش، خطا خواهد داد. در مرحله بعد، تابع LEN، مجموع کل حروف موجود در سلول A2 را نشان می‌دهد.

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

به این شکل هر حرفی که بعد از اولین فاصله نوشته شده باشد، شامل خود فاصله، از رشته A2 حذف می‌شود. در نهایت تمام آن‌چه پیش از اولین فاصله نوشته شده، در خروجی نشان داده خواهد شد که همان نام یا «Hafez» است.

آشنایی با تابع متنی RIGHT

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

1RIGHT(text,num_chars)

آرگومان‌های این تابع به شرح زیر هستند:

• Text  : متن یا عددی که می‌خواهید حروف را از آن استخراج کنید.
• [num_char]  : تعداد حروفی که قصد استخراج آن‌ها را دارید.

مثال کار با تابع RIGHT از تابع های متنی پر کاربرد در اکسل

در مثال زیر قصد داریم هفت حرف را از سمت راست رشته موجود در سلول A2 استخراج کنیم. در صورتی که بدانید تعداد حروف موردنظر برای استخراج چند عدد است، راحت‌تر می‌توانید از تابع RIGHT استفاده کنید. به این منظور فرمول را به شکل زیر می‌نویسیم:

1=RIGHT(A2,7)

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

در صورتی که تعداد حروف موردنظر برای استخراج را نمی‌دانید، بهتر است تابع RIGHT را در ترکیب با سایر توابع به کار ببرید. در مثال زیر برای استخراج نام خانوادگی یا نام سمت راست، ترکیبی از توابع RIGHT ،LEN و FIND را مانند زیر به کار برده‌ایم:

1=RIGHT(A2,LEN(A2)-FIND(" ",A2))

با اجرای این فرمول، ابتدا تابع FIND، محل قرارگیری اولین فاصله را در رشته موجود در سلول A2 پیدا می‌کند. این تابع محل قرارگیری فاصله را مشخص خواهد کرد و اگر فاصله‌ای در رشته وجود نداشته باشد، خطا می‌دهد.

سپس تابع LEN، طول کل رشته موجود در سلول A2 را نشان می‌دهد و بعد محل قرارگیری اولین فاصله را از آن حذف می‌کند. حالا تابع RIGHT، حروف موجود در سمت راست رشته را بر اساس طول به‌دست آمده به کمک تابع LEN، استخراج می‌کند.

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

معرفی تابع MID در اکسل

تابع MID با دریافت موقعیت به‌خصوص و تعداد حروف، زیر رشته‌ای را از درون رشته دیگر استخراج می‌کند. به بیان دیگر با استفاده از این تابع و با مشخص کردن حرف شروع و تعداد حروف موردنظر، رشته‌ای را به راحتی پیدا و استخراج کنید.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

فرمول این تابع به شکل زیر نوشته می‌شود:

1MID(text,start_num,num_chars)

آرگومان‌های این فرمول نیز به شرح زیر هستند:

• Text  : متن یا عددی که می‌خواهید حروف آن را استخراج کنید.
• Start_char  : موقعیت عددی حرف اولی که می‌خواهید حروف بعد از آن را استخراج کنید.
• Num_chars  : تعداد حروفی که قصد دارید از رشته استخراج کنید.

مثال کار با تابع MID، یکی از توابع متنی کاربردی در اکسل

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

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

• از حرف ششم تا شش حرف بعدی
• از حرف ششم تا ۱۱ حرف بعدی
• عدد منفی به جای حرف آغازین که به خطا منجر می‌شود
• استفاده از عدد صفر به عنوان تعداد حروف موردنظر برای استخراج که در نتیجه چیزی نشان داده نشده
• استفاده از عدد منفی به جای تعداد حروف موردنظر برای استخراج که به خطا منجر شده
• عدد شروع صفر که به خطا منجر شده
• نوشتن متن موردنظر به طور مستقیم در فرمول

آشنایی و روش استفاده از تابع LOWER

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

فرمول این تابع به شکل زیر نوشته می‌شود:

1LOWER(text)

تنها آرگومان این فرمول یعنی Text  ، همان متنی است که قصد دارید حروف آن را تغییر دهید. شما می‌توانید متن را مستقیما درون فرمول بنویسید یا آن را در سلول دیگری قرار داده و به آن ارجاع دهید.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

مثال کار با تابع LOWER

در مثال زیر، چند جمله را که در چهار حالت حروف بزرگ، حروف مناسب، حروف کوچک و حروف مناسب جمله هستند، با هم مقایسه کرده‌ایم.

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

بررسی تابع متنی PROPER

تابع PROPER، حروف رشته دریافتی را با حروف مناسب می‌نویسید. به بیانی دیگر این تابع رشته را گرفته و اولین حرف هر کلمه آن را با حروف بزرگ انگلیسی و باقی را با حروف کوچک می‌نویسید. فرمول تابع PROPER به شکل زیر نوشته می‌شود و بسیار ساده است:

1PROPER(text)

تنها آرگومان این تابع (Text) برای وارد کردن رشته یا متنی است مکه قصد تغییر دادن حروف آن را دارید. شما می‌توانید متن را به طور مستقیم درون فرمول بنویسید یا آن را در سلول دیگری قرار داده و سپس به آن ارجاع دهید.

مثال کار با تابع متنی PROPER

در نمونه زیر، متنی را با کمک تابع PROPER به حروف متناسب نوشته و سپس آن را برای مقایسه در کنار متون با حروف بزرگ و کوچک قرار داده‌ایم.

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

معرفی تابع متنی UPPER در اکسل

این تابع برعکس تابع LOWER است و تمام حروف رشته دریافتی را به حروف بزرگ انگلیسی تبدیل می‌کند. البته اگر عددی درون رشته باشد، قالب بندی آن بدون تغییر باقی خواهد ماند. فرمول این تابع را در ادامه می‌بییند:

1UPPER(text)

برای استفاده از تابع UPPER کافی است متن یا رشته مربوطه را به طور مستقیم به جای آرگومان Text   بنویسید یا آن را درون سلول دیگری قرار داده و سپس به آن ارجاع دهید.

نحوه کار با تابع UPPER

تابع UPPER نیز مانند سایر توابع متنی مربوط به حروف در اکسل تنها با یک آرگومان کار می‌کند. در مثال زیر می‌بینید که حروف یک جمله با استفاده از این تابع، چگونه با جمله‌های دیگر که به حروف کوچک و مناسب نوشته شده‌اند، متفاوت هستند.

آشنایی با تابع REPT، یکی از تابع های متنی برای تکرار در اکسل

تابع REPT یک رشته متنی را چندین بار در خروجی نشان می‌دهد. به بیانی دیگر می‌توانید یک متن خاص و تعداد تکرار موردنظر را به این تابع بدهید تا آن را در خروجی نشان دهد. فرمول این تابع به شکل زیر نوشته می‌شود:

1REPT(value1, [value2], …)

در این فرمول، Value1 نشان دهنده متن موردنظر برای تکرار و [value2] نشان دهنده تعداد دفعات تکرار است.

مثال کار با تابع REPT در اکسل

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

معرفی تابع TEXT، از آشناترین توابع متنی در اکسل

نرم‌افزار اکسل معمولا برای کار با انواع اعداد به کار می‌رود. در بیشتر مواقع هنگام کار با این نرم‌افزار با اعداد سر و کار دارید. با این حال موقعیت‌هایی وجود دارند که نیاز است اعداد را به متن تبدیل کنید و در این شرایط است که پای تابع TEXT یکی از پرکاربردترین تابع های متنی در اکسل به میان می‌آید. در ادامه بیشتر با این تابع و کاربردهای آن آشنا می‌شویم.

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

فرمول تابع TEXT و موارد استفاده پایه

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

1TEXT(value, format_text)

آرگوما‌ن‌های این تابع نیز به شرح زیر هستند:

• Value  : مقدار عددی که قرار است به متن تبدیل شود. می‌تواند عدد، تاریخ، ارجاع به سلول حاوی مقدار عددی یا تابع دیگری باشد که در خروجی یک مقدار عددی یا تاریخ را نشان می‌دهد.
• Format_text  : قالب بندی موردنظر برای اعمال روی متن. این آرگومان به شکل کد قالب نوشته شده در دو «علامت نقل قول» ("") وارد می‌شود. مانند: "mm/dd/yy"

تابع TEXT در تمام نسخه‌های اکسل، حتی قبل از اکسل ۲۰۰۷ نیز موجود است. معمولا از فرمول تابع TEXT در شرایط زیر استفاده می‌شود:

• برای نمایش اعداد در قالبی که خواندن آن راحت‌تر باشد یا بیشتر به درد مخاطب بخورد.
• نمایش تاریخ در قالب بندی خاص
• ترکیب اعداد و تاریخ‌ها با متن یا حروف خاص

به عنوان نمونه اگر بخواهید تاریخی را از سلول A2 دریافت کرده و آن را قالب سنتی تاریخ به شکل «January 1, 2016» بنویسید، امکان به کار بردن فرمول TEXT به شکل زیر را خواهید داشت:

1=TEXT(A2, "mmmm d, yyyy")

نتیجه نهایی شبیه به تصویر زیر خواهد شد.

توجه داشته باشید که تابع TEXT در اکسل، مقادیر عددی را به یک رشته متنی تبدیل می‌کند. در نتیجه شما امکان استفاده از نتیجه این تابع در سایر محاسبات را نخواهید داشت. در چنین شرایطی می‌توانید مقدار اصلی را نگه داشته (به روش مخفی کردن در اکسل) و آن‌ها را در سایر فرمول‌ها به کار ببرید.

کدهای قالب برای استفاده از در توابع متنی در اکسل مانند TEXT

همان‌طور که در قسمت قبل دیدیم، فرمول تابع TEXT در اکسل بسیار ساده است. تنها بخش کمی سخت‌تر آن مربوط به کدهای قالب می‌شود که برای نمایش اعداد در قالب و شکل دلخواه به کار می‌روند. این تابع معمولا بیشتر کدهای قالبی که در قالب بندی‌های عددی اکسل استفاده می‌شوند را پشتیبانی می‌کند.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

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

به علاوه می‌توانید هرکدام از حروف زیر را نیز در کد قالب بگذارید و به همان شکلی که هستند نشان داده خواهند شد:‌

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

همچنین برای محاسبات تاریخ در اکسل، امکان به کار بردن کدهای قالب زیر را نیز خواهید داشت.

کد d برای نمایش روز هفته یا ماه:

• مثال: «d» برای نمایش عدد یک یا دو رقمی بدون صفر اول آن (۱ تا ۳۱)
• مثال: «dd» نمایش اعداد دو رقمی با امکان نمایش صفر اول عدد (۰۱ تا ۳۱)
• مثال: «ddd» نمایش مخفف سه حرفی روزهای هفته به زبان انگلیسی (Mon تا Sun)
• مثال: «dddd» برای نشان دادن نام کامل روز هفته (شنبه تا جمعه)

کد m برای نمایش ماه در صورتی که بخشی از یک تاریخ باشد:

• مثال: «m»  برای نمایش عدد یک یا دو رقمی بدون صفر اول آن (۱ تا ۱۲)
• مثال: «mm» نمایش اعداد دو رقمی با امکان نمایش صفر اول عدد (۰۱ تا ۱۲)
• مثال: «mmm» نمایش مخفف سه حرفی روزهای ماه به زبان انگلیسی (Jan تا Dec)
• مثال: «mmmm» برای نشان دادن نام کامل ماه (فروردین تا اسفند)

کد y برای نمایش سال:

• مثال: «yy» نمایش اعداد دو رقمی (۰۱ به معنی ۱۴۰۱ یا ۰۲ به معنی ۱۴۰۲)
• مثال: «yyy» نمایش اعداد چهار رقمی (مانند ۱۴۰۱ یا ۱۴۰۲)

کد h برای نمایش ساعت:

• مثال: «h» برای نمایش اعداد یک یا دو رقمی بدون صفر در اول آن‌ها (۱ تا ۲۴)
• مثال: «hh» نمایش اعداد دو رقمی با امکان نوشتن صفر در اول آن‌ها (۰۱ تا ۲۴)

کد m برای نمایش دقیقه وقتی به عنوان بخشی از تاریخ استفاده شود:

• مثال: «m» نمایش اعداد دو رقمی بدون نمایش صفر در اول عدد (۱ تا ۶۰)
• مثال: «mm» نمایش اعداد دو رقمی با امکان نمایش صفر اول عدد (۰۱ تا ۶۰)

کد s برای نمایش ثانیه:

• مثال: «m» نمایش اعداد دو رقمی بدون نمایش صفر در اول عدد (۱ تا ۶۰)
• مثال: «mm» نمایش اعداد دو رقمی با امکان نمایش صفر اول عدد (۰۱ تا ۶۰)

کد AM/PM برای نمایش زمان:

در صورت نمایش تاریخ با قالب ۱۲ ساعته، از AM برای نمایش زمان‌های پیش از ظهر و از PM برای زمان‌های پس از ظهر استفاده می‌شود.

در ادامه چند مثال مختلف برای نمایش تاریخ در اکسل را می‌بینیم.

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

مثال‌های کار با تابع متنی TEXT در اکسل

تا به این مرحله متوجه شده‌ایم که تابع TEXT یکی از توابع متنی در اکسل است که استفاده محدودی دارد. به این دلیل که امکان تغییر دادن مقادیر نمایش داده با تغییر قالب بندی سلول در اکسل (Format Cell) وجود دارد.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

اما این مشکل زمانی حل می‌شود که تابع TEXT را در ترکیب با سایر توابع اکسل به کار ببرید. در ادامه چند مثال مختلف را برای درک بهتر این موضوع می‌بینیم.

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

معمولا هنگام تهیه گزارش یا انجام محاسبات آماری با اکسل، نه تنها به محاسبه درصد، مجموع و موارد مشابه نیاز دارید، ممکن است همچنین بخواهید به کاربران توضیح دهید که هریک از این اعداد چه معنایی دارند. در چنین شرایطی می‌توانید از ترکیب تابع CONCATENATE در اکسل برای پیوند متن و عدد و تابع TEXT برای نمایش عدد یا تاریخ به شکل دلخواه کمک بگیرید.

فیلم آموزش داشبورد مدیریتی اکسل و گزارش گری حرفه ای در Excel در فرادرس

کلیک کنید

تصور کنید قصد دارید مجموع نهایی را بر اساس قیمت واحد موجود در سلول A2، تعداد در B2 و مقدار تخفیف در سلول C2، محاسبه کنید و به این منظور فرمول را مانند نمونه زیر می‌نویسید:

1=A2*B2*(1-C2)

حالا برای اینکه کاربران به درستی متوجه شوند که این عدد چیست، می‌خواهید آن را در کنار جمله‌ای مانند «قیمت=» قرار دهید. به علاوه قصد نمایش دادن نماد ارزی موردنظر، جداکننده هزارگان و دو رقم اعشار را نیز دارید.

برای انجام این کار، محاسبه بالا را درون اولین آرگومان تابع Text قرار دهید و قالب بندی موردنظر را نیز در دومین آرگومان با کدهای قالب مشخص کنید. سپس فرمول TEXT را به کمک علامت «امپرسند» (&) یا تابع CONCATENATE به رشته موردنظر (قیمت=) پیوند دهید. در نهایت فرمول شما چیزی شبیه به نمونه زیر خواهد شد:

1="قیمت= "&TEXT(A2*B2*(1-C2), "$###,###.00")

یا

1= CONCATENATE("قیمت= ",TEXT(A2*B2*(1-C2), "$###,###.00"))

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

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

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

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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

به عنوان مثال برای نمایش اعداد هفت رقمی با وجود صفر پشت آن‌ها‌، می‌توانید فرمول زیر را به کار ببرید. در این مثال عدد اصلی درون سلول A2 نوشته شده است:

1=TEXT(A2,"0000000")

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

سخن پایانی

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

در اکثر مواقع ورودی توابع متنی اکسل تنها یک آرگومان است که به رشته مربوطه اشاره دارد. به عنوان نمونه برای کار با تابع UPPER که تمام حروف انگلیسی را به حروف بزرگ تبدیل می‌کند، فقط باید متن موردنظر را در فرمول بنویسید.

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