سبد خرید
0

سبد خرید شما خالی است.

توابع Left و Right در اکسل

تابع left اکسل و تابع right اکسل
۴.۴/۵ - (۱۲ امتیاز)

حتما تا حالا براتون پیش اومده که بخواید روی یک سری داده تغییراتی ایجاد کنید. مثلا یک تعداد کاراکتر رو حذف کنید، کاراکتری خاص رو به داده ها اضافه کنید. عبارتی رو در سلول ها جایگزین عبارت جدید کنید و … همه این کارها رو در اکسل می تونیم با توابع متنی انجام بدیم. توابع متنی یا Text Function توابع بسیار بسیار قدرتمندی هستن و اگر خیلی خوب به اونها مسلط باشیم و بتونیم با هم ترکیب کنیم، می تونیم کارهای خیلی مفید و حرفه ای روی داده هامون انجام بدیم. در این مقاله میخوام به تشریح تابع Left اکسل و تابع Right اکسل بپردازم.

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

ساختار تابع Left و Right

Text: سلول یا عبارتی که میخوایم یک تعداد کاراکتر رو ازش جدا کنیم.

[Num_Chars]: تعداد کاراکتر که باید باقی بمونه.

  • همونطور که می بینید آرگومان دوم داخل براکت [ ] هست. این به این معنی هست که میتونیم این آرگومان رو مشخص نکنیم. در صورتی که خالی بذاریم این آرگومان رو، اکسل بصورت پیشفرض ۱ در نظر میگیره. یعنی یک کاراکتر رو جدا میکنه.
  • اگر این آرگومان رو بیشتر از تعداد کل کاراکترهای موجود یک عبارت بذاریم، کل اون عبارت به عنوان خروجی نمایش داده میشه.

به نمونه زیر دقت کنید. از عبارت موجود در سلول A1 میخوام کلمه “اکسل پدیا” رو جدا کنم. برای این کار از تابع Left استفاده میکنم. تعداد کاراکتر کلمه “کسل پدیا” برابر هست با ۹، پس مطابق شکل ۱ فرمول زیر رو می نویسیم:

=LEFT (A1,۹)

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

شکل ۱- جدا کردن قسمتی از عبارت موجود در یک سلول با استفاده از تابع left اکسل

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

 

تابع  Left و Right و کلا توابع متنی، قابلیت ترکیب بالایی دارند و اگر به خوبی بر اونها مسلط باشیم و بتونیم ترکیب کنیم، کارهای جالبی میتونیم انجام بدیم. این قابلیت ترکیب در خصوص آرگومانهایی که عددی هستند خیلی ویژه تر و قدرتمند هست.

آرگومان دوم این دو تابع عددی هست و از همین موضوع میخوایم برای ترکیب استفاده کنیم و چند نمونه مسئله رو با هم حل کنیم.

جدا کردن N کاراکتر آخر یک سلول یا یک رشته

فرض کنید در داده های زیر میخوایم تاریخ تولد رو جدا کنیم. همونطور که میبینیم تاریخ های تولد تعداد کاراکتر ثابتی ندارند پس باید الگویی پیدا کنیم که بر اساس اون بتونیم فرمول نویسی کنیم. در داده های نشان داده شده در شکل ۲، تاریخ تولد از – به بعد هست پس باید مکان – رو پیدا کرده و از اون به بعد رو از عبارت موجود در سلول جدا کنیم.

برای پیدا کردن مکان – همونطور که قبلا گفتیم باید از تابع find استفاده کنیم. این تابع عددی به ما میده که نشان دهنده مکان – هست.(شکل ۲)

پیدا کردن مکان – در سلول

شکل ۲- پیدا کردن مکان – در سلول

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

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

=Len (A1)

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

شکل ۳- محاسبه تعداد کل کاراکترهای یک سلول

حالا کافیه که تعداد کل کاراکترها رو از مکان – کم کنیم تا تعداد کاراکترهای تاریخ تولد در بیاد. مثلا برای ردیف اول، تعداد کل کاراکترها ۱۴ تاست، مکان – هم پنجمین کاراکتر هست. یعنی ۵-۱۴ که برابر است با ۹ میشه تعداد کاراکترهای تاریخ یعنی ۲۳/۱/۱۳۶۵.

