نحوه جدا کردن اعداد از متن در اکسل – آموزش ۴ روش آسان

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

جداسازی متن از عدد با استفاده از Flash Fill

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

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

کلیک کنید

قصد داریم عدد و دپارتمان کارمند را از هم جدا کنیم و در سلول‌های جداگانه نمایش دهیم. در روش اول از Flash Fill استفاده می‌کنیم. این قابلیت اکسل با توجه به عملکرد کاربر الگویی را می‌یابد و آن را تکرار می‌کند. بنابراین اگر به طور دستی ابتدا این کار را انجام دهیم، اکسل می‌تواند الگوی آن را بیابد و برای سایر سلول‌ها تکرار کند. در این مثال ابتدا عبارت «MKT» را در ستون B وارد می‌کنیم:

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

با این کار عبارت وارد شده در سایر سلول‌ها تکرار می‌شود. آیکونی در گوشه سلول‌های انتخابی نمایش داده می‌شود. با زدن روی این آیکون منوی باز می‌شود که کافیست روی گزینه آخر یا Flash Fill بزنید.

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

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

دقت کنید گاهی ممکن است Flash Fill نتواند الگوی صحیح را بیابد. بنابراین، نیاز است بیش از یک نمونه را به طور دستی وارد کنید تا اکسل بتواند الگو را راحت‌تر بیابد. توجه داشته باشید در روش Flash Fill، اگر محتوای سلول را در ستون A تغییر دهید، محتوای ستون B به طور خودکار تغییر نخواهد کرد.

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

مثال بخش قبل را دوباره در نظر بگیرید.

از جایی که تعداد حروف در اسم دپارتمان و تعداد ارقام در کد پرسنلی کارمندان یکسان نیست، می‌توان به تنهایی از توابع «LEFT» و «RIGHT» استفاده کرد با تغییر فرمول به شکل زیر بخش چپ نوشته جدا می‌شود.

=LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1)

بخش داخلی سلول از تابع FIND استفاده شده است. این تابع اعداد صفر تا ۹ را در سلول A2 می‌یابد و جایگاه آن رقم را برمی‌گرداند. اگر نتواند رقم را بیابد، خطای #VALUE!   نتیجه تابع خواهد بود. برای مثال در سلول A2 نتیجه تابع FIND به شکل زیر خواهد بود:

{#VALUE!,4,#VALUE!,#VALUE!,#VALUE!,6,#VALUE!,5,#VALUE!,#VALUE!}

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

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

{“”,4,””,””,””,6,””,5,””,””}

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

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

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

=MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")),100)

اگر محتوای سلول ابتدا با عدد شروع و با متن تمام می‌شد می‌توانید با ایجاد تغییراتی کوچک، فرمول را مجدد استفاده کنید برای این کار نیاز است به جای تابع MIN از تابع MAX استفاده کنید تا جایگاهی را که عدد تمام می‌شود، پیدا کنید. سپس با استفاده از تابع LEFT و MID می‌توانید عدد را از متن جدا کنید.

یادگرفتن اکسل با فرادرس

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

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

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

مجموعه فیلم‌های آموزش اکسل فرادرس

جداسازی متن از عدد با استفاده از VBA

استفاده از فرمولی که در بخش پیشین معرفی شد روش موثری برای جدا کردن اعداد از متن در اکسل است. اما اگر نیاز دارید به دفعات اعداد را از متن جدا کنید می‌توانید تابع دلخواه خود را در VBA اکسل بسازید. پیشتر در فرادرس در مطلب «آموزش VBA در اکسل – از صفر تا صد + فیلم رایگان» به طور مفصل راجع به VBA توضیح داده شده است. با استفاده از تابع دلخواه خود می‌توانید تنها با یک آرگمان متن را از عدد جدا کنید.

فیلم آموزش برنامه نویسی VBA برای خودکار کردن برنامه های Office در فرادرس

کلیک کنید

همچنین می‌توانید کد VBA این تابع را در یک ماکرو ذخیره کنید تا در تمامی فایل‌های اکسل شما در دسترس باشد. کد زیر تابع «GETNUMBER» را در اکسل ایجاد می‌کند، آدرس سلول مد نظر تنها آرگمان این تابع است. با اعمال این تابع تمامی اعداد موجود در آن سلول استخراج می‌شوند و تابع آن عدد را به عنوان خروجی برمی‌گرداند.

'This code will create a function that can separate numbers from a cell
Function GetNumber(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumber = Result
End Function

کد زیرنویس تابع مشابه «GETTEXT» را نشان می‌دهد. مشابه تابع قبلی این تابع یک آرگمان را به عنوان آدرس سلول مد نظر می‌پذیرد و محتوای متنی آن سلول را به عنوان خروجی برمی‌گرداند.

''This code will create a function that can separate text from a cell
Function GetText(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetText = Result
End Function

برای افزودن این کدها به اکسل طبق مراحل زیر عمل کنید:

• به سربرگ «Developer» بروید. اگر این سربرگ نمایش داده نمی‌شود، در تنظیمات نوار ابزار اکسل آن را اضافه کنید.
• روی آیکون ویژوال بیسیک بزنید.

• در ویرایشگر ویژوال بیسیک بخش «Project Explorer» در سمت چپ نمایش داده می‌شود در این بخش نام شیت و فایل شما نمایش داده خواهد شد. اگر این نامه را نمی‌بینید به سربرگ «View» بروید و در منو روی Project Explorer کلیک کنید.

• سپس روی نام شیت اکسلی کلیک کنید که قصد دارید تابع در آن وارد شود. با این کار یک ماژول با آن فایل اضافه خواهد شد.

• سپس روی ماژول اضافه شده دوبار کلیک کنید با این کار پنجره کد ماژول باز می‌شود.
• کد را در این بخش وارد کنید و ویرایشگر را ببندید.

با انجام مراحل بالا دو تابع GetNumber و GetText به اکسل افزوده می‌شود و می‌توانید از آن‌ها استفاده کنید. در تصویر زیر نتیجه استفاده از این تابع را می‌بینید.

فراموش نکنید بعد از افزودن کد در پنجره کد ماژول نیاز است فایل را با پسوند .xlsm به جای .xlsm ذخیره کنید.

۴. جدا کردن اعداد از متن در اکسل با Power Query

با استفاده از Power Query می‌توان تنها با چند کلیک اعداد را از متن در اکسل جدا کرد. پیشتر در مجله فرادرس در مطلب «Microsoft Power Query چیست و چه مزایایی دارد؟» به معرفی Power Query پرداخته‌ایم. برای استفاده از این قابلیت برای جداسازی عدد از متن،  باید داده‌ مد نظر را در جدول یا در یک رشته داده اسم‌گذاری شده وارد کرده باشید. در تصویر زیر مثالی که در بخش‌های قبلی استفاده کردیم در یک جدول تک ستونه آورده شده است.

برای جداسازی عدد از متن با استفاده از Power Query طبق مراحل زیر عمل کنید:

یکی از سلول‌های جدول ایجاد شده را انتخاب کنید و در نوار ابزار اکسل روی سربرگ «Data» بزنید.

در بخش «Get and Transform Data» روی گزینه «From Table Range» بزنید.

ویرایشگر Power Query باز می‌شود. در این ویرایشگر ستونی را که قصد دارید عملیات روی آن انجام شود، انتخاب کنید. در ویرایشگر Power Query به سربرگ «Transform» بروید.

روی گزینه «split Column» بزنید.

منوی باز می‌شود که در این منو گزینه «By Non-Digit to Digit» را انتخاب کنید. اگر داده‌ای دارید که ابتدا با عدد شروع می‌شود می‌توانید از گزینه «By Digit to Non-Digit» استفاده کنید.

خواهید دید که محتوای سلول‌ها در دو ستون عدد و متن تفکیک می‌شود.

در صورت نیاز می‌توانید اسم ستون‌های جدید جدول را تغییر دهید. حال در سربرگ «Home» روی «Close and Load» بزنید تا نتیجه در شیت اکسل شما بارگذاری شود.

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