هزار و یک شب اکسل - شب سی و پنجم استخراج کلمات از یک رشته Extracting Words from a String

[امين اسماعيلي]

به نام خداوندگار ایران زمین
با درود

Extracting Words from a String

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

Extracting the first word of a string

برای استخراج اولین کلمه در یک رشته که با اسپیس از کلمه بعدی جدا شده مثل نام و نام خانوادگی از تابع Left استفاده میکنیم و تابع Find برای پیدا کردن این فاصله .

=LEFT(A1,FIND(" ",A1)-1)

این فرمول ما،تکست ما قبل اولین فاصله رو برای ما برمیگردونه که در یک سل مانند A1 وارد شده باشد.این تابع اگر که تکست ما فاصله ای در اون نباشه با ارور مواجه میشه به خاطر اینکه شامل یک تک کلمه است. یک فرمول که یه ذره پیچیده تر باشه این مشکل رو حل میکنه بله درسته تابع IFERROR :

=IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)

اگر شما دارین از ورژن های پایین تر اکسل استفاده میکنین که این تابع رو نداره میتونین فرمول رو به طریق زیر اصلاح کنین :

=IF(ISERR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))

Extracting the last word of a string

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

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1, " ","*",LEN(A1)-LEN(SUBSTITUTE(A1, " ","")))))

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

=IFERROR(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1, " ","*",LEN(A1)-LEN(SUBSTITUTE(A1, " ",""))))),A1)

اگر هم دارین از ورژن های قدیمی استفاده میکنین بدین صورت میشه :

=IF(ISERR(FIND("",A1)),A1,RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1, " ","*",LEN(A1)-LEN(SUBSTITUTE(A1, " ",""))))))

Extracting all except the first word of a string

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

فرمول زیر محتویات سل A1 رو بدون اولین کلمه را برای ما برای ما بر میگرداند .

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

مثلا اگر داشته باشیم Amin Esmaeili Shirazi جواب میشه Esmaeili Shirazi
مثل تموم فرمول ها ی بالا اگر کلمه ما یک کلمه باشه یعنی فاصله ای در آن نباشه با مشکل مواجه میشه پس :

=IFERROR(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),A1)

و در ورژن های پایین میشه :

 
=IF(ISERR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))

خب دوستای گلم شب همگیتون شیک و مجلسی ، برم که فک کنم شیرازی بودن اومد سراغم تا شبی دیگر و هزار و یک شب اکسلی دیگر بدرود

اخطار: این یک موضوع قدیمی است به دلیل قدیمی بودن موضوع، ممکن است برخی فایل های ضمیمه به درستی کار نکنند. لطفا در صورت عدم ضرورت، از بالا آوردن موضوعات قدیمی خودداری نمایید.

[امين اسماعيلي]

با درود مجدد
خب رفتم بخوابم حیفم اومد که ازتون کار نکشم تنبلااااااااااااااااااااا ااا البته شوخی بودا
فرض کنید توی یه ستون ما یه سری اسامی داریم حالا شما انگلیسی بنویسین . که ممکنه شامل نام و نام خانوادگی و یا اسم وسط هم باشن مانند Franklin L. Jakson . حالا میخواهیم که این اسامی در سه ستون مجزا بر اساس نام و نام خانوادگی و نام وسط تفکیک بشن . ستون B مثلا first name , ستون C نام وسط شخص , ستون D نام خانوادگی. فقط یه مورد ممکنه یه سری از اسامی نام وسط نداشته باشن . ممکنه یه کلمه ای باشن . همه این ایرادا باید گرفته بشن. راهنمایی مشکلتون تو نام وسط هستش چون اولی و اخری رو تو تمرین بالا با هم مرور کردیم . یال بچه ها
از جایی کپی برداری نکنین که کشتمتون

[mokaram]

ممنون از زحمات شبانه روزی شما استاد اسماعیلی
برا حالتی که فامیلی دو قسمتی باشه مثل حسین خانی ، قوام آبادی راه حلی هست؟

[امين اسماعيلي]

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

[mokaram]

با درود
یعنی میخوی چیکارش کنی

میخوام فقط فامیلی را بده دیگه

[~M*E*H*D*I~]

این مواردی که محمد رضا سوال کرد راهی نداره ، من خودم همیشه نام و نام خانوادگی رو با یک علامت ویژه مینویسم که در صورت نیاز راحت جدا بشه ، علی ایحال کلا پیشنهادم اینه که تو کاراتون فیلدای نام و نام خانوادگی جدا باشه ، والا موارد خاص مثل مثالای محمد رضا باید دستی انجام بشه

[امين اسماعيلي]

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

[امين اسماعيلي]

با درود
وال موندم تو کار خودم به خدا . نخواستیم خودمون جواب میدیم

مثلا تو سل A1 داریم یه اسم سه کلمه ای مثل Amanda M. esmaeili حالا میخوایم اسم اول Amanda بیاد تو سل B1 و M. اسم وسط بیاد تو C1 و esmaili باید تو D1 خب به ترتیب تو B1 و C1 و D1 فرمول های زیر رو بنویسین . در ضمن اگر یه تک اسم بنویسین تو فرمول های زیر ما اینطور در نطر گرفتیم که این میتونه اسم کوچیک باشه یا اسم بزرگ چون نمیدونیم پس تو هر دوش میزاریمش

=IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)

=IF(LEN(B1&D1)+2>=LEN(A1),"",MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1&D1)-2))

=IFERROR(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),A1)

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

دیگه هم تکلیف نداریم . دیگه هم نگین شیرازیااااااااااااااااااا ااا

[Behnam]

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