حالا کافیه که عدد بدست آمده یعنی (تعداد کل – مکان -) رو در تابع right قرار داده و از انتهای سلول به این تعداد جدا کنیم.

تفکیک تاریخ از رشته متنی با استفاده از right

شکل ۴- تفکیک تاریخ از رشته متنی با استفاده از تابع right اکسل

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

=RIGHT ( A2 , LEN (A2) FIND ( “-” , A2 ))

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

 

جدا کردن کاراکترهای یک سلول تا قبل از یک کاراکتر خاص با استفاده از تابع left

فرض کنید یک سری داده داریم از نام و نام خانوادگی که با Space از هم جدا شدن. میخواهیم بخش نام رو از عبارت جدا کنیم. برای این کار اول باید مکان فاصله (Space) رو پیدا کنیم و بعد با تابع Left قسمت نام رو جدا کنیم.

=FIND (” “ , A2)

پیدا کردن مکان فاصله " " در یک رشته متنی برای استفاده در تابع left

شکل ۵- پیدا کردن مکان فاصله ” ” در یک رشته متنی

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

=LEFT (A2 , B2)

جدا کردن اسم از رشته متنی نام و نام خانوادگی با استفاده از تابع left

شکل ۶- جدا کردن اسم از رشته متنی نام و نام خانوادگی با ترکیب تابع left اکسل و تابع right اکسل

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

=LEFT (A2 , FIND ( ” ” , A2 ))

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

 

همونطور که در شکل ۷ می بینید در ستون B اعدادی که از تابع متنی بیرون اومدن جمع زده شده و نتجه ۰ هست. (چون ظاهرا عدد هست اما مقدار عددی نداره).

در ستون C این خروجی داخل یک تابع Value قرار گرفته و خروجی فرمول، به عدد تبدیل شده و قابلیت جمع پذیری داره که در سلول C8 نمایش داده شده.

تبدیل خروجی توابع متنی به عدد

شکل ۷- تبدیل خروجی توابع متنی به عدد

توابع متنی روی رشته های از جنس تاریخ و زمان

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

تفکیک کردن اجزاء یک رشته زمانی

شکل ۸- تفکیک کردن اجزاء یک رشته زمانی

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

این کار رو به دو روش میتونیم انجام بدیم:

  • استفاده از توابع مخصوص این کار یعنی Hour, Minute, Second. حتما مقاله مربوط به محاسبات زمان در اکسل رو مطالعه کنید.
  • تبدیل رشته زمانی مورد نظر به متن و استفاده از توابع متنی.

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

همچنین پیشنهاد میکنم مقالات زیر رو هم نگاه کنی:

دانلود فایل مثال تابع Left اکسل

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

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

  • ارسال به ایمیل

Des

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

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

دیدگاه کاربران
  • حسین 13 مرداد 1400 / 6:01 ب.ظ

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

  • محمد حسین صابری 19 بهمن 1399 / 8:32 ب.ظ

    سلام جناب چراغی عزیز و خانم خاکزاد گرامی
    خیلی خیلی از مطالبتون ممنونم
    واقعا کارگشا بود
    یک سوال از خدمتتون داشتم
    من یک big data دارم که زمان انجام خرید به صورت زیر اومده
    ۱۲/۱/۲۰۱۰ ۸:۳۴
    و میخام ساعت و تاریخ رو از یکدیگر جدا کنم و در دو سلول قرار بدم
    چه پیشنهادی دارین ؟؟ لازمه که بگم تمام داده ها لاتین هستن

  • علیرضا 29 مهر 1399 / 10:21 ق.ظ

    سلام وقت بخیر.ممنون از مطالب مفید سایتتون.
    من یه سوال داشتم. حدود ۸۰۰۰ تا شماره موبایل دارم . میخواستم شماره های ۹۱۲ شو جدا کنم.
    چطوری میتونم اینکارو کنم؟
    ممنون میشم راهنمایی کنید

    • سامان چراغی 1 آبان 1399 / 5:14 ب.ظ

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

  • شایان 8 اردیبهشت 1399 / 8:50 ب.ظ

    با سلام و خسته نباشی
    با توجه به توضیحات بالا، می خواستم دو سوال که نتیجه آنها یکسان هست را بپرسم:
    ۱- در یک ردیف حدود ۵۰۰ کارکتر دارم، چگونه میتوان با یک بار فرمول نویسی آنها را ۱۰تا۱۰تا جدا کرده و در ستون های بعدی تقسیم کنم ؟
    ۲- چگونه هر ۱۰تا کارکتر یک علامت , در میان آنها ایجاد کنم؟

    • حسنا خاکزاد 8 اردیبهشت 1399 / 9:23 ب.ظ

      اگر داده ها در یک سلول هستن، از تابع mid میشه استفاده کرد
      اگر منظورتون ۵۰۰ سلول هست و هر ده تا میخواید اینگار و بکنید، ترکیب offset با توابع دیگه جواب میده
      برای اضافه کردن , هم flash fill جواب میده و هم تابع

      یک مثال از یک نمونه داده بزنید، که جزئیاتش مشخص بشه

      • شایان 9 اردیبهشت 1399 / 7:52 ب.ظ

        سلام
        بله ۵۰۰ کاراکتر در یک سلول وجود دارد. مانند (qwertyuiopasdfghjklzxcvbnm)
        ۱- چگونه میتوان با یک بار فرمول نویسی ۱۰تا۱۰تا جدا نمود؟ ( توجه داشته باشید نمیخواهم مدام به صورت دستی با تابع mid جدا نمایم. یعنی فقط یک بار فرمول نوشته بشه و سپس در ستون های بعدی کپی گردد)
        ۲- با توجه به اینکه این ۵۰۰ کاراکتر کاملا به هم چسبیده هستند و نمیخواهم به صورت دستی مابین آنها علامت , اضافه نمایم لذا موفق نشدم با flash fill آنها را با علامت , جدا نمایم. مگر اینکه یکبار به صورت دستی ۱۰تا۱۰تا علامت را اضافه نمایم. که بنده راه بهتری لازم دارم.
        با تشکر

        • حسنا خاکزاد 9 اردیبهشت 1399 / 10:30 ب.ظ

          درود
          این فرمول رو به پایین درگ کنید:

          میتونید همین فرمول رو به یک , هم وصل کنید

  • نقدی 25 فروردین 1399 / 6:44 ب.ظ

    با سلام
    من یک اکسل دارم که در هر سل باید به اندازه کاراکتر کد بنویسم. کدها به این شکل هستند GM3 L8 NZ$ . هر سل دو تا فاصله بین کارکترها هست که باید محاسبه شود. محاسبه هم براساس اعداد تعلق گرفته به هر حرف یا کاراکتر هست. ( مثلا عبارت بالا به این شکل میشه:۲۶-۱۴- -۸-۱۲- -۳-۱۳-۷۷-۳۰، اعدادهم به روش دیگری در این سل محاسبه میشوند و باید حداقل ۱۰ باشند ، سپس به این شک ادامه پیدا میکند: (دو به دو با هم جمع میشوند : ۳+۰=۳ و هر جای خالی نیز که معادل ۲۷ =۲+۷=۹ هست، حداقل یکی در نظر گرفته میشود ) ۸-۵-۹-۸-۳-۹-۳-۴-۵-۳ و همنظور الی اخر : ۴-۸-۳-۷-۸ و ۴-۲-۶ و در نهایت ۸۴ که معادل ان تعداد جای خالی را مشخص میکند) میتونید راهنمایی بفرمائید از طریق فرمول یا کامند خاصی من این کار را میتونم انجام بدم؟ ایا اعداد معادل هر حرف یا کاراکتر تعریف شده هستند یا باید هر کدام جدا تعریف شوند؟

    • حسنا خاکزاد 25 فروردین 1399 / 8:07 ب.ظ

      درود
      خیلی واضح مشخص نیست خواستتون
      فقط میتونم بگم تعداد کاراکتر رو تابع Len بهتون میده
      تابع mid هم میتونه تفکیک انجام بده
      اینکه هر حرف معادل یک عدد باشه، داریم توی اکسل ولی کد اسکی رو میده و با تابع code بدست میاد. ولی معادل اعدادی که شما میخواید نیست

      • نقدی 26 فروردین 1399 / 1:42 ق.ظ

        بله، درست میفرمایید، کدها اسکی نیست، و برای حروف و کاراکترها تعریف شده، میخوام ببینم چطور میتونم این محاسبات را انجام بدم، یا اینکه دوعدد که مربوط به یک حرف هست و چطور جمع بزنم، میدونم باید از sum استفاده کنم و چند تا شرط ولی نمیدونم چطور

        • حسنا خاکزاد 26 فروردین 1399 / 10:40 ق.ظ

          میتونید جدولی درست کنید از اینکه هر حرف معادل چه کاراکتری هست
          بعد با mid تفکیک و با vlookup فراخوان یکنید و بعد با sum جمع بزنید

  • علی 26 بهمن 1398 / 11:18 ب.ظ

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

    • حسنا خاکزاد 27 بهمن 1398 / 9:29 ب.ظ

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

  • رضا 9 آبان 1398 / 10:09 ق.ظ

    سلام
    من یک قبض آب طراحی کردم و الان میخوام فرمول های محاسباتی رو با اعداد واقعی (بدون اعمال عملگرها) در قسمت پایین قبض بنویسم، و اونو پرینت بگیرم (برای بایگانی میخوام) راه حلی هست؟ (که هر کس خواست محاسبه کنه کلیه محاسبات رو ببینه)
    مثلاٌ ۴۸۱۲۰۰۰=۱۲۰۰*۴۰۱۰:میزان آب بها
    ۴۳۳۰۸۰= ۹%*۴۸۱۲۰۰۰=:مالیات بر ارزش افزوده
    ۳۵۷۶۹۲۰۰ =۸۹۲۰*۴۰۱۰:خدمات دفع فاضلاب

    • سامان چراغی 10 آبان 1398 / 9:15 ق.ظ

      سلام
      این قسمت رو در یک سلول و با استفاده از عملگر & میتونید ایجاد کنید. به عنوان مثال فرض کنید در محاسبه آب بها عدد ۴۰۱۰ در سلول A1 محاسبه شده و نتیجه آنجا قرار دارد، عدد ۱۲۰۰ یک عدد ثابت هست و عدد ۴۸۱۲۰۰۰ در سلول A2 قرار داده شده، حالا کافیه سلولی که میخواید این توضیحات رو نمایش بده به اینصورت فرمول نویسی بشه:

  • Hamid 30 مهر 1398 / 10:05 ب.ظ

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

    • سامان چراغی 1 آبان 1398 / 10:24 ق.ظ

      سلام و تشکر
      اگر فقط نتیجه رو میخواید منتقل کنید، سلول رو کپی و با استفاده از Paste Special گزینه Value رو بزنید که نتیجه فرمول منتقل بشه.
      اگر قصد دارید فرمول رو منتقل کنید ابتدا سلول رو منتقل کنید و بعد به ابتدای آدرس سلول های هر آرگومان آدرس شیت رو هم اضافه کنید که اطلاعات اولیه رو از شیت اول بخونه.

  • خادم 31 فروردین 1398 / 2:23 ب.ظ

    با سلام-تشکر از مهندس چراغی-آموزش بسیار کاربردی بود.

  • محمد 11 دی 1397 / 10:08 ق.ظ

    با سلام می خواستم بدونم حالا اگر متن سه بخشی بود بخش وسط رو چطور جدا کنیم مثلا (محمد چگینی ۱۰۰۰) چگینی رو چطور جدا کنیم

    • حسنا خاکزاد 11 دی 1397 / 10:37 ق.ظ

      درود بر شما
      ابزار Flash Fill خیلی کمک میکنه برای تفکیک حالت های متنوع.
      https://excelpedia.net/flash-fill/

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

  • Saeed Haeri 29 آذر 1397 / 4:51 ب.ظ

    با سلام. عالی بود استاد! کامل و کافی. متشکرم.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان