یک سری شماره موبایل داریم و میخوایم با یکسان سازی در اکسل برای ارسال پیام واتس اپ آنها را آماده کنیم ، ولی شماره ها یکسان نیستند و باید از یک استاندارد پیروی کنند. یعنی باید با عدد 98 شروع شده و بدون صفر اول وارد شوند مانند 989351405575 که در ادامه به رایگان این کار را انجام میدهیم.
فهرست محتوا
مشکلات احتمالی یکسان سازی در اکسل
به تصویر زیر توجه کنید ، فقط رویف اول آن شماره موبایل درست دارد ، و مابقی باید یکسان سازی شوند . و مشکلات موجود در شماره تلفنها از ردیف دوم به بعد :
- ردیف دوم در ابتدا دارای + است که باید حذف شود.
- ردیف سوم فاقد پیش شماره ایران یعنی 98 می باشد باید اضافه شود.
- ردیف چهارم یک صفر در ابتدا دارد و 98 ندارد و در ضمن یک شماره اضافه می باشد.
- ردیف پنجم 00 در ابتدا دارد.
- ردیف آخر هم 98 در ابتدا ندارد و همچنین یک رقم کم می باشد.
حالا ما میخواهیم ، قدم به قدم همانطور که در ستون B تصویر بالال می بینید ، شماره ها را یکسان کنیم.
قدم اول ، بررسی وجود 0 در ابتدا و حذف آن
برای انجام یکسان سازی در اکسل باید بررسی کنیم که آیا در ابتدای شماره هاب وارد شده 00 وجود دارد یا خیر و اگر وجد داشت آن را حذف کنیم.
برای این کار ابتدا با تابع Left اکسل دو رقم سمت چپ اعدا وارد شده در ستون A را جدا می کنیم .
از یک تابع IF استفاده میکنیم و اگر در ابتدای شماره موبایل در اکسل 00 بود.
با تابع SUBSTITUTE آن را با “” جایگزین می کنیم و در غیر اینصورت همان مقدار A را نمایش می دهیم.
با فرض بر اینکه نتیجه فرمول بالا در ستون B قرار گرفته باید ، بررسی کنیم ببینیم آیا ابتدای شماره با 0 شروع شده است یا نه و اگر چنین بود آن را با “” جایگزین کنیم .
قدم دوم ، حذف + از ابتدای شماره در صورت وجود
این هم بسیار ساده است و یا یک تابع Left بررسی می کنیم که آیا شماره موبایل در اکسل علامت + را در ابتدای خود دارد یا نه ، اگر وجود داشت با تابع SUBSTITUTE آن را با “” جایگزین میکنیم و اگر نبود که همان نتیجه فرمول بالا را نمایش می دهیم.
قدم سوم ، اضافه کردن 98 به ابتدا نوشته
خب باید بررسی کنیم که نتیجه عملیاتهای بالا در ابتدای خود عدد 98 را دارد یا نه که با تابع IF به راحتی انجام می شود ، اگر وجود داشت که هیچ همان را نمایش می دهیم و اگر نبود باید باید یک 98 به ابتدای آن اضافه کنیم .
در نهایت با فرض اینکه شماره ها ما در ستون A قرار دارند ، کافیست که فرمول زیر را در ستون B بنویسیم.
=CONCAT("+98",RIGHT(A2,10))اصلا بزرگ بودن فرمول بالا باعث ترس شما نباشد ، همانطور که در ویدیو دیدید ، این کار به آسانی انجام شده است.
قدم آخر ، رنگی کردن شماره های نادرست
البته منظور از نادرست در این قسمت ، از لحاظ تعداد می باشد ، چون اگر بخواهیم به شماره موبایل پیش شماره را اضافه کنیم مانند 989351405575 یک عدد 12 رقمی به دست می آید و می خواهیم بررسی کنیم که تعداد نتیجه به دست آمده کمتر یا بیشتر از 12 رقم نباشد.
- محدوده فرمول را انتخاب کرده و Conditional Formatting را باز می کنیم .
- گزینه New Rule را انتخاب کرده و مورد آخر آن را کلیک می کنیم .
- در باکس فرمول از تابع Len استفاده میکنیم .
- مقدار نتیجه را <> با 12 قرار می دهیم و یک رنگ برای آن در نظر می گیریم.
حالا به راحتی می توانیم شماره موبایلهایی که یکسان سازی شده کپی کنیم و در جاهای مورد نیاز از آن بهره ببریم.
در مورد یکسان سازی داده ها هم در دوره جامع مقدماتی اکسل صحبت کرده ایم.
