یکی از توانمندی‌های Excel، تبدیل کردن عبارت‌های موجود در یک ستون به عبارت‌هایی در ستون‌های مجزا است. در مورد این قابلیت کاربردی اکسل که Text to Columns نام دارد، مقاله‌ای منتشر کردیم. قابلیت جدا کردن عبارت‌های مختلف که به عنوان مثال با ویرگول، فاصله یا اسپیس، نقطه و حتی با کلمه‌ی خاصی جدا شده‌اند، در اکسل وجود دارد.
در این مقاله می‌خواهیم با چند مثال دیگر، استفاده از قابلیت تبدیل متن به چند ستون در Excel مجموعه‌ی آفیس مایکروسافت را به صورت حرفه‌ای‌تری بررسی کنیم. با ما باشید.
برای آشنایی با قابلیت تبدیل متن به ستون‌هایی از داده در اکسل، به مقاله‌ی قبلی سیاره‌ی آی‌تی در این خصوص توجه فرمایید:
استفاده از Text to Columns ساده است اما در عین حال توابع جالب و کاربردی برای پردازش متن و رشته یا String در اکسل وجود دارد که گاهی به ناچار باید از روش فرمول‌نویسی پیچیده برای تبدیل متن به ستون‌های مجزا استفاده کرد:
تبدیل متن به چند سلول با Delimiter یا جداکننده
فرض کنید لیستی از نام و نام خانوادگی افراد داریم و می‌خواهیم این دو را جدا کرده و به دو ستون تبدیل کنیم.
برای این کار ابتدا ستون جدیدی ایجاد کنید و در اولین سلول آن، عنوان ستون که نام خانوادگی یا Last name است را تایپ کنید.
برای تبدیل سلول‌های ستون اول به دو سلول در ستون اول و دوم، ابتدا ستون اول را انتخاب کنید و روی تب Data از نوار ابزار بالای صفحه کلیک کنید و سپس روی Text to Columns کلیک کنید.
در پنجره‌ی باز شده در مرحله‌ی اول گزینه‌ی Delimited را انتخاب کنید. این گزینه برای جدا کردن عبارت از محلی که به عنوان مثال ویرگول، فاصله یا کاراکتری دیگر به کار رفته، کاربرد دارد. در مثال ما نام و نام خانوادگی با فاصله یا در واقع زدن کلید Space جدا شده است.
روی Next کلیک کنید و در مرحله‌ی بعدی Delimited را انتخاب کنید که در مثال ما Space است. دقت کنید که تیک گزینه‌ی Tab را نیز حذف کنید.
و روی Next کلیک کنید. در مرحله‌ی سوم، نوع داده‌ها را انتخاب کنید. در مثال ما داده از نوع متن ساده است. لذا انتخاب کردن گزینه‌ی General یا Text مناسب به نظر می‌رسد.
در کادر Destination یا مقصد، می‌توانید محلی که عبارت جدا شده قرار می‌گیرد را وارد کنید. حالت پیش‌فرض سلول‌های پس از سلول فعلی است. بنابراین در مثال ما نیازی به تغییر سلول نیست.
در نهایت روی Finish کلیک کنید. نتیجه را در تصویر زیر مشاهده می‌کنید:
تبدیل متن به چند ستون با تعداد حرف و عدد مشخص
برخی اقسام داده دارای طول مشخص و ثابتی است. به عنوان مثال کد کالا، کد اشتراک، کد ملی، پلاک خودرو، شماره تلفن و غیره، معمولاً تعداد کاراکتر یا عدد ثابت و مشخصی دارد. در این صورت برای جدا کردن بخش‌های کدها می‌توانید داده‌ها را با ابزار Text to Columns اکسل به چند بخش در ستون‌های مجزا تقسیم کنید. به عنوان مثال برای جدا کردن کد شهر که پیش از شماره تلفن قرار می‌گیرد، می‌توانید چند رقم ابتدای شماره تلفن‌ها را جدا کنید.
و اما مثالی ساده و روش کار: فرض کنید شماره‌ی فاکتورهای یک فروشگاه به صورت کدی است که از دو حرف ابتدایی و چند عدد پس از آن تشکیل می‌شود. دو حرف ابتدا مشخص کننده‌ی مشتری است و عددهای بعدی مشخص‌کننده‌ی شماره‌ی فاکتور است. لذا ابتدا دو ستون جدید با عنوان Client یا مشتری و Invoice No یا شماره فاکتور ایجاد می‌کنیم:
و سپس ابزار Text to Columns را از تب Data اجرا می‌کنیم.
در مرحله‌ی اول حالت Fixed Width که به معنی طول ثابت است را انتخاب کنید و روی Next کلیک کنید.
در مرحله‌ی دوم می‌بایست محلی که عبارت شکسته می‌شود و جدا می‌شود را مشخص کنید. برای این مهم در بخش پیش‌نمایش داده‌ها یا Data Preview پایین پنجره روی محلی که موردنظر است کلیک کنید و سپس روی Next کلیک کنید.
در مرحله‌ی سوم نوع دیتا را به تناسب نیاز انتخاب کنید و همین‌طور سلول مقصد را مشخص کنید. با توجه به اینکه می‌خواهیم مقصد داده‌های جدا شده از سلول B2 شروع شود، عبارت =$B$2 را تایپ می‌کنیم. در نهایت روی Finish کلیک کنید.
همان‌طور که در تصویر زیر مشاهده می‌کنید، کد مشتریان و شماره‌ی فاکتور در دو ستون B و C چیده می‌شود.
تبدیل تاریخ با قابلیت تبدیل متن به ستون‌های داده
مثال بعدی در مورد مرحله‌ی سوم تبدیل داده‌ها به ستون‌های داده و در واقع مشخص کردن نوع داده است. فرض کنید می‌خواهیم تاریخ میلادی را که به شیوه‌ی آمریکایی نوشته شده، به شیوه‌ی اروپایی تغییر بدهیم و در سلول‌هایی درج کنیم و همین‌طور حالت برعکس را انجام بدهیم.
طبق معمول سلول‌های ردیف اول را انتخاب کنید و در تب Data روی Text to Columns کلیک کنید.
در مرحله‌ی اول Delimiter را انتخاب کنید و در مرحله‌ی دوم تیک تمام گزینه‌ها را حذف کنید چرا که در واقع قرار نیست عبارت را به چند ستون تبدیل کنیم!
و در مرحله‌ی سوم نوع داده را Date که به معنی تاریخ است، انتخاب کنید. سپس از منوی کرکره‌ای نوع نمایش تاریخ، مشخص کنید که می‌خواهید ترتیب روز و ماه و سال چگونه باشد.
روز با حرف D و ماه با حرف M و سال با حرف Y مشخص می‌شود.
شیوه‌ی آمریکایی بیان تاریخ، ماه و سپس روز و در نهایت سال است و لذا حالت MDY معادل شیوه‌ی آمریکایی است.
در نهایت روی Finish کلیک کنید. همان‌طور که در تصویر زیر مشاهده می‌کنید، داده‌های ستون A تغییر کرده است.
تبدیل فرمت اعداد با ابزار Text to Columns اکسل
در مثال قبل به کاربردی جالب که تبدیل کردن شیوه‌ی نمایش تاریخ بود اشاره کردیم. این مثال هم در ارتباط با جدا کردن عبارت‌ها نیست بلکه روش تغییر فرمت اعداد را بررسی می‌کنیم.
در برخی کشورها برای مشخص کردن موقعیت ممیز یا بخش اعشاری، از کاما یا ویرگول استفاده می‌شود و در برخی دیگر از نقطه استفاده می‌شود. به عنوان مثال عدد 1,064.34 در انگستان معادل هزار و شصت و چهار و سی و چهار صدم است در حالی که در بسیاری از کشورها معادل ۱ و خورده‌ای است! در صورت نیاز می‌توانید فرمت اعداد اینچنینی را به سادگی با Text to Columns اکسل تغییر بدهید.
ابتدا ستون اعداد را انتخاب کنید و ابزار Text to Columns را اجرا کنید. در مرحله‌ی اول Delimiter را انتخاب کنید و در مرحله‌ی دوم تیک تمام موارد را حذف کنید. در مرحله‌ی سوم نوع دیتا را General انتخاب کنید و برای دسترسی به تنظیمات پیشرفته روی دکمه‌ی Advanced کلیک کنید.
در پنجره‌ی باز شده، در کادر Decimal separator یا جداکننده‌ی بخش اعشاری، , یا . را به تناسب نیاز تایپ کنید و در دومین کادر که Thousand separator یا جداکننده‌ی هزارتایی است نیز علامت موردنظر را تایپ کنید. در نهایت روی OK کلیک کنید.
با کلیک روی Finish ، اعداد به شیوه‌ی تنظیم شده نمایش داده می‌شود.
اگر اشتباهی رخ داده، می‌توانید مجدداً علامت بخش اعشاری و جداکننده‌ی هر سه رقم را از نو تعریف کنید.
howtogeekسیاره‌ی آی‌تی