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

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

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

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

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

کلیک کنید

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

1MID(text, start_num, num_chars)

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

• Text: به عنوان رشته متنی اصلی
• Start_num:‌ محل قرارگیری اولین حرف موردنظر برای استخراج
• Num_chars: تعداد حروف موردنظر برای استخراج

توجه داشته باشید که نوشتن تمام این آرگومان‌ها در فرمول ضروری است. مثلا برای استخراج چهار حرف از رشته متنی موجود در سلول «A2»، با شروع از نوزدهمین حرف، فرمول زیر را می‌نویسیم:

1=MID(A2,19, 4)

نتیجه چیزی شبیه به تصویر مثال زیر خواهد شد.

۵ نکته درباره تابع MID در اکسل

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

• توجه داشته باشید که خروجی تابع MID، همیشه رشته متنی است، حتی اگر رشته فرعی استخراج شده، فقط شامل اعداد باشد. اگر قصد دارید از نتیجه تابع MID برای محاسبه استفاده کنید، باید این مورد را جدی بگیرید. برای تبدیل خروجی این تابع به عدد می‌توانید آن را در ترکیب با تابع «VALUE» استفاده کنید.
• اگر عدد موجود در آرگومان Start_num، بزرگ‌تر از طول رشته اصلی باشد، فرمول تابع MID یک رشته خالی را در خروجی نشان می‌دهد.
• اگر آرگومان Start_num، کمتر از یک باشد، خطای Value بروز می‌کند.
• اگر عدد آرگومان Num_char، کمتر از صفر باشد (عدد منفی)، فرمول، خطای Value نشان می‌دهد. همچنین اگر این آرگومان برابر با صفر باشد، یک رشته خالی در خروجی نمایش داده می‌شود.
• در صورتی که مجموع اعداد موجود در آرگومان‌های Start_nim و Num_char، بیشتر از مجموع کل طول رشته اصلی باشد، این تابع یک رشته فرعی را در خروجی نشان می‌دهد که از نقطه Start_num شروع شده و تا آخرین حرف ادامه دارد.

درک بهتر کارکرد تابع MID در اکسل با ذکر مثال

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

چطور نام و نام خانوادگی را در اکسل استخراج کنیم؟

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

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

کلیک کنید

اما این دو تابع در حال حاضر فقط روی آفیس 365 در دسترس هستند. به همین دلیل در ادامه بررسی می‌کنیم که جدا کردن نام و نام خانوادگی در اکسل به کمک تابع MID چگونه است.

استفاده از تابع MID برای استخراج نام

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

1=MID(A2,1,SEARCH(" ",A2)-1)

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

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

استخراج نام خانوادگی با تابع MID در اکسل

برای استخراج نام خانوادگی نیز فرمول زیر را می‌نویسیم:

1=TRIM(MID(A2,SEARCH(" ",A2),LEN(A2)))

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

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

کلیک کنید

به همین دلیل، مجموع کل طول رشته اصلی را که به کمک تابع LEN به‌دست آمده است را در آرگومان Num_char، قرار می‌دهیم. همچنین می‌توانید به جای LEN، عددی را قرار دهید که مطمئن هستید بیشتر از هر نام خانوادگی‌ای خواهد بود، مثلا عدد «100» انتخاب مناسبی است. در نهایت تابع TRIM، فاصله‌های اضافی را حذف می‌کند و نام خانوادگی در خروجی نشان داده خواهد شد.

نحوه استخراج رشته فرعی بین دو جداکننده

مثال قبلی را در نظر بگیرید، اگر بین نام و نام خانوادگی، یک نام میانی نیز بود، چگونه باید آن‌ها را استخراج می‌کردیم؟

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

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

کلیک کنید

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

1SEARCH(" ",A2)+1

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

1SEARCH(" ",A2,SEARCH(" ",A2)+1)

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

1SEARCH (" ", A2, SEARCH (" ",A2)+1) - SEARCH (" ",A2)

در نهایت تمام این فرمول‌ها را همراه با تابع MID می‌نویسیم تا رشته فرعی موجود بین دو حرف فاصله را به‌دست آوریم:

1=MID(A2, SEARCH(" ",A2)+1, SEARCH (" ", A2, SEARCH (" ",A2)+1) - SEARCH (" ",A2)-1)

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

1MID(string, SEARCH(delimiter, string)+1, SEARCH (delimiter, string, SEARCH (delimiter, string)+1) - SEARCH (delimiter, string)-1)

مثلا برای دریافت رشته‌ای که بین یک «علامت ویرگول» (،) و یک علامت فاصله قرار دارد، فرمول را به شکل زیر می‌نویسیم:

1=MID(A2,SEARCH("، ",A2)+1,SEARCH("، ",A2,SEARCH("، ",A2)+1)-SEARCH("، ",A2)-1)

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

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

این مثال، نمایان‌گر استفاده‌ای خلاقانه از فرمول MID پیچیده در اکسل است که در ادامه بیشتر با آن آشنا می‌شویم:

• LEN:‌ دریافت طول کلی رشته
• REPT: تکرار یک حرف مشخص با تعداد دفعاتی که معلوم شده است
• SUBSTITUTE:‌ تعویض یک حرف با حرف دیگر
• MID:‌ استخراج یک رشته فرعی
• TRIM:‌ حذف فاصله‌های اضافه

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

1TRIM(MID(SUBSTITUTE(string," ",REPT(" ",LEN(string))), (N-1)*LEN(string)+1, LEN(string)))

شرح این فرمول را در ادامه می‌خوانیم:

• «String»‌، رشته اصلی متنی‌ای است که می‌خواهیم کلمه موردنظر را از آن استخراج کنیم.
• «N»، تعداد کلمات موردنظر برای استخراج است.

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

1=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (2-1)*LEN(A2)+1, LEN(A2)))

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

نحوه کارکرد فرمول

به طور خلاصه، این فرمول، هر کلمه موجود در رشته اصلی را همراه با تعدادی فاصله جمع کرده و سپس مجموعه «فاصله-کلمه-فاصله» موردنظر را پیدا و استخراج می‌کند، نهایتا فاصله‌های اضافه را برمی‌دارد. در ادامه به شکلی مفصل‌تر نحوه کارکرد این فرمول را بیان می‌کنیم.

در این فرمول، توابع SUBTITUTE و REPT، هر فاصله موجود در رشته را با چند فاصله جایگزین می‌کنند. همچنین تعداد فاصله‌های اضافه با طول کلی رشته اصلی که توسط فرمول LEN: SUBSTITUTE(A2," ",REPT(" ",LEN(A2)))   محاسبه شده، برابر است.

شما می‌توانید نتیجه میانی و کار در حال انجام را مانند سیارک‌هایی از کلمات درحال حرکت در فضا در نظر بگیرید که شبیه به نمونه زیر هستند:

1فاصله-کلمه1-فاصله-کلمه2-فاصله-کلمه3-...

این رشته بزرگ، سپس به‌جای آرگومان Text در تابع MID قرار می‌گیرد. در مرحله بعد با استفاده از عبارت (N-1)*LEN(A1)+1  ، روی موقعیت شروع رشته فرعی موردنظر (آرگومان Start_num) کار می‌شود. این محاسبه یا موقعیت اولین حرف کلمه موردنظر یا در بیشتر مواقع، محل قرارگیری برخی از حروف فاصله در جداسازی فضای قبلی را در خروجی نشان می‌دهد.

تعداد حروف موردنظر برای استخراج (آرگومان Num_char) ساده‌ترین بخش را به خود اختصاص می‌دهد. در این بخش، از طول کلی رشته اصلی را در نظر می‌گیریم که در واقع به شکل LEN(A2)   نوشته می‌‌شود. حالا رشته فرعی «فاصله-کلمه موردنظر-فاصله» به‌دست آمده است.

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

1=TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))

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

همچنین اگر رشته منبع شما شامل چندین فاصله بین کلمات و کلمات بزرگ . کوچک است، می‌توانید یک تابع TRIM درون هر تابع LEN قرار دهید تا فرمول شبیه به مثال زیر شود:

1=TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(TRIM(A2)))), (B2-1)*LEN(TRIM(A2))+1, LEN(TRIM(A2))))

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

چگونه کلمه‌ای دارای حروف خاص را در اکسل استخراج کنیم؟

برای انجام این کار باید تابع MID را به شکلی پیچیده‌تر و در ترکیب با توابع دیگری مانند TRIM ،SUBSTITUTE و FINF به کار ببرید.

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

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

1TRIM(MID(SUBSTITUTE(string," ",REPT(" ",99)),MAX(1,FIND(char,SUBSTITUTE(string," ",REPT(" ",99)))-50),99))

در نظر بگیرید که رشته اصلی درون سلول A2 قرار دارد و می‌خواهید رشته‌ای فرعی شامل علامت «@» یا آدرس ایمیل را از آن استخراج کنید. باید فرمول را به شکل نمونه زیر بنویسید:

1=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),MAX(1,FIND("@",SUBSTITUTE(A2," ",REPT(" ",99)))-50),99))

به همین شکل می‌توانید آدرس سایت (با کمک علامت WWW.) و موارد مشابه را نیز به راحتی استخراج کنید.

نحوه کارکرد این فرمول

مانند مثال قبلی، در این فرمول نیز توابع SUBSTITUTE و REPT هر فاصله موجود در رشته را به چند فاصله، به طور خاص، به 99 فاصله، تبدیل می‌کنند.

سپس تابع FIND، محل حرف موردنظر (در این مثال علامت @) را مشخص کرده و عدد 50 را از آن کم می‌کند. با انجام این کار، 50 حرف به عقب‌تر و به جایی اواسط مجموعه 99 فاصله‌ای قرار می‌گیرید که قبل از رشته فرعی حاوی حرف مشخص شده است.

تابع MAX برای شرایطی مورد استفاده قرار می‌گیرد که رشته فرعی موردنظر در ابتدای رشته اصلی باشد. در این اوضاع، نتیجه عبارت FIND()-50   عددی منفی خواهد بود و عبارت MAX(1, FIND()-50)   آن را با عدد «1»‌ جایگزین می‌کند.

سپس از آن نقطه شروع، تابع MID‌، تمام 99 حرف بعدی را گرفته و رشته فرعی موردنظر را همراه با تعداد زیادی فاصله، در خروجی می‌گذارد، چیزی شبیه به رشته: «فاصله-رشته فرعی-فاصله». در نهایت تابع TRIM طبق معمول کمک می‌کند از شر فاصله‌های اضافی خلاص شوید. توجه کنید که اگر رشته فرعی موردنظر برای استخراج، خیلی بزرگ است، باید اعداد 99 و 50 را با عددهای بزرگ‌تر مانند 1000 و 500 جابه‌جا کنید.

تابع SUBSTITUTE در اکسل چگونه کار می‌کند؟

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

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

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

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

کلیک کنید

همان‌طور که قبلا نیز گفتیم می‌توانید برای تبدیل خروجی متنی به عدد، فرمول MID را با تابع VALUE ترکیب کنید. این تابع برای تبدیل مقادیر متنی به عدد متناظر آن‌ها کاربرد دارد.

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

1=VALUE(MID(A2,7,3))

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

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

1=VALUE(MID(A2,SEARCH(":",A2)+1,SEARCH(":",A2,SEARCH(":",A2)+1)-SEARCH(":",A2)-1))

سخن پایانی

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

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