چگونه یک عدد را از یک رشته در اکسل استخراج کنیم

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

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

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

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

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

برای استخراج یک عدد از انتهای رشته با استفاده از Flash Fill:

1. سلول کنار اولین سلول حاوی رشته های شما را انتخاب کنید.
   
2. عددی که در انتهای رشته ظاهر می شود را وارد کنید.
   
3. اگر تمام داده‌های شما در یک ستون پیوسته و بدون شکاف هستند، سلول را مستقیماً زیر صفحه‌ای که شماره را در آن وارد کرده‌اید انتخاب کنید.
   
4. اگر داده‌های شما در برخی ردیف‌ها شکاف دارند، اولین سلول و تمام سلول‌های زیر که می‌خواهید اعداد را از آنها استخراج کنید، از جمله هر ردیف خالی را انتخاب کنید.
   
5. مطبوعات Ctrl+E در ویندوز یا Cmd+E در مک برای اجرای ویژگی Flash Fill.
6. سلول های باقیمانده شما به طور خودکار بر اساس الگویی که اکسل مشاهده کرده است پر می شود. در بیشتر موارد، اعداد انتهای رشته به درستی استخراج می شوند.
   

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

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

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

1. در سلولی که می خواهید عدد استخراج شده ظاهر شود کلیک کنید.
2. فرمول زیر را وارد کنید و سلول حاوی رشته خود را جایگزین «B2» کنید:

   =VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE,ROW(INDIRECT("1:"&LEN(B2))),0))))

3. مطبوعات وارد.
4. عدد باید از رشته استخراج شود.
5. برای اعمال فرمول بر روی هر سلول باقیمانده، روی دستگیره کشیدن در گوشه سمت راست پایین سلول کلیک کنید.
   
6. روی سلول هایی که می خواهید فرمول در آن ها تکرار شود، به پایین بکشید.
   
7. ماوس خود را رها کنید و فرمول در سلول های انتخاب شده کپی می شود.
8. اکنون این سلول ها باید حاوی اعداد استخراج شده شما باشند.
   
9. هر سلولی که رشته ای برای استخراج از آن وجود نداشته باشد، یک خطا برمی گرداند. اگر می خواهید یک سلول خالی را برگردانید، می توانید از فرمول استفاده کنید:

   =IFERROR(VALUE(RIGHT(B2,LEN(B2)-MAX(IF(ISNUMBER(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1)=FALSE,ROW(INDIRECT("1:"&LEN(B2))),0)))),"")

10. اکنون هر سلول خالی یک فضای خالی برمی گرداند.
    

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

به عنوان مثال، AB5HG23 به 1، 2، 0، 4، 5، 0، 0 تبدیل می شود. سپس به دنبال بالاترین مقدار در این لیست می گردد، که موقعیت آخرین کاراکتر است که عدد نیست. در نهایت تمام کاراکترهای بعد از این نقطه را که اعداد انتهای رشته هستند برمی گرداند. سپس تابع VALUE این را دوباره به عدد تبدیل می کند. ساده است، درست است؟

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

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

برای استخراج یک عدد از ابتدای رشته با استفاده از Flash Fill:

1. سلول سمت راست اولین رشته ای را انتخاب کنید که دارای شماره ای است که می خواهید استخراج کنید.
2. عددی را که در ابتدای رشته ظاهر می شود وارد کنید.
   
3. اگر تمام داده های شما در یک ستون پیوسته و بدون شکاف هستند، سلول را مستقیماً در زیر انتخاب کنید.
   
4. اگر داده‌های شما دارای شکاف هستند، اولین سلول و تمام سلول‌های زیر که می‌خواهید اعداد را از آنها استخراج کنید، از جمله هر ردیف خالی را انتخاب کنید.
   
5. مطبوعات Ctrl+E در ویندوز یا Cmd+E در مک برای شروع Flash Fill.
6. سلول هایی که انتخاب کردید اکنون باید اعداد استخراج شده را نشان دهند.
   

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

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

برای استخراج یک عدد از ابتدای رشته با استفاده از فرمول:

1. سلولی را انتخاب کنید که می خواهید شماره استخراج شده در آن ظاهر شود.
2. فرمول زیر را وارد کنید و سلول حاوی رشته خود را جایگزین ‘B18’ کنید:

   =VALUE(LEFT(B18,MATCH(FALSE,ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))

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

   =IFERROR(VALUE(LEFT(B18,MATCH(FALSE,ISNUMBER(MID(B18,ROW(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1)),"")

نحوه استخراج اعداد از هر نقطه در رشته با استفاده از Flash Fill

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

برای استخراج اعداد از هر نقطه از یک رشته با استفاده از Flash Fill:

1. در سلول کنار رشته اول، اعداد موجود در رشته را به صورت دستی تایپ کنید.
   
2. اگر تمام داده های شما در یک ستون پیوسته است، سلول را مستقیماً در زیر انتخاب کنید.
   
3. اگر داده‌های شما دارای شکاف هستند، اولین سلول و تمام سلول‌های زیر که می‌خواهید اعداد را از آن استخراج کنید، از جمله هر ردیف خالی را انتخاب کنید.
   
4. مطبوعات Ctrl+E در ویندوز یا Cmd+E در مک برای شروع Flash Fill.
5. اکنون سلول ها باید با اعداد استخراج شده پر شوند.
   

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

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

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

1. سلولی را انتخاب کنید که می خواهید شماره استخراج شده در آن ظاهر شود.
2. فرمول زیر را وارد کنید و سلول حاوی رشته خود را جایگزین ‘B9’ کنید:

   =SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN(B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10)

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

   =IFERROR(SUMPRODUCT(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,ROW(INDIRECT("1:"&LEN(B9))),1))*ROW(INDIRECT("1:"&LEN(B9))),0),ROW(INDIRECT("1:"&LEN(B9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B9)))/10),"")

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

مهارت های اکسل خود را گسترش دهید

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

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