close
دانلود فیلم
جدا کردن عدد از متن در اکسل
loading...

بیشتر بدانیم

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

نتایج زنده فوتبال جهان

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

رضا ولی پور بازدید : 315 دوشنبه 08 تير 1394 نظرات ()
در این مطلب نحوه تجزیه متن در اکسل بصورت عمومی اشاره شده است. برای یک حالت خاص، در یک سلول شامل ترکیبی از اعداد و حروف، چنانچه بخواهیم آن‌ها را از هم جدا کنیم همیشه نمی‌توان از روش‌های عمومی استفاده کرد. در ادامه مطلب با ما همراه باشید با نحوه این جداسازی آشنا شوید.

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

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

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

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

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

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

=LEFT(A1,4)

=MID(A1,5,10)

نحوه استفاده از توابع LEFT و MID قبلا در اینجا ارائه شده است. پس از اعمال فرمول‌ها برای سلول‌های 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)

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

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

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

کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic for applications window ظاهر می‌شود. در این پنجره از تب Insertگزینه Module را انتخاب نمائید. در پنجره جدید باز شده کدهای زیر را کپی کنید.

Sub GetNumbers()
'Updateby20140512
Dim RngAs Range
Dim WorkRngAs Range
Dim xValueAs String
Dim OutValueAs String
On Error Resume Next
xTitleId ="KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each RngIn WorkRng
    OutValue =""
    xValue = Rng.Value
    For i = 1To VBA.Len(xValue)
        If VBA.IsNumeric(VBA.Mid(xValue, i, 1))Then
            OutValue = OutValue & VBA.Mid(xValue, i, 1)
        End If
    Next
    Rng.Value = OutValue
Next
WorkRng.NumberFormat ="@"
End Sub

سپس دکمه doc-multiply-calculation-3 یا کلید F5 را فشار دهید. پس از این کار پنجره KutoolsforExcel ظاهر می‌شود.

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

 

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

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

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

Function TextOnly(pWorkRngAs Range)As String
'Updateby20140515
Dim xValueAs String
Dim OutValueAs String
xValue = pWorkRng.Value
For xIndex = 1To VBA.Len(xValue)
    If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1))Then
        OutValue = OutValue & VBA.Mid(xValue, xIndex, 1)
    End If
Next
TextOnly = OutValue
End Function

Ctrl+s را بزنید تا کد ذخیره شود و Mircosfot Visual Basic را ببندید. دستور زیر را در یک سلول تایپ کنید و در سایر سلول‌ها اعمال کنید.

=TextOnly(A1)

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

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

 

مطالب مرتبط
ارسال نظر برای این مطلب

نام
ایمیل (منتشر نمی‌شود) (لازم)
وبسایت
:) :( ;) :D ;)) :X :? :P :* =(( :O @};- :B /:) :S
نظر خصوصی
مشخصات شما ذخیره شود ؟ [حذف مشخصات] [شکلک ها]
کد امنیتی
دریافت و نصب فایل نگارش جدید

توجه !!!

  •  قبل از نصب نگارش جدید حتما از برنامه اصلی (app) خارج شوید.
  •  با ذخیره سازی پایگاه داده های سیستم از اطلاعات خود فایل پشتیبان (bak) بگیرید.
  •  پس از نصب نگارش در برنامه اصلی پایگاه داده را بروزرسانی کرده و آخرین تغییر در نگارشها را مطالعه نمایید.

اگر سیستم مدرسه به اینترنت پرسرعت متصل است:

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

با جایگزینی فایلها ( …Replace) به شما خواهد داد که با کلیک بر روی Yes to All  تمامی فایل های جدید نصب می شوند.

اگر به هر دلیلی نگارش ناقص دریافت گردد ( خاموش شدن سیستم,باز بودن برنامه اصلی و عدم نصب نگارش جدید و …) نگارش برنامه قدیمی بود و هنگام دریافت پیام ” نگارش جدیدی موجود نیست ” داده می شود. در این حالت تیک گزینه ” دریافت تنها در صورت وجود نگارش جدیدتر ” را برداشت و مجدد روی دریافت کلیک نمایید.

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

ابتدا فایل نگارش جدید برنامه مورد نظر را از ستون سمت چپ سایت دانلود کرده و بدون اینکه نام فایل را تغییر دهید(برای مشاهده نام و کاربرد فایل ها اینجا را کلیک کنید) از طریق “برنامه هلپر (Helper )” منوی “بروزرسانی سیستم” گزینه “دریافت و نصب فایل نگارش جدید سیستم مدرسه الکترونیک” را اجرا کرده و پس از تعیین مسیر فایل دانلود شده , روی تایید کلیک کنید.سیستم پیامی در رابطه با جایگزینی فایلها ( …Replace) به شما خواهد داد که با کلیک بر روی Yes to All  تمامی فایل های جدید نصب می شوند.

لازم به ذکر است که برای برنامه هلپر هم نگارش های جدیدی ارائه می شود اما  نسبت به برنامه اصلی خیلی کم نگارش هلپر تغییر می کند.

هلپر دانا

هلپر راهنماییهلپر توصیفی

 

 

 

آخرین نگارش ارائه شده برای برنامه هلپر مورد نظر را از ستون سمت چپ سایت دانلود کرده و در محل نصب برنامه کپی کنید.دقت نمایید در هنگام کپی کردن فایل برنامه هلپر بسته باشد و در جواب پیام سیستم مبنی بر جایگزینی فایل Helper.exe* گزینه Yes  را کلیک نمایید.

محل نصب برنامه:

برای مشخص کردن محل نصب برنامه ابتدا روی میانبر یا shortcute برنامه راست کلیک کنید روی Properties کلیک کرده و در پنجره باز شده در تب shortcute  ودر کادر Start in  می توانید مسیر نصب برنامه را ببینید.

تبلیغات
Rozblog.com رز بلاگ - متفاوت ترين سرويس سایت ساز
درباره ما
Profile Pic
اینجانب رضا ولی پور مدیر دبیرستان دوره اول روستای گنج آباد آموزش و پرورش ناحیه 2 ارومیه جهت آشنایی و راهنمایی همکاران گرامی در استفاده مناسب از برنامه مدارس این سایت را تقدیم می نمایم. امیدوارم با نظرات خود و شرکت در انجمن سایت ما را در رسیدن به اهدافمان یاری فرمائید.
اطلاعات کاربری
نام کاربری :
رمز عبور :
  • فراموشی رمز عبور؟
  • نظرسنجی
    مطالب این سایت چه اندازه به شما کمک نموده است؟





    آمار سایت
  • کل مطالب : 210
  • کل نظرات : 125
  • افراد آنلاین : 8
  • تعداد اعضا : 235
  • آی پی امروز : 397
  • آی پی دیروز : 481
  • بازدید امروز : 947
  • باردید دیروز : 1,064
  • گوگل امروز : 212
  • گوگل دیروز : 304
  • بازدید هفته : 2,812
  • بازدید ماه : 16,838
  • بازدید سال : 118,629
  • بازدید کلی : 386,196
  • کدهای اختصاصی