جدا کردن نام و نام خانوادگی در اکسل (۴ روش کاربردی و سریع)

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

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

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

  • برای جداسازی سریع و یک‌باره، بدون فرمول‌نویسی، از قابلیت Flash Fill (روش اول) یا ابزار Text to Columns (روش دوم) استفاده کنید.
  • برای جداسازی خودکار با یک فرمول کوتاه، تابع TEXTSPLIT (روش سوم) را به کار ببرید.
  • برای اسامی چندبخشی، دارای پیشوند/پسوند و فهرست‌های در حال تغییر، از فرمول‌نویسی با توابع متنی (روش چهارم) استفاده کنید.

روش اول: جداسازی سریع نام و نام خانوادگی با کلید میانبر (Flash Fill)

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

نمودار سه‌بعدی از یک جدول که ستون دوم آن با آیکون جرقه و دو کلید کیبورد به صورت خودکار پر می‌شود
  1. مطمئن شوید دو ستون خالی در کنار ستون نام و نام خانوادگی قرار دارد. فرض کنید سلول A3 حاوی عبارت «نوید عموزاده» است.
  2. در اولین سلول ستون مجاور، یعنی سلول B3، نام کوچک «نوید» را به‌صورت دستی تایپ کنید.
  3. سلول B3 را انتخاب کنید و کلیدهای ترکیبی Ctrl + E را فشار دهید. اکسل الگو را شناسایی می‌کند و تمام نام‌های کوچک را در ستون B وارد می‌کند.
  4. به ستون بعدی بروید و در سلول C3، اولین نام خانوادگی، یعنی «عموزاده» را تایپ کنید.
  5. روی سلول C3 کلیک کنید و دوباره کلیدهای Ctrl + E را بزنید تا اکسل نام خانوادگی تمام ردیف‌ها را به‌طور خودکار تفکیک کند.
آموزش اکسل کاربردی
آموزش اکسل کاربردی
899,000 494,450 تومان
45%

روش دوم: استفاده از ابزار Text to Columns (ویزارد جداسازی)

ویزارد Text to Columns ابزار استاندارد اکسل برای تفکیک داده‌های متنی یک سلول است. این ابزار متن را بر اساس یک کاراکتر جداکننده (Delimiter)، مثل فاصله یا ویرگول، یک‌جا به ستون‌های مجزا تقسیم می‌کند. علاوه بر نام و نام خانوادگی، برای جدا کردن نام میانی و پیشوندها نیز انعطاف‌پذیری بالایی دارد.

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

تفکیک نام‌هایی که با فاصله (Space) یا ویرگول جدا شده‌اند

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

پنل گرافیکی سه‌بعدی که در آن یک نوار افقی با خط عمودی درخشان به دو ستون مجزا تقسیم شده است
  1. ستون یا سلول‌های حاوی نام و نام خانوادگی را انتخاب کنید تا هایلایت شوند.
  2. از نوار بالای صفحه وارد تب Data شوید و در بخش Data Tools روی گزینه Text to Columns کلیک کنید.
  3. در پنجره بازشده، گزینه Delimited را انتخاب کنید و دکمه Next را بزنید.
  4. در بخش Delimiters، تیک گزینه Tab را بردارید و گزینه Space را فعال کنید. اگر نام و نام خانوادگی با ویرگول جدا شده‌اند، گزینه Comma را تیک بزنید. اگر داده‌ها هم ویرگول دارند و هم فاصله، می‌توانید هر دو گزینه را انتخاب‌شده نگه دارید. سپس روی Next کلیک کنید.
  5. در بخش Column data format، گزینه Text را انتخاب کنید.
  6. در فیلد Destination، آدرس ستون مقصد را برای قرار گرفتن خروجی‌ها مشخص کنید و روی Finish کلیک کنید.

این روش برای نام‌های ساده و اسامی دارای نام میانی یا پیشوند، مانند Sue Sally Smith یا Sue S. Smith، کاربرد دارد.

حذف بخشی از نام (مثل نام میانی) هنگام جداسازی

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

سه ستون از بلوک‌های رنگی که ستون وسط خاکستری و نیمه‌شفاف شده و علامت توقف قرمز بالای آن قرار دارد
  1. در کادر پیش‌نمایش پایین پنجره (Preview of selected data)، روی ستون نام میانی کلیک کنید تا هایلایت شود.
  2. از بخش Column data format در بالای پنجره، گزینه Do not import column (Skip) را انتخاب کنید.
  3. روی دکمه Finish کلیک کنید.

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

روش سوم: تفکیک نام و نام خانوادگی با تابع TEXTSPLIT

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

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

فرض کنید اولین نام و نام خانوادگی جدول در سلول A3 قرار دارد. روی سلولی که می‌خواهید نام کوچک در آن نمایش داده شود کلیک کنید. فرمول زیر را بنویسید و دکمه Enter را فشار دهید؛ آدرس A3 را با آدرس سلول موردنظر خود جایگزین کنید:

=TEXTSPLIT(A3, " ")

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

روش چهارم: فرمول‌نویسی با توابع متنی (برای استفاده مکرر و اسامی پیچیده)

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

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

فرمول نویسی در اکسل فرمول نویسی در اکسل

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

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

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

فرمول استخراج نام کوچک:

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

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

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

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

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

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

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

در این فرمول‌ها، تابع find در اکسل و تابع SEARCH محل کاراکتر فاصله را پیدا می‌کنند. توابع LEFT، RIGHT و تابع mid در اکسل نیز با کمک تابع LEN، که طول متن را می‌شمارد، حروف قبل، بعد یا بین فاصله‌ها را استخراج می‌کنند.

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

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

۱. حالت اول: نام‌های دوبخشی (با نام خانوادگی تک‌بخشی)

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

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

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

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

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

۲. حالت دوم: نام‌های خانوادگی دوبخشی و سه‌بخشی (با نام کوچک تک‌بخشی)

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

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

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

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

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

مدیریت پیشوند و پسوند اسامی با فرمول

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

فرمول نام با حذف پیشوند:

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

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

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

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

فرمول نام:

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

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

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

فرمول پسوند:

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

نحوه اعمال فرمول روی تمام سطرهای جدول

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

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

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

آیا می‌توانم نام و نام خانوادگی را به طور همزمان در دو ستون جدا کنم؟

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

آیا امکان جداسازی نام و نام خانوادگی برای صدها یا هزاران سطر به صورت یکجا وجود دارد؟

بله، ابزار Text to Columns، تابع TEXTSPLIT، قابلیت Flash Fill و Power Query برای پردازش حجم بالای داده طراحی شده‌اند. این ابزارها بدون کند کردن برنامه، الگو را در چند ثانیه روی کل جدول اعمال می‌کنند.

چگونه مانع از پاک شدن داده‌های اصلی هنگام استفاده از Text to Columns شویم؟

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

کدام روش برای لیست‌هایی که مدام به آن‌ها نام جدید اضافه می‌شود مناسب‌تر است؟

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

چطور هنگام تفکیک اسامی سه‌بخشی، نام میانی را حذف کنیم تا وارد جدول نشود؟

در مرحله آخر ویزارد Text to Columns، ستون نام میانی را در بخش پیش‌نمایش انتخاب کنید و گزینه Do not import column (Skip) را فعال کنید.
برای تفکیک سریع و یک‌باره اسامی، ویزارد Text to Columns یا میانبر صفحه‌کلید کار شما را راه می‌اندازند؛ توابع متنی برای مدیریت داده‌های پیچیده و فهرست‌های در حال رشد انتخاب بهتری هستند. اگر پس از ویرایش و مرتب‌سازی جدول نیاز به ترکیب مجدد ستون‌ها داشتید، می‌توانید از روش‌های ادغام دو سلول در اکسل استفاده کنید. برای تسلط بیشتر بر توابع متنی و مدیریت پیشرفته داده‌ها در جدول‌های بزرگ نیز می‌توانید مباحث آموزشی اکسل در مکتب‌خونه را دنبال کنید.

https://maktabkhooneh.org/mag/separate-first-and-last-name-in-excel/
تصویر مهناز محمدی

مهناز محمدی

0 0 امتیازها
امتیاز دهی به محتوا
مشترک شوید
اطلاع از
guest
0 دیدگاه
قدیمی ترین
جدید ترین دیدگاه با تعداد رای زیاد
مقالات بیشتر
آموزش ارتباط بین جداول در اکسل به صورت گام به گام

ارتباط بین جداول در اکسل به صورت گام به گام

XLOOKUP در اکسل چیست؟

کار با تابع XLOOKUP در اکسل به زبان ساده

انتقال نقشه از اتوکد به اکسل

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

ایجاد فرم در اکسل با vba

ایجاد فرم در اکسل با vba در چند گام ساده

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

معرفی بهترین منابع اکسل PDF

دانلود و نصب اکسل

دانلود اکسل و نصب آن برای انواع سیستم عامل