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

می توانید از اکسل برای تقسیم اطلاعات به قطعات کوچکتر استفاده کنید. یافتن داده های مورد نیاز و دستکاری آن ها یک هدف مهم برای بسیاری از کاربران اکسل است.

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

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

فرمول های اکسل برای تقسیم نام ها به قطعات

از کجا شروع می کنی؟

تفکیک نام ها

این فرمول عمومی است:

=LEFT(سلول،FIND(" "،سلول،1)-1)

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

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

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

=LEFT(cell;FIND(" ";cell;1)-1)

در مثال، شما استفاده می کنید:

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

اکنون می توانید به سادگی دسته پر را تا انتهای ستون First Name به پایین بکشید.

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

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

کاما یا نقطه ویرگول؟

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

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

برای کشف اینکه کدام نماد توسط اکسل شما استفاده می شود، به سادگی شروع به تایپ فرمول کنید. هنگامی که وارد =LEFT( می شوید ، یک متن شناور را مشاهده می کنید که قالب بندی مناسب را پیشنهاد می کند.

تفکیک نام خانوادگی

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

فرمول مورد نیاز شما این است:

=RIGHT(سلول، LEN(سلول) – SEARCH("#"، SUBSTITUTE(سلول،"، "#"، LEN(سلول) - LEN(SUBSTITUTE(سلول،" "، "")))))

در مثال بالا، از فرمول زیر در سلول C2 استفاده می کنید:

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

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

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

نام های خانوادگی و نام خانوادگی با شخصیت های خاص دست نخورده باقی می مانند.

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

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

=RIGHT(سلول، LEN(سلول) - SEARCH(" "، سلول))

یا:

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

یا:

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

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

تفکیک اسامی میانی

فرمول نام های میانی به شرح زیر است:

=MID(سلول، SEARCH(" "، سلول) + 1، SEARCH(" "، سلول، SEARCH(" "، سلول)+1) - SEARCH(" "، سلول)-1)

در مثال بالا دریافت می کنید:

=MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1)

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

=MID(A2;SEARCH(" ";A2)+1;SEARCH(" ";A2;SEARCH(" ";A2)+1)-SEARCH(" ";A2)-1)

پس از وارد کردن فرمول، دسته پر را به پایین بکشید. در اینجا یک ستون نام میانی به مثال بالا اضافه شده است:

اگر نام کامل حاوی نام میانی یا حرف اول نباشد، مقادیر صفر را در این ستون دریافت می کنید که ممکن است به صورت #VALUE نمایش داده شود. برای دریافت سلول های خالی به جای #VALUE!، می توانید از تابع IFERROR استفاده کنید.

سپس فرمول شما به صورت زیر می شود:

=IFERROR(MID(سلول، SEARCH(" "، سلول) + 1، SEARCH(" "، سلول، SEARCH(" "، سلول)+1) - SEARCH(" "، سلول)-1)،"")

یا:

=IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1),"")

یا:

=IFERROR(MID(A2;SEARCH(" ";A2)+1;SEARCH(" ";A2;SEARCH(" ";A2)+1)-SEARCH(" ";A2)-1);"")

یک رویکرد برای جداسازی چند نام میانی

اگر فردی در لیست شما چند نام میانی داشته باشد چه اتفاقی می افتد؟ با استفاده از فرمول فوق، فقط نام میانی آنها بازیابی می شود.

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

این فرمول عبارت است از:

=TRIM(MID(cell1,LEN(cell2)+1,LEN(cell1)-LEN(cell2&cell3)))

در اینجا، cell1 به اشاره گر سلول در زیر ستون نام کامل، cell2 به نشانگر سلول زیر ستون First Name اشاره دارد، در حالی که cell3 به اشاره گر سلول زیر ستون نام خانوادگی اشاره دارد. در مثال بالا دریافت می کنیم:

=TRIM(MID(A2,LEN(B2)+1,LEN(A2)-LEN(B2&D2)))

یا:

=TRIM(MID(A2;LEN(B2)+1;LEN(A2)-LEN(B2&D2)))

اگر با این فرمول پیش بروید، دیگر نگران مقادیر صفر نخواهید بود.

جمع بندی سریع

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

نام ها: =LEFT(سلول،FIND(" "،سلول،1)-1)

نام‌های خانوادگی: =RIGHT(سلول، LEN(سلول) – SEARCH("#"، SUBSTITUTE(سلول،"، "#"، LEN(سلول) - LEN(SUBSTITUTE(سلول، ""، "")))) )

نام‌های میانی: =IFERROR(MID(سلول، SEARCH(" "، سلول) + 1، SEARCH(" "، سلول، SEARCH(" "، سلول)+1) - SEARCH(" "، سلول)-1)،" ”)

فرمول جایگزین برای نام‌های میانی: =TRIM(MID(cell1,LEN(cell2)+1,LEN(cell1)-LEN(cell2&cell3)))

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

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

1. مطمئن شوید که تب Data از منوی بالا انتخاب شده است و ستونی را که می خواهید تبدیل کنید برجسته کنید.
2. سپس روی  Text to Columns کلیک کنید .
3. در مرحله بعد، مطمئن شوید که  Delimited انتخاب شده است و روی Next کلیک کنید .
4. حالا از بین گزینه ها Space  را انتخاب کنید و Next را بزنید .
5. سپس،  مقصد را به “ $B$2 ” تغییر دهید و روی  Finish کلیک کنید. نتیجه نهایی باید به این شکل باشد.

کلام پایانی

راه های زیادی برای حل این مشکل در اکسل وجود دارد. اگر هیچ یک از گزینه های موجود آنچه را که نیاز دارید انجام نمی دهد، کمی تحقیق کنید.

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

به عنوان مثال، اگر نام کامل شخصی با نام خانوادگی او شروع شود، به اشتباه از هم جدا می شود. فرمول ها همچنین با نام های خانوادگی حاوی پیشوندها یا پسوندهایی مانند le Carré یا ون گوگ مشکل دارند. اگر نام شخصی به Jr ختم شود، به عنوان نام خانوادگی او در لیست قرار می گیرد.

با این حال، تغییراتی وجود دارد که می توانید برای حل این مشکلات همانطور که ظاهر می شوند، اضافه کنید. کار با فرمول ها به شما انعطاف پذیری لازم برای مقابله با این پیچیدگی ها را می دهد.