نحوه تقسیم نام‌ها در اکسل با Text to Columns

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

1. ستونی از نام‌های کامل را که می‌خواهید جدا کنید انتخاب کنید.
2. به برگه Data > گروه ابزار داده بروید و روی Text to Columns کلیک کنید.
3. در مرحله اول برنامه Convert Text to Columns Wizard، گزینه Delimited را انتخاب کرده و روی Next کلیک کنید.
4. در مرحله بعد، یک یا چند “جداکننده” را انتخاب کرده و روی Next کلیک کنید.در این مورد ما، قسمت‌های مختلف نام‌ها با فاصله از هم جدا می‌شوند، بنابراین ما این جدا کننده را انتخاب می‌کنیم.بخش پیش نمایش داده ها نشان می‌دهد که همه نام‌های ما به خوبی تجزیه شده‌اند.

   نکته. اگر با اسامی جدا شده با کاما و فاصله مانند Anderson، Ronnie سروکار دارید، سپس کادرهای Comma و Space را در قسمت Delimiters علامت بزنید و کادر انتخاب Treat consecutive delimiters را به عنوان یک انتخاب کنید (معمولاً به طور پیش فرض انتخاب می‌شود).

5. در مرحله آخر، فرمت داده و مقصد را انتخاب کرده و روی Finish کلیک کنید. قالب پیش‌فرض General در بیشتر موارد خوب کار می‌کند. به عنوان مقصد، بالاترین سلول را در ستونی که می‌خواهید نتایج را در آن خروجی بگیرید مشخص کنید (لطفاً به خاطر داشته باشید که با این کار همه داده‌های موجود بازنویسی می‌شوند، بنابراین حتماً یک ستون خالی انتخاب کنید).

انجام شد. نام، وسط و نام خانوادگی به ستون‌های جداگانه تقسیم می‌شود:

نام و نام خانوادگی را در اکسل با فرمول جدا کنید

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

نحوه جدا کردن نام و نام خانوادگی از نام کامل با فاصله

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

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

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

=LEFT(cell, SEARCH(” “, cell) – 1)

شما از تابع SEARCH یا FIND برای به دست آوردن موقعیت کاراکتر فاصله (” “) در یک سلول استفاده می‌کنید، که از آن 1 کم می‌کنید تا خود فضا حذف شود. این عدد به تابع LEFT به عنوان تعداد کاراکترهایی که باید استخراج شوند، از سمت چپ رشته شروع می‌شود.

فرمول دریافت نام خانوادگی

فرمول عمومی برای استخراج نام خانوادگی این است:

=RIGHT(cell, LEN(cell) – SEARCH(” “, cell))

در این فرمول، شما همچنین از تابع SEARCH برای پیدا کردن موقعیت کاراکتر فاصله استفاده می‌کنید، آن عدد را از طول کل رشته کم می‌کنید (با LEN برگردانده می‌شود ) و تابع RIGHT را برای استخراج این تعداد کاراکتر از سمت راست دریافت می‌کنید. رشته با نام کامل در سلول A2، فرمول‌ها به شرح زیر است:

دریافت نام :

=LEFT(A2,SEARCH(" ",A2)-1)

دریافت نام خانوادگی :

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

فرمول‌ها را به ترتیب در سلول‌های B2 و C2 وارد کرده و دستگیره پر را بکشید تا فرمول‌ها در ستون‌ها کپی شوند. نتیجه چیزی شبیه به این خواهد بود:

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

=RIGHT(A2, LEN(A2) - SEARCH("#", SUBSTITUTE(A2," ", "#", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))))

در اینجا توضیح سطح بالایی از منطق فرمول ارائه شده است: شما آخرین فاصله در نام را با یک علامت هش (#) یا هر کاراکتر دیگری که در هیچ نامی ظاهر نمی‌شود جایگزین می‌کنید و موقعیت آن کاراکتر را مشخص می‌کنید. پس از آن، عدد بالا را از طول کل رشته کم می‌کنید تا طول نام خانوادگی را بدست آورید و تابع RIGHT این تعداد کاراکتر را استخراج کنید.

بنابراین، در اینجا نحوه جدا کردن نام و نام خانوادگی در اکسل زمانی که برخی از نام‌های اصلی دارای نام میانی هستند، آمده است:

نحوه جدا کردن نام و نام خانوادگی با کاما

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

فرمول استخراج نام

=RIGHT(cell, LEN(cell) – SEARCH(” “, cell))

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

فرمول استخراج نام خانوادگی

=LEFT(cell, SEARCH(” “, cell) – 2)

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

با نام کامل در سلول A2، فرمول‌ها به شکل زیر هستند:

دریافت نام :

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

دریافت نام خانوادگی :

=LEFT(A2, SEARCH(" ", A2) - 2)

تصویر زیر نتایج را نشان می‌دهد:

چگونه نام کامل را به نام، نام خانوادگی و میانی تقسیم کنیم

تقسیم نام‌هایی که شامل نام میانی یا حرف اول وسط هستند، بسته به قالب نام، به رویکردهای کمی متفاوت نیاز دارد.

اگر نام شما در قالب نام وسط نام خانوادگی باشد، فرمول‌های زیر به خوبی کار می‌کنند:

A	B	C	D
1	Full name	First name	Middle Name	Last name
2	FirstName MiddleName LastName	=LEFT(A2,SEARCH(" ", A2)-1)	=MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1)	=RIGHT(A2,LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2,1)+1))
Result:	David Mark White	David	Mark	White

برای به دست آوردن نام، از فرمول LEFT SEARCH از قبل آشنا استفاده می‌کنید.

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

برای استخراج نام میانی، باید موقعیت هر دو فاصله در نام را بدانید. برای تعیین موقعیت اولین فاصله، از یک تابع SEARCH(” “,A2) ساده استفاده کنید، که 1 را به آن اضافه کنید تا استخراج با کاراکتر بعدی شروع شود. این عدد به آرگومان start_num تابع MID ​​می رود. برای تعیین طول نام میانی، موقعیت فضای 1 را از موقعیت فضای دوم کم کنید، 1 را از نتیجه کم کنید تا از شر یک فضای انتهایی خلاص شوید و این عدد را در آرگومان num_chars MID قرار دهید. به آن بگویید که چند کاراکتر استخراج کند.

و در اینجا فرمول هایی برای جدا کردن نام های نام خانوادگی، نام نوع نام میانی وجود دارد :

A	B	C	D
1	Full name	First name	Middle name	Last Name
2	LastName, FirstName MiddleName	=MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2) + 1) - SEARCH(" ", A2) -1)	=RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ", A2, 1)+1))	=LEFT(A2, SEARCH(" ",A2,1)-2)
Result:	White, David Mark	David	Mark	White

رویکرد مشابهی را می‌توان برای تقسیم اسامی با پسوندها استفاده کرد:

A	B	C	D
1	Full name	First name	Last name	Suffix
2	FirstName LastName, Suffix	=LEFT(A2, SEARCH(" ",A2)-1)	=MID(A2, SEARCH(" ",A2) + 1, SEARCH(",",A2) - SEARCH(" ",A2)-1)	=RIGHT(A2, LEN(A2) - SEARCH(" ", A2, SEARCH(" ",A2)+1))
Result:	Robert Furlan, Jr.	Robert	Furlan	Jr.

به این ترتیب می‌توانید نام‌ها را در اکسل با استفاده از ترکیب‌های مختلف توابع تقسیم کنید.

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

 

جدا کردن نام در Excel 2013، 2016 و 2019 با Flash Fill

همه می‌دانند که Flash Fill می‌تواند به سرعت داده‌های یک الگوی خاص را پر کند. اما آیا می‌دانستید که می‌تواند داده‌ها را نیز تقسیم کند؟ در اینجا به این صورت توضیح داده شده است:

1. یک ستون جدید در کنار ستون با نام‌های اصلی اضافه کنید و قسمت نامی را که می‌خواهید در سلول اول استخراج کنید تایپ کنید (نام اول در این مثال).
2. شروع به تایپ نام اول در سلول دوم کنید. اگر اکسل، الگویی را حس کند (در بیشتر موارد این کار را انجام می‌دهد)، نام‌های کوچک را در تمام سلول‌های دیگر به طور خودکار پر می‌کند.
3. اکنون تنها کاری که باید انجام دهید این است که دکمه اینتر را فشار دهید 🙂