جستجو

ترفندهای کامپیوتری

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال‌ پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfandir
🔵 ارتباط با ادمین:
telegram.me/pctarfand_admin

خلاصه آمار

تبليغات

جدا کردن عدد از متن در اکسل + ماکرو

اکسل ماکرونویسی

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

برای اینکار دو حالت وجود دارد:

حالت اول) در همه سلول‌ها عدد یا حروف در یک جهت قرار داشته باشد مثلا در تمام سلول‌ها عدد در سمت راست باشد و اینکه محتویات سلول‌ها مثل ۱ش۲س بصورت ترکیبی نباشند. البته باید یکی از شرایط زیر را نیز دارا باشد:

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

۲- یا اینکه یکی از دو مورد عدد یا حروف درون تمام سلول‌ها تعداد کاراکتر یکسانی داشته باشد.

مثال ۱) در شکل زیر در همه سلول‌ها، اعداد در سمت چپ قرار دارند و ۴ رقمی هستند.

فرمول‌های زیر را به ترتیب در سلول B1 و C1 کپی کنید:

=LEFT(A1,4)

=MID(A1,5,LEN(A1)-4)

نحوه استفاده از توابع LEFT ،MID و LEN قبلا در اینجا ارائه شده است. پس از اعمال فرمول‌ها برای سلول‌های A1 تا A4، نتیجه زیر حاصل می‌شود.

 

مثال ۲) در شکل زیر در همه سلول‌ها، اعداد در سمت راست قرار دارند و ۴ رقمی هستند.

فرمول‌های زیر را به ترتیب در سلول B1 و C1 کپی کنید:

=RIGHT(A1,4)

=MID(A1,1,LEN(A1)-4)

نحوه استفاده از توابع RIGHT ،MID و LEN قبلا در اینجا ارائه شده است. پس از اعمال فرمول‌ها برای سلول‌های A1 تا A4، نتیجه زیر حاصل می‌شود.


حالت دوم) در حالتی مثل شکل زیر که تعداد کاراکترهای عدد و حروف درون سلول‌ها متفاوت و بصورت ترکیبی می‌باشند و همچنین فاصله بین متن و عدد در سلول‌ها متفاوت است، دیگر نمی‌توان از روش‌های حالت ۱ (بالا) استفاده کرد.

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

۱- با استفاده از فرمول نویسی

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

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

نتیجه آن در شکل زیر قابل مشاهده است.

۲- با استفاده از ماکرو

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

  • استخراج عدد از ترکیب

مطابق راهنمای درون فایل، کد ماکرو را اجرا کنید. کادر KutoolsforExcel ظاهر می شود.

محدوده مورد نظرتان را انتخاب کنید و روی Ok کلیک کنید تا نتیجه زیر حاصل شود.

 

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

  • استخراج حروف از ترکیب

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

=TextOnly(A1)

۳- با استفاده از افزونه Kutools for Excel:

نحوه استفاده از افزونه Kutools for Excel برای استخراج عدد و استخراج حروف در اینجا و اینجا مشاهده کنید.

منبع : extendoffice.com

 

برای مطلع شدن از جدیدترین مطالب سایت، کانال‌ پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:

          

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

آدرس ایمیل: tarfandha.blog@gmail.com

آی‌دی تلگرام: pctarfand_admin@

نظرات (۲۶)

  1. محمدمهدی سیدی

    سلام من خرید ماکرو انجام دادم اما صفحه ای برام نیومده لطفا فایل مورد نظر رو برام جیمیل کنید
    • پاسخ:

      سلام
      با تشکر از شما
      لینک برایتان ایمیل شد.
      موفق باشید.
  2. سلام یک سلول دارم که از وب نمایش قیمت میزاره ولی نمیخوام علامت $ کنار قیمت باشه باید چیکار کنم ؟
    • پاسخ:

      سلام
      یعنی بصورت آنلاین نمایش داده می شود؟
  3. سلام،
    میخواهم در یک ستون که هر فیلد آن مثلا یک عدد 20رقمی هست، 16 رقم اولی انرا نگه دارد و بعد از عدد شانزدهم هرچه هست پاک شود
    راهنمایی لطفا
    • پاسخ:

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

      =LEFT(A1,16)

  4. سلام اینقدر این اکسل توانایی داره که بعضی وقتها من انتظار دارم برام چای هم بریزه
    تشکر از حوصله و وقتی که دوستان بخرج میدهند.
  5. سلام
    ممنون از سایت عالی شما
    یه سوال ؟
    اون قسمتی که فرمول نوشتید رو در اکسل کپی میکنم ولی ایراد میگیره
    =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
    • پاسخ:

      سلام
      چه ایرادی؟
      چک کنید آیا با تغییر ویرگول ها به نقطه ویرگول مشکل حل می شود؟
  6. باسلام و خسته نباشید
    منم میخوام یه نمودار در اکسل رسم کنم اعدادمو وارد میکنم رسم نمودار انجام نمیشه .داده هام به این صورت هستند x=۰.۰-.۵-۱--۱.۵تا ۱۰
    y=1.98-1.99-2.01-2.05-2.09-2.14-2.20-2.25-3.0-6.0
    این داده هار وارد میکنم رسم میکنه ولی هر داده ای غیر از اینارا رسم میکنه ممنون میشه راهنمایی کنید با تشکر
    • پاسخ:

      سلام
      داده ها مشخص نیست.
  7. سلام
    من چند مورد در اکسل مشکل داشتم که شاید ربطی به این بحث نداشته باشه
    1- چطوری میشه در اکسل بین دو عدد متن نوشت؟
    مثلا بخواهیم بنویسیم « 20 تا 30 » به صورت « تا 30 20 » نوشته میشه و با هیچ راهی با جابجا کردن اعداد متن و... درست نمیشه. تنها راهش اینه که متن به عدد بچسبه مثل «20تا 30» که من نمی خوام اینجوری بشه.
    2- وقتی کنار سلول ها شماره ردیف داریم مثلا 500 تا و به عللی چند تا از ردیف های وسط‌و پاک می کنیم می‌خوام با یک دستور تمام شماره ردیف‌ها تا آخر درست بشه یا به صورت خودکار ردیف بشه آیا امکان چنین کاری هست؟
    با تشکر
    • پاسخ:

      سلام
      1- با استفاده از گزینه Right-to-Left در تب Home و قسمت Alignment مشکل حل می‌شود.
      2- اگر از اعداد معمولی استفاده کنید بعید می‌دانم بتوان به صورت خودکار اینکار را انجام داد. اما اگر از تابع Row استفاده کنید مشکل حل می‌شود.
      ()ROW=
      موفق باشید.
  8. علی منصوری

    با سلام و احترام
    من میخوام اعداد خاصی رو از یک سلول بردارم وقتی که از ماکرو استفاده میکنم تمام اعدادی که در سلول هستن در کنار هم بهم میده ولی من میخوام بین اعداد خط فاصله بذاره که بعد بتونم جداشون کنم یا یه کاری کنم فقط اعدد مورد نظر را خروجی داده شود آیا امکان این کار وجود دارد به طور مثال 3933 ردیف از این سلولها دارم که کارکترهای انها با هم مساوی نیستند شماره پیگیری عدد مورد نظر میباشد
    1-دریافت طی قبض شماره 1109 - POS‏ - شماره پیگیری: 123300968542
    2-صورتحساب رستوران 'رستوران نارنج - کرمان ' - شماره 83036 Pos - شماره پیگیری:124078773171
    3-صورتحساب 29073 پذیرش 29147 - POS شماره پیگیری:124509856605 ( THOMAS MANFRED KEIL )
    • پاسخ:

      سلام

      باید یک تابع جدید بسازید (SplitNum). برای اینکار از ماکروی زیر استفاده کنید.

      Function SplitNum(Stri As String)
      Dim xLen As Integer
      xLen = Len(Stri)
      SplitNum = ""
      For i = 1 To xLen
          If IsNumeric(Mid(Stri, i, 1)) Then
          SplitNum = SplitNum + Mid(Stri, i, 1)
          Else
      SplitNum = SplitNum + " "
      End If
      Next
         SplitNum = Replace(WorksheetFunction.Trim(SplitNum), " ", "-")
      End Function

      روش ساخت تابع و استفاده در مطلب بالا توضیح داده شده است.

      موفق باشید.

  9. سلام
    با استفاده از روش فرمول نویسی مثل اینکه محدودیت در متن داره و متن که یه مقدار طولانی تر میشه از تعداد اعداد استخراج شده کم میشه و نمیتونه به طول کامل همه اعداد رواستخراج کنه
    برای این موضوع چه راهی پیشنهاد میدید؟
  10. با سلام و با تشکر از مطالب خوبتان
    سوال من این است چطور با یک دستور می توانم متنی را در داخل پرانتز بنویسم
    مثال برای نوشتن 10000 به صورت حروفی داخل پرانتز (ده هزار)
    • پاسخ:

      سلام
      منظورتان این است که لیستی از اعداد دارید و می‌خواهید معادل آن‌ها به حروف به صورت خودکار درج شود؟
  11. سلام و عرض ادب
    نمودار خطی حاوی 1870 در دو ستون باید رسم کنم با داده های بسیار نزدیک به هم .اما نمیتونم.چند داده اول راخدمتتون ارسال می کنم امیدوارم راهنمایی بفرمایید.
    90.086385 399.237422
    90.086386 401.166106
    90.086387 403.094789
    90.086388 405.023472
    90.086389 406.952155
    90.086390 408.880838
    90.240486 410.809522
    90.267012 412.738205
    90.290848 414.666888
    • پاسخ:

      سلام
      مشکلتان چیست؟

  12. اگردریک لیست ودرهرسط تاریخ باشد وبخواهیم تاریخ راازمتن حذف کنیم چگونه اقدام کنیم .باتشکر
    • پاسخ:

      سلام
      مراحل مشابه مراحل گفته شده در مطلب فوق است. اگر مورد شما متفاوت است یک مثال بزنید.
  13. با سلام من میخوام 2 تا سلول در دو شیت مجزا را باهم مقایسه کنم و اگر در شیت اول سلول a با شیت دوم سلول a برابر بود سلول b شیت 2 را برابر با سلول b شیت اول بکنه
    راحتتر بگم
    من کارم طوری هستش که یک فایل اکسل برای من میفرستند تا من با مشترکین تماس بگیرم و بعد از تماس حاصل صحبت را در یکی از سلول های جلوی اسم مشترک مینویسم تا اینجا که مشکلی نیست ولی بعد از گذشت 3 ساعت دوباره یک فایل دیگه برای تماس به من میدهند که ممکنه قبلا باهاش تماس گرفته باشم و این دوباره کاری میشه و باعث مزاحمت مشترک میشه حالا میخوام سلول بعد از تکراری ها رو نتیجه صحبت قبلی رو بذاره مثلا یک مشترک به نام شجاعی حاصل تماس شده درخواست وام و بعد در لیست دیگه میخوان خود اکسل بگرده ببینه اگه سلول شجاعی برابر با سلول شجاعی دارای نتیجه بود همون نتیجه رو دوباره جلوی این یکی لیست هم بنویسه
    ببخشید اگه طولانی شد
    ممنونم
    • پاسخ:

      سلام
      به نظرم راحت ترین راه استفاده از تابع Vlookup است که در این مطلب آموزش داده شده است. مثلا اگر محدوده داده‌های شما در شیت اول A1:B4 باشد (در ستون A اسامی افراد و در ستون B نتیجه تماس باشد) و در شیت دوم در ستون A اسامی افراد باشد کافیست فرمول زیر را در سلول B1 بنویسید و در سایر سلول‌های ستون B اعمال کنید.

      =VLOOKUP(A1,Sheet1!$A$1:$B$4,2,FALSE)

      موفق باشید.
  14. سلام
    با تشکر از اقدامات شما
    مشکلی که برای اینجانب در اکسل به وجود آمده این است که
    در هنگام وارد کردن عدد در اکسل به عنوان مثال ۱ بعد از اینتر عدد به صورت ۰.۰۱ می شود به طوری که هر عددی که وارد می شود به صورت تقسیم به ۱۰۰ ظاهر می شود مثال
    ۱ می شود ۰.۰۱
    ۲ می شود ۰.۰۲
    خواهشمند است اینجانب را راهنمایی فر مایید

    • پاسخ:

      سلام
      در تنظیمات اکسل تیک گزینه Automatically Insert a Decimal Point را بردارید. تصویر راهنما
      موفق باشید.
  15. با سلام از زحمات بی دریغی که انجام می دهید ، یک سوال خدمتتان داشتم در اکسل قصد دارم یک تاریخ نوشته شده رو با روز دیگر در سلول دیگر جمع کنم  مثلا 94/07/28 رو با 7 روز دیگر جمع کنم که نتیجه میشه 94/08/04 مشکلات موجود از چه نمادی بین عداد استفاده کنم و طریقه فرمول نویسیش به چه صورت باشد ./

    ممنون و سپاس گذارم

    • پاسخ:

      سلام
      متاسفانه اکسل از تاریخ شمسی پشتیبانی نمی‌کند. روش حل این مشکل در این مطلب گفته شده است. در برخی سایت‌های معرفی در مطلب فوق‌الذکر، امکان اضافه کردن چند روز به یک تاریخ نیز وجود دارد.
      اما راه کلی اضافه کردن چند روز به یک تاریخ در این سایت و این سایت توضیح داده شده است.
      موفق باشید.
  16. با عرض سلام
    من یک رشته عددی در یک سل دارم میخواهم این رشته در یک سل خالی پنج رقم پتج رقم بوسله یک کارکتر خاص جدا بشن مثلا
    1233456789 در یک سل
    در یک سل دیگه بشه مثل نمونه زیر
    56789;12334
    • پاسخ:

      سلام
      با فرض اینکه عدد مورد نظر در سلول A1 است در سلول B1 و C1 دستور زیر را بنویسید:

      =MID(A1,1,5)

      =MID(A1,6,5)

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

      سلام
      با فرض اینکه این اعداد در ستون B قرار دارند از فرمول زیر در ستون C استفاده کنید:

      =IF(AND(ISERR(FIND("<",B1)),ISERR(FIND(">",B1))),B1,MID(B1,2,LEN(B1)))

      موفق باشید.
  18. سلام
    عالی بود ، فقط یه سؤال : می خوام داده های یک فایل اکسل را ( فقط یک شیت ) وارد فایل اکسل
    دیگری کنم به جز کپی کردن آیا در داخل برنامه می شه این کار را انجام داد ، با تمام قالب بندی صورت
    گرفته .
    با تشکر
    • پاسخ:

      سلام
      احتمالا راهنمایی ارائه شده در اینجا مشکل شما را حل می‌کند.
      موفق باشید.

ارسال نظر

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

۱- با توجه با اینکه نظرات خصوصی شما امکان نمایش در سایت را ندارد، بنابراین هنگام ارسال نظر، گزینه "" را انتخاب نکنید. چون تنها راه پاسخگویی به آن از طریق ایمیل شما است که با توجه به مشغله کاری فرصت ارسال پاسخ از طریق ایمیل وجود ندارد.

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

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی