تابع جدا کردن عدد از متن

1
admin این مطلب رو نوشت. 13 آوريل 2014

از این تابع برای جدا کردن عدد از متن استفاده می شود . یعنی اگر در یک سلول داسته باشیم : علی23محمد8متین3تنت3م خروجی تابع برابر خواهد بود با : 33823 کافی از تابع Seperate استفاده کنیم . لينك دانلود: Separate_v.1.2.rar

به نام خداوند جان و خرد

18 نظرات
0
admin این مطلب رو نوشت. 14 آوريل 2014

سلام

بابت تابع Seperate خیلی ممنون، البته من یک تشکر دیگر بابت کتاب حسابداری مدیریت پیشرفته دکتر حسین سجادی به شما بدهکار هستم

تفکیک اعداد، علائم نگارشی و حروف الفبا که به صورت یک رشته "String" به هم پیوسته در یک خانه "Cell" اکسل نوشته شده است از دقدقه‌های هر حسابدار و حسابرسی بوده و هست.

من خودم بسته به چیدمان و ترکیب کاراکترها "Character" از روشهایی متنوعی همچون بکاربردن امکان text to columns گرفته تا تعریف توابع جدید در VB استفاده میکنم ولی شخصا روشی استاندارد که مورد پذیرش همه باشد و از همه مهمتر عملیات تفکیک را در هر شرایطی بدون اشتباه و با دقت کافی انجام دهد سراغ ندارم و در اکثر موارد بناچار با استفاده از روشهایی که سراغ دارم یک بار اعداد را به بهترین نحو ممکن تفکیک کرده و بعد جهت اصلاح مواردی که به درستی تفکیک نشده‌اند کل داده‌ها را بررسی می‌کنم که در برخی موارد واقعاً زمان‌بر است که باعث شده همواره در پی یافتن راه مناسبتری باشم.

با توجه به اینکه هدف موسسه از ایجاد این انجمن گسترش دامنه استفاده از نرم افزار اکسل در حسابرسی است سعی میکنم در ادامه پست روشهایی رو که برای تفکیک اعداد از متن سراغ دارم به صورت قدم به قدم توضیح بدهم البته امیدوارم دیگران هم راه‌هایی را که سراغ دارند در این پست ضمن ارائه مثال توضیح بدهند.

آخرین ویرایش 27 اوت 2014

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 15 آوريل 2014

]من چندبار یه این مشکل برخورد کردم و برای تفکیک اعداد از متن خصوصاً شرح اسناد (مثلاً جداکردن شماره فاکتورها یا شماره چکها از شرح) به صورت دستی جدا کردم یعنی یک ستون کنار ستون شرح ایجاد کردم و اعداد را یا تایپ کردم یا copy و paste که واقعا برای خسته کننده بود.

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

آخرین ویرایش 27 اوت 2014

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 15 آوريل 2014

[size=6]منم قبلا با این مشکل برخود کردم وامصیبتاااااااااااااااااااااااااااااااااااااااااااااااااا دست اخر با استفاده از فرمولهای Left و Right مشکل رو حل کردم

این فایل اکسل آقای ayfarhesab جالبه فرمول Seperate فقط در فایل ایشون وجود داره و برای استفاده از این فرمول باید این فایل اجرا شود و Enable Macros

البته من با همین روش خودم مشکل رو حل میکنم.

فرض کنید در سل A1 عبارت زیر رو داشته باشیم: علیخانی 1393 تالار گفتگوی تخصصی موسسه حسابرسی هوشیارممیز برای استخراج شماره 1393 فرمول زیر پیشنهاد میکنم: [center][/center] اینم فایل نمونه[/size]

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 16 آوريل 2014

سلام

استفاده از تابع Mid نسبت به ترکیب تودرتوی توابع left و Right بهتر است توصیه میکنم از این تابع استفاده کنید.

ساختار این تابع به شرح زیر است: [left](MID(text, startnum, numchars=[/left] [left](تعداد کاراکترهای که قصد دارید جدا کنید , کاراکتری را که قصد دارید عملیات جداسازی از آن آغاز شود را انتخاب کنید , خانه مبدا را انتخواب کنید)mid=[/left] فرض کنید در خانه C8 به عنوان شرح سند داشته باشید:

[h]چک شماره 557 مورخ 1393/01/30 بابت قرارداد 100557 مربوط به اسفند ماه آقای کرم خانی[/h]

و قصد داریم شماره چک را از متن چدا کنیم برای این منظور تابع MID را به صورت زیر در خانه‌ای که قصد داریم عمل جدا سازی در آن انجام شود مینویسیم. [left](MID(C8,10,3=[/left] که پاسخ 557 خواهد بود.

این روش یکی از ساده ترین روشها است البته در صورتی که تعداد خانه‌هایی که قصد داریم در آن عملیات جداسازی را انجام دهیم زیاد باشد توصیه میکنم هریک از بخشهای startnum و numchars را نیز از یک خانه دیگر فراخوانی کنید. به عبارت دیگر اگر در مثال بالا برای تابع MID یک ستون جدید ایجاد نموده‌اید برای درج اعداد مربوط به هریک از بخش های startnum و numchars نیز یک ستون جدید ایجاد کنید.

مثلا در مثال قبلی من برای این منظور تابع MID را در خانه F8 درج کنم و startnum و numchars را به ترتیب از خانه‌های E8 و D8 فراخوانی کنم تابع MID به صورت زیر خواهد بود:

[left](MID(C8,ٍE8,D8=[/left]

که اکنون قادر هستم به راحتی مقدار هریک از بخشهای startnum و numchars را عدد دهی کنم.

در صورت ترکیب این تابع با توابعی مثل FIND و... میتوان سرعت و دقت انجام عملیات را افزایش داد.

و در صورتی که بخواهید پاسخ استخراج شده خاصیت عددی داشته باشد باید تابع MID را درون تابع VALUE تعریف کنید.

آخرین ویرایش 27 اوت 2014

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 16 آوريل 2014

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

فقط گفتی چطوری با FIND سرعت کار رو بالا ببرم؟

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 20 آوريل 2014

سلام من همین چند دقیقه پیش تست کردم جواب داد

ولی با FIND نشد یعنی قاطی شد!! به قول 007 کمک

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 21 آوريل 2014

سلام

اول از همه باید از ayfarhesab تشکر کنم که این عنوان را ایجاد کردند و امیدوارم در تکمیل بحث شرکت کنند

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

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

جهت آشنایی با این روش پیشنهاد میکنم مطالب زیر را مطالعه بفرمایید

Extract_Numbers.zip

Use FIND And MID Function For Extract From String

http://www.excel-bytes.com/extracting-phone-fax-numbers-in-excel

آخرین ویرایش 27 اوت 2014

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 22 آوريل 2014

سلام

فایل Extract Numbers.zip تست شد جواب میده

فرمولهای به کار رفته ساده هستند ولی نتیجه جالبه مشد یه کاری کرد که فرمول FIND کمتر تکرار کرد سعی میکنم تهیه کنم و برات بفرستم

راستی چرا در تابع IFERROR مقدار 32768 برگشت داده شده؟؟؟؟؟؟

آخرین ویرایش 27 اوت 2014

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 22 آوريل 2014

با سلام مجدد و کسب اجازه از KaramKhani

ورژن 2 از فایل Extract Numbers را تهیه کردم که جهت مطالعه و بررسی تقدیم مینمایم :-)

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

Extract Numbers V2.zip

آخرین ویرایش 27 اوت 2014

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 23 آوريل 2014

با سلام خدمت دوستان و همكاران گرامي و تشكر از همه بابت اطلاعات فوق العاده خوب در خصوص اكسل در فايل جناب كرمخاني استفاده از find رو به طريق ديگري اضافه نمودم كه اميد وارم دوستان ايرادات و انتقادات وارده را بفرمايند.

ExtractNumbersV3.xlsx

آخرین ویرایش 27 اوت 2014

به نام خداوند جان و خرد

0
admin این مطلب رو نوشت. 24 آوريل 2014

سلام

با بهت همکاری آقای ayfarhesab و Alikhani و 100707 ممنون این همکاری و تعامل بین اعضا همان چیزی است که موسسه به خاطرش این تالار رو ایجاد کرده.

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

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

  1. Seperate (این تابع توسط آقای صابر (ayfarhesab) معرفی شد و جزء توابعی است که کاربران حرفه‌ای Excel برای رفع نیازهای خود در نرم افزار Excel ایجاد می‌کنند، که امیدوارم درادامه این پستها این چگونگی نوشتن این تابع نیز شرح داده شود)
  2. Left این تابع قادر است مقادیر مشخصی از کاراکترهای مندرج در یک "Cell" را استخراج کند
  3. Right این تابع کاربردی مشابه با کاربرد تابع Left دارد با این تفاوت که عمل جداسازی را از سمت راست انجام میدهد
  4. Mid که ترکیب دو تابع Left و Right بحساب می‌آید
  5. IFERROR که مقادیر خطا را برگشت می‌دهد
  6. FIND که با استفاده از آن می‌توان مقادیر مشخصی را در یک محدود مشخص جستجو کرد
  7. MIN که مقدار مینیمم یک دسته از مقادیر عددی را مشخص میکند
  8. LEN که عمدتا برای شمارش تعداد کاراکترهای مشخص شده استفاده می‌شود
  9. Search که فقط نام برده شده و کاربردی مشابه با تابع FIND دارد که امیدوارم دوستان با مطالعه این پستها و پیگیری مطالب با این توابع آشنا شده در کارهای روزمره خود از آنها استفاده نمایند چراکه هدف این تالار چیزی جز آشنا کردن آنها با این توابع و کاربردهای آنها نیست.

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

آخرین ویرایش 27 اوت 2014

به نام خداوند جان و خرد

0
100557 این مطلب رو نوشت. 27 اوت 2014

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

samir karamkhani

0
100557 این مطلب رو نوشت. 30 اوت 2014

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

=Separator1(S)

که اعداد موجود در متن را جداسازی منماید. این تابع برای زمانی که در متن مورد نظر فقط یک عدد وجود داشته باشد مناسب می‌باشد.
=Separator2(S)

که ضمن جداسازی اعداد به ازای هر کاراکتر حذف شده یک خط تیره "-" قرار میدهد استفاده از این تابع برای زمانهایی که در متن مورد نظر بیش از یک عدد وجود دارد توصیه می‌گردد.
همان طور که گفته شد اگر از تابع Separator2 استفاده شود کلیه کاراکترهای غیر حرفی به " - " تغییر خواهد کرد.
مثلا اکر در شرح سند متن زیر نوشته شده باشد:
موسسه حسابرسی هوشیار ممیز در سال 1378 در اداره ثبت شرکتها به ثبت رسیده است و از سال 1382 تا کنون از معتمدین سازمان بورس اوراق بهادار بوده است نتیجه به صورت زیر خواهد بود:
---------------------------------1378-----------------------------------------------1382-----------------------------------------------------
مسلما وجود این همه خط فاصله آزاردهنده است من نیز موافقم ولی راستش را بخواهید موفق نگردیدم تابع را بگونه‌ای برنامه ریزی کنم که این خطوط فاصله را حذف نماید البته جهت رفع این مشکل نیز به زودی راه حلی خواهیم یافت، لیکن تا آن زمان دو راه حل به شرح زیر پیشنهاد می‌گردد:


1. استفاده از توابع موجود در اکسل جهت جداسازی اعداد که به عنوان در پیوست بعدی به عنوان نمونه با استفاده از توابع MID، LEN، MIN و FIND ضمن حذف خطوط فاصله دو عدد موجود در سلول مربوطه را از یکدیگر تفکیک می‌نماییم.
2. استفاده از حقه ساده‌ای آقای زهدی، ایشان پیشنهاد کردند ستونی را که نتیجه تابع در آن درج گردیده است را Copy کرده و Past special values نماییم سپس با استفاده از کلیدهای ترکیبی Ctrl+H ابزار Find and Replace را فراخوانی نماییم و هر دو خط فاصله را با یک خط فاصله Replace کنیم و این حرکت را تا زمانی که بین هر عدد تنها یک خط فاصله باقی بماند ادامه دهیم و هرگاه بین اعداد مربوطه تنها یک خط فاصله باقی ماند با استفاده از ابزارهایی مثل Text to columns یا دیگر توابع اعداد را تفکیک نماییم
با اینکه توضیح راه حل آقای زهدی زمان برتر بود و اجرای آن به ظاهر سخت تر است باید اعتراف کنم در عمل راه حل ایشان به مراتب از راه حلی که من استفاده کردم ساده تر بود. البته راه حلهای فوق مطلق نیستند و مسلما ره حلهای دیگر نیز وجود دارد که به ذهن ما خطور نکرده است و امیدواریم دیگر همکاران و مراجعین به تالار در این رابطه پیشنهادات خود را ارائه نمایند. فایل نمونه

samir karamkhani

0
mehrdad7 این مطلب رو نوشت. 17 ژوئن 2015

مسلما وجود این همه خط فاصله آزاردهنده است

با تشکر از تمامی فعالان در بحث تابع جدا کردن عدد از متن بابت ارایه مطالب مفیدتون با سلام جهت از بین بردن خط فاصله از تابع replace و قرار دادن فاصله به جای خط و بعد از ان استفاده از تابع trim جهت حذف فاصله های زاید به این صورت میتوان این خط فاصله ها از بین برد فواصل بین اعداد یک فاصله space باشد

پروردگارا ببخش مرا که آنقدر حسرت نداشته هایم را خوردم ، شاکر داشته هایم نبودم . . .

0
100557 این مطلب رو نوشت. 18 ژوئن 2015

سلام

ممنون میشوم اگر در این خصوص یک فایل اکسل نیز به عنوان نمونه ضمیمه نمایید.

من فکر میکنم منظور شما replace (جایگذین) کردن کاراکترهای خط تیره با کاراکتر دیگری بوده است که برای این منظور باید از تابع SUBSTITUTE استفاده کنید.

آخرین ویرایش 18 ژوئن 2015

samir karamkhani

0
mehrdad7 این مطلب رو نوشت. 21 ژوئن 2015

با سلام با عرض معذرت اشتباه نوشتاری بود منظور از تابع replace همان تگ جایگزینی بود (در قسمت editing) تابع جایگزین کردن همان تابعی که معرفی کردید (تابع SUBSTITUTE )

دانلود

پروردگارا ببخش مرا که آنقدر حسرت نداشته هایم را خوردم ، شاکر داشته هایم نبودم . . .

0
100557 این مطلب رو نوشت. 21 ژوئن 2015

سلام

ممنون بابت ارسال فایل نمونه

اگر بتوانیم این تابع را در VB تعریف کنیم و با فرمول قبلی ترکیب کنیم به یک فرمول تقریا کامل دست خواهیم یافت

samir karamkhani

0
100557 این مطلب رو نوشت. 28 ژوئيه 2015

سلام

در مورد جدا کردن عدد از متن در پستهای قبل چند راه حل معرفی شد که هرکدام به نوبه خود مزایا و معایبی داشت خیلی از دوستان ازم پرسیدن خودم از کدام یک از روشهای بالا استفاده میکنم در جواب این دوستان باید عرض کنم من خودم از همه روشهای بالا و چند روش دیگر بسته به شرایط و خصوصیات متنی که قرار است از آن اعدادی را استخراج کنم استفاده میکنم.
امروز فرمول ساده دیگری معرفی خواهم کرد که به تازگی با آن برخورد کرده ام این فرمول که در VBA تعریف شده نیر ممکن است در برخی شرایط مفید باشد این فرمول مزایا و معایبی به شرح زیر دارد:
مزایا:
1. اگر در متن درون سلول (خانه اکسل) بیش از یک رشته اعداد باشد دو رشته را به صورت تفکیک شده نشان میدهد.
مثلا اکر در شرح سند متن زیر نوشته شده باشد:
موسسه حسابرسی هوشیار ممیز در سال 1378 در اداره ثبت شرکتها به ثبت رسیده است و از سال 1382 تا کنون از معتمدین سازمان بورس اوراق بهادار بوده است نتیجه به صورت زیر خواهد بود:
1387 1382
بله مشکل حذف ------ ها حل شده
2. خط تیره را به عنوان کاراکتر عددی می‌پذیرد و به همراه اعداد از رشته های غیر عددی تفکیک می‌کند.
معایب:
1. اکسل کاراکتر / را به عنوان یک کاراکتر عددی شناسایی نمی‌گند در نتیجه تاریخهایی مثل 2015/07/28 را به صورت 20150728 نشان می‌دهد.
2. کاراکتر . را به عنوان یک کاراکتر عددی شناسایی نمیکند در نتیجه اگر در متن عددی مثل 139.54 داشته باشین خروجی به صورت 13954 خواهد بود
برای استفاده از این فرمول کافی است به محیط اکسل VBA وارد شوید، یک Module ایجاد کنید، متن زیر را در آن وارد کنید فایل اکسل را ذخیره کنید و با بستن اکسل VBA از این فرمول با نام AlphaNumericOnly استفاده کنید.


Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 32, 45, 48 To 57:
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function


نمونه فایل در گوگل درایو

AlphaNumericOnly
TechOnTheNet.com

آخرین ویرایش 01 اُكتبر 2016

samir karamkhani


263 نفر از 683 عضو ما 1162 بار در مباحثه ها نظر ارسال کرده اند.