سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

ابزار Text to column و تفکیک متن در سلول

تفکیک متن در سلول
۴.۷/۵ - (۱۳ امتیاز)

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

خیلی وقت ها پیش میاد که داده هایی که به دست ما رسیده، به هم چسبیده هستن و ما میخوایم این ها رو از هم تفکیک کنیم، در واقع قصد تفکیک متن در سلول رو داریم. بسته به منطق و نظم داده ها، روش های مختلفی برای این کار وجود داره. کدنویسی VBA، فرمول نویسی، ابزارهایی مثل Flash Fill و Text To Column راه هایی هستند که میشه برای این کار ازشون استفاده کرد. در این مقاله تفکیک داده ها بوسیله ابزار Text to Column رو آموزش خواهم داد.

ابزار Text to Column از تب Data و قسمت Data Tools در دسترس هست.

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

  • داده ها رو انتخاب میکنیم.
  • از تب Data گزینه Text to column رو میزنیم.
  • از پنجره باز شده (گام ۱ از ۳) گزینه Delimited رو انتخاب میکنیم و Next رو میزنیم.

نمونه داده ها

شکل ۱- تفکیک متن در سلول- نمونه داده ها

در پنجره ظاهر شده (گام ۲ از ۳) باید جدا کننده رو انتخاب کنیم. یا یکی از گزینه های موجود مثل Comma (کاما)، Space (فاصله) و … که در قسمت Delimiters نشون داده شده جدا کننده هستن، یا اینکه Other رو انتخاب میکنیم و جدا کننده مورد نظر رو در باکس روبروی اون، تایپ میکنیم. در اینجا جدا کننده اسلش (/) هست. برای همین Other رو انتخاب کرده و / رو تایپ میکنیم و Next رو میزنیم. (شکل ۲)

تفکیک متن در سلول- تعیین جدا کننده داده ها

شکل ۲- تفکیک متن در سلول- تعیین جدا کننده داده ها

  • در مرحله آخر (گام ۳ از ۳) در قسمت Destination مقصد داده های تفکیک شده رو تعیین میکنیم. اگر همون A1 بذاریم بمونه، داده های تفکیک شده رو روی داده های اصلی نمایش میده. برای اینکه اصل داده ها رو بتونیم نگه داریم، سلول B1 رو به عنوان مقصد تعیین میکنیم و Finish رو میزنیم. (شکل ۳)

برای مشاهده کاربرد تعیین فرمت در قسمت Column Data Format حتما پست مربوط به تبدیل متن به عدد رو مطالعه کنید.

تعیین مقصد داده های تفکیک شده و فرمت آنها

شکل ۳- تفکیک متن در سلول- تعیین مقصد داده های تفکیک شده و فرمت آنها

نکته:
هر قسمت از اطلاعات جدا شده در یک ستون قرار خواهند گرفت، که امکان تعیین فرمت هر ستون به صورت جداگانه وجود داره. برای تعیین فرمت هر ستون کافیه در قسمت Data Preview ستونی که میخوایم رو انتخاب کنیم (الا ستون اول که سیاه شده انتخاب شده) و بعد فرمت رو از بخش Column Data Format تعیین کنید). با انجام این کار، بعد از تفکیک اطلاعات، در اکسل هر ستون فرمت مشخص شده رو خواهد داشت.

 

با زدن گزینه Finish داده ها بصورت شکل ۴ تفکیک میشن و میتونید از داده های تفکیک شده استفاده کنید.

داده های تفکیک شده

شکل ۴- تفکیک متن در سلول- داده های تفکیک شده

تا اینجا نحوه تفکیک داده ها بوسیله جدا کننده مشخص Delimiter رو دیدیم. در آینده به نحوه تفکیک داده ها توسط پهنای کاراکتر Fixed Width خواهیم پرداخت.

آواتار
144

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

دیدگاه کاربران
  • Mm ۱۴ آذر ۱۴۰۱ / ۱۰:۴۳ ب٫ظ

    با سلام
    اگر بخواهیم سلولهایی که با عدد و اسپیس شروع شده را ، عدد و اسپیس ها را حذف کنیم چکار باید بکنیم
    یعنی میخواهیم متن بدون عدد و اسپیس اولش باشه

    • آواتار
      حسنا خاکزاد ۱۵ آذر ۱۴۰۱ / ۱۰:۵۷ ق٫ظ

      درود
      بستگی به شرایط و الگو داره که بشه فرمول داد
      در کل از ابزار flash fill هم میتونید استفاده کنید

  • اشکان رحمانی ۲۴ آبان ۱۴۰۰ / ۶:۲۳ ب٫ظ

    حالا من مشکلم اینه که اطلاعات تکستی که میارم تو اکسل با هیچ جدا کننده تی جدا نشده در این مواقع چگونه باید به ۳ قسمت تبدیل بشن ؟

    • آواتار
      حسنا خاکزاد ۲۵ آبان ۱۴۰۰ / ۲:۲۷ ب٫ظ

      درود بر شما
      باید منطقی وجود داشته باشه
      مثلا سه تا سه تا
      یا مثلا اول پنج تا بعد سه تا بعد دو تا
      یا ….

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

    • ارسلان ۱۰ بهمن ۱۴۰۱ / ۸:۰۴ ق٫ظ

      سلام وقتتون بخیر
      میخواستم ببینم اگه برام بگین داده های gps چجوری جداسازی کنم؟

      • آواتار
        حسنا خاکزاد ۱۱ بهمن ۱۴۰۱ / ۱۲:۴۸ ب٫ظ

        درود
        متوجه سوالتون نشدم!

  • melika ۱۷ اسفند ۱۳۹۹ / ۱۲:۳۲ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۷ اسفند ۱۳۹۹ / ۶:۰۰ ب٫ظ

      درود بر شما
      سوال واضح نیست
      توضیح بیشتر بدید لطفا

    • سامان چراغی ۲۸ فروردین ۱۴۰۰ / ۳:۰۶ ب٫ظ

      سلام
      قبل از همه فرمت سلول ها رو چک کنید (ممکنه تو فرمت این سلول ها اعشار رو زیاد کرده باشید)
      همچنین در مرحله آخر Text To Column حتما فرمت هر ستون رو مشخص کنید.
      در بعضی موارد اگه Text to Column به صورت Fix مشخص شده باشه میتونه این مشکل به وجود بیاره.

  • ali ۱۱ بهمن ۱۳۹۹ / ۶:۲۶ ب٫ظ

    سپاس بخاطر آموزش

  • مصطفی رضایی ۲۲ مرداد ۱۳۹۹ / ۱۲:۵۷ ب٫ظ

    سلام وقت بخیر،
    در اکسل ۲۰۱۳ جدولی دارم که اعداد و حروف به صورت ترکیبی و بدون الگو و نظمی وارد شده اند و برخی سلول ها هم بدون عدد هستند. چطور اعداد را استخراج کنم. هر فرمولی رو انجام میدم میشه.لطفا راهنمایی کنید.

    • آواتار
      حسنا خاکزاد ۲۳ مرداد ۱۳۹۹ / ۹:۰۰ ب٫ظ

      درود

      خیلی خیلی بستگی به الگو یداده ها داره و نمیشه راه حل مشخصی ارائه داد
      میتونید از ابزار flash fill استفاده کنید

  • هادی ۱۳ اردیبهشت ۱۳۹۹ / ۰:۲۹ ق٫ظ

    بسیار ممنونم.کارگشا بود

  • محمود جابری ۱۰ اردیبهشت ۱۳۹۹ / ۸:۱۶ ق٫ظ

    با سلام و خسته نباشید
    مت جدولی را از فایل html به اکسل کپی کرده ام که تاریخ در آن برعکس می باشد چگونه با حالت قبل تبدیل کنم
    ۹۹/۰۱/۲۷ به صورت ۲۷/۰۱/۹۹ در اکسل نوشته شده است

    • آواتار
      حسنا خاکزاد ۱۰ اردیبهشت ۱۳۹۹ / ۱۰:۳۸ ق٫ظ

      درود
      با توجه به اینکه ساختار تاریخ نداره، (اگر داشت با توابع year/month/day و date میشه جابجا کرد)
      اما چون فرمت متنی داره، هم میتونید با flash fill جابجا کنید
      هم توابع متنی مثل right, left, mid و …

  • رفیعی ۵ اردیبهشت ۱۳۹۹ / ۶:۲۳ ق٫ظ

    با تشکر از آموزش شما
    فونتهای فارسی من در دفتر تلفن بصورت ط­غŒط¯ط±غŒ خروجی میده چطور میتونم تصحیح کنم یا حداقل تبدیل به فینگلیش کنم؟

    • آواتار
      حسنا خاکزاد ۵ اردیبهشت ۱۳۹۹ / ۵:۲۴ ب٫ظ

      درود
      اگر منظورتون اینه که دفتر تلفنتون در محیط وی بی ای نوشته شده، باید تنظیمات زبان (Local System) در Region کنترل پنل رو روی persian قرار بدید و بعد ری استارت کنید و بعد فونت فارسی شناخته میشه

  • فرزانه ۲۴ اسفند ۱۳۹۸ / ۱۰:۲۸ ب٫ظ

    با سلام و سپاس از آموزش های خوبتون.
    سوالی داشتم در خصوص تفکیک متن که ممنون میشم راهنمایی بفرمایید:
    بنده عناوین حدود ۸۵ هزار تا مقاله رو در اکسل ذخیره کردم و نیاز دارم که کلمات کلیدی رو از عناوین مقالات استخراج کنم (بدون ایست واژه ها)، و البته مهم هست که این کلیدواژه ها در ستون های جداگانه ای قرار داده بشن که نظم تاریخی عناوین مقالات به هم نخوره. طبق جستجوهایی که داشتم گزینه word count فقط کلیدواژه های با بسامد بالا رو به همراه تعداد رخداد آن ها در شیت جداگانه ای استخراج میکنه و دیگه نظم تاریخی حفظ نمیشه…..
    آیا نرم افزار اکسل چنین امکانی رو در اختیار بنده قرار میده؟؟؟

    • آواتار
      حسنا خاکزاد ۲۵ اسفند ۱۳۹۸ / ۱۱:۴۶ ق٫ظ

      درود
      نمیدونم که برای این قبیل کارهای تخصصی افزونه ای در اکسل موجود هست یا نه…
      اما اگه بخوایم با خود اکسل این مسئله حل کنیم، اول باید توضیح بدید که منظورتون از نظم زمانی چیه و با چی تعیین میشه و در کجای اون رشته متنی قرار میگیره و …
      چون کلمات رو میتونید با همین ابزار text to column تفکیک کنید و بعد با countif تکرار هر کلمه رو حساب کنید. امام منظور از حفظ نظم زمانی رو باید توضیح بدید

      • فرزانه ۲۵ اسفند ۱۳۹۸ / ۱:۰۲ ب٫ظ

        با سپاس از شما
        منظور از نظم زمانی تاریخ انتشار مقالات هست که در فیلد جداگانه ای ذخیره شده. پایگاه داده شامل عناوین مقالات و تاریخ انتشار اون مقالات هست. بنده قرار است که واژگان عناوین مقالات رو جدا کنم که فرمودید با text to column میشه انجام داد و بعد میخوام بسامد واژگان رو در طی دوره زمانی (تاریخ انتشار) مختلف بررسی کنم. مثلا هر واژه در طی سال ۹۰ تا ۹۸ چه میزان رشدی داشته….
        حال سوال من این هست که چطور میشه واژگان رو که جدا میکنم و بسامد آن ها را تعیین میکنم سیر تاریخی حفظ شود و مشخص باشد که هر واژه در چه سال هایی در عناوین مقالات به کار رفته است؟

        • آواتار
          حسنا خاکزاد ۲۵ اسفند ۱۳۹۸ / ۱:۲۱ ب٫ظ

          در countifs که محاسبه میکنید تعداد تکرار کلمات رو، یک شرط هم اضافه کنید، به عنوان تاریخ
          مثلا کلمه اکسل در سال ۹۶ چند بار تکرار شده
          اینطوری هم تاریخش دخیل شده و هم کلمه

          • فرزانه ۲۵ اسفند ۱۳۹۸ / ۳:۲۵ ب٫ظ

            از راهنماییتون بی نهایت سپاسگزارم.

  • جابری ۶ بهمن ۱۳۹۸ / ۲:۴۲ ب٫ظ

    مطلب آموزشی بسایر عالی بود
    با تشکر

  • مجید ۲۴ آذر ۱۳۹۸ / ۵:۲۸ ب٫ظ

    با سلام و وقت بخیر
    بنده یک فایل اکسل رو وقتی باز می کنم تمام مطالب به هم ریخته س.
    تمام کلمات فارسی ناخوانا هستند به صورت زیر هستند.
    ط§غŒطھط§ظ„غŒط§غŒغŒ

    ممنون میشم بنده رو راهنمایی کنید.

    • سامان چراغی ۲۵ آذر ۱۳۹۸ / ۷:۱۱ ب٫ظ

      سلام
      ممکنه یک فایل CSV که از ابتدا داده های اون به این حالت تبدیل شده در اکسل باز شده و ذخیره شده که کاریش نمیشه کرد.
      ممکنه یک فایل CSV که اطلاعات درست درونش هست اما در اکسل با Encoding UFT8 باز نشده، که در اینصورت باید با استفاده از ابزار Get Data اون فایل رو باز کنید و UTF8 رو براش تنظیم کنید و در انتها در اکسل وارد کنید.

  • جواد ۱۹ مرداد ۱۳۹۸ / ۱۱:۰۴ ب٫ظ

    سلام و عرض ادب خدمت خانم خاکزاد
    یه داده ای دارم که از یه نرم افزار خروجی اکسل انجام شده وی میخاستم ارقام آخر هر سلول رو به سلول دیگری انتقال بدم .لطفا راهنمایی بفرمایید
    مواد نوع یک۱۲۳۴۵۱
    مواد نوع دو۱۲۳۱
    مواد نوع سه۲۱۵۴۱۳
    مواد نوع چهار۲۳

    • سامان چراغی ۲۰ مرداد ۱۳۹۸ / ۸:۲۴ ق٫ظ

      سلام
      از تابع Right استفاده کنید.

      • 1 ۲۱ مرداد ۱۳۹۸ / ۰:۰۵ ق٫ظ

        سلام. ارقام ثابت نیست و نمیشه

        • آواتار
          حسنا خاکزاد ۲۱ مرداد ۱۳۹۸ / ۱۱:۴۹ ق٫ظ

          هم از ابزار flash fillمیتونید استفاده کنید
          هم از همین text to column که همه رو تفکیک میکنه و شما فقط اخری رو لازم دارید
          هم اینکه آخرین space رو پیدا کنید و بعد از تابع right استفاده کنید

          برای ایده گرفتن میتونید مقاله زیر رو بخونید

          https://excelpedia.net/number-extraction/

          • جواد ۲۱ مرداد ۱۳۹۸ / ۴:۵۳ ب٫ظ

            با تشکر از شما

  • ali ۵ بهمن ۱۳۹۷ / ۲:۲۲ ق٫ظ

    در تبدیل متن به عدد از طریق تابعvalue…اگر یکی در میان سلول خالی در بین سلول های مورد نظر مون باشه ..تابع valueارور میده و همه چیو خراب میکنه…چیکار کنم که تابع valueبرای سلول های خالی استفاده نشه

    • آواتار
      حسنا خاکزاد ۵ بهمن ۱۳۹۷ / ۷:۲۹ ب٫ظ

      درود بر شما
      یا میتونید با توابع مدیریت خطا مثل iferror , …. کنترل کنید
      یا اینکه فرمول رو فقط در سلول های غیرخالی بنویسید. که با Go to می تونید پیدا کنید

  • Hassan Karimi ۱۴ شهریور ۱۳۹۷ / ۰:۲۷ ق٫ظ

    با سلام
    ممنون که دانسته هاتون رو Share می کنین. ای کاش می تونستم تو کلاس های حضوریتون شرکت کنم.

  • vahid ۱۳ تیر ۱۳۹۷ / ۱:۵۸ ب٫ظ

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

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان