اگر زیاد از اکسل^(Excel) استفاده می کنید ، احتمالاً با موقعیتی برخورد کرده اید که در یک سلول یک نام دارید و باید نام را به سلول های مختلف جدا کنید. این یک مشکل بسیار رایج در اکسل^(Excel) است و احتمالاً می توانید یک جستجوی گوگل انجام دهید و^{(Google search and download)} 100 ماکرو مختلف نوشته شده توسط افراد مختلف را دانلود کنید تا این کار را برای شما انجام دهد.

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

اگر فرمول‌ها را دوست ندارید و راه‌حل سریع‌تری^{(quicker solution)} می‌خواهید ، به قسمت Text to Columns بروید ، که به شما می‌آموزد چگونه از یک ویژگی Excel^{(Excel feature)} برای انجام همان کار استفاده کنید. علاوه بر این، اگر بیش از دو مورد در یک سلول دارید که باید جدا کنید، از ویژگی متن به ستون نیز استفاده کنید. به عنوان مثال، اگر یک ستون دارای 6 فیلد با هم باشد، استفاده از فرمول های زیر^{(formulas below)} واقعا کثیف و پیچیده خواهد شد.

جدا کردن نام ها در اکسل

برای شروع، بیایید ببینیم نام ها معمولاً چگونه در یک صفحه گسترده اکسل^{(Excel spreadsheet)} ذخیره می شوند . متداول‌ترین دو راهی که من دیده‌ام، نام ^(firstname) خانوادگی^(lastname) با فاصله و نام خانوادگی^(lastname) ، نام^(firstname) خانوادگی با کاما است که این دو را از هم جدا می‌کند. هر زمان که من یک حرف اول وسطی را دیدم، معمولاً نام ^(firstname) خانوادگی ^(lastname)میانی^(midinitial) مانند زیر است :

با استفاده از چند فرمول ساده و ترکیب چند تا از آنها با هم، می توانید به راحتی نام، نام خانوادگی و حرف اول را در سلول های جداگانه در اکسل^(Excel) جدا کنید. بیایید با استخراج قسمت اول نام شروع کنیم. در مورد من، ما از دو تابع استفاده خواهیم کرد: چپ و جستجو^{(left and search)} . به طور منطقی این چیزی است که ما باید انجام دهیم:

^(Search)متن داخل سلول را برای یک فاصله یا کاما ^{(space or comma)}جستجو کنید، موقعیت را پیدا کنید و سپس تمام حروف سمت چپ آن موقعیت را بردارید.

در اینجا یک فرمول ساده است که کار را به درستی انجام می دهد: =LEFT(NN, SEARCH(” “, NN) – 1) ، که در آن NN سلولی است که نام در آن ذخیره شده است. -1 برای حذف فضای اضافی یا کاما^{(space or comma)} در انتهای رشته وجود دارد.

همانطور که می بینید، ما با تابع سمت چپ^{(left function)} شروع می کنیم که دو آرگومان می گیرد: رشته و تعداد کاراکترهایی که می خواهید از ابتدای رشته بگیرید. در حالت اول، با استفاده از دو گیومه و قرار دادن فاصله در بین آن، یک فاصله را جستجو می کنیم. در حالت دوم به جای فاصله به دنبال کاما هستیم. پس نتیجه 3 سناریویی که ذکر کردم چیست؟

اسم را از ردیف 3^{(row 3)} ، نام خانوادگی را از ردیف 5^{(row 5)} و نام را از ردیف 7^{(row 7)} گرفتیم . عالی! بنابراین بسته به نحوه ذخیره داده های شما، اکنون نام یا نام خانوادگی را استخراج کرده اید. حالا برای قسمت بعدی. این چیزی است که اکنون باید به صورت منطقی انجام دهیم:

- متن را در سلول برای یک فاصله یا کاما ^{(space or comma)}جستجو^(Search) کنید، موقعیت را پیدا کنید و سپس موقعیت را از طول کل رشته کم کنید. در اینجا فرمول به نظر می رسد:

=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))

بنابراین اکنون از تابع مناسب استفاده می کنیم. این نیز دو آرگومان دارد: رشته و تعداد کاراکترهایی که می‌خواهید از انتهای رشته به سمت چپ بگیرید. بنابراین ما طول رشته را منهای موقعیت فاصله یا کاما^{(space or comma)} می خواهیم. این به ما همه چیز را در سمت راست اولین فاصله یا کاما^{(space or comma)} می دهد.

عالی^(Great) است، اکنون قسمت دوم نام را داریم! در دو مورد اول، کار شما تقریباً تمام شده است، اما اگر یک حرف وسطی در نام وجود داشته باشد، می‌توانید ببینید که نتیجه همچنان شامل نام خانوادگی با حرف اول وسط است. بنابراین چگونه می توانیم نام خانوادگی را بدست آوریم و از حرف اول وسط خلاص شویم؟ آسان! فقط^(Just) همان فرمولی را که برای بدست آوردن بخش دوم نام استفاده کردیم را دوباره اجرا کنید.

بنابراین ما درست کار دیگری را انجام می دهیم و این بار فرمول را روی سلول ترکیبی اولیه و نام خانوادگی^{(name cell)} وسط اعمال می کنیم . بعد از حرف اول وسط فاصله را پیدا می کند و سپس طول منهای موقعیت فاصله تعداد^{(space number)} کاراکترها را از انتهای رشته می گیرد.

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

متن به ستون

همچنین راه آسان دیگری وجود دارد که می توانید متن ترکیبی را به ستون های جداگانه در اکسل^(Excel) جدا کنید. این یک ویژگی برجسته به نام Text to Columns است و بسیار خوب کار می کند. همچنین اگر ستونی داشته باشید که بیش از دو قطعه داده داشته باشد بسیار کارآمدتر است.

به عنوان مثال، در زیر تعدادی داده دارم که یک ردیف^{(one row)} دارای 4 قطعه داده و ردیف دیگر دارای 5 قطعه داده است. من می خواهم آن را به ترتیب به 4 ستون و 5 ستون تقسیم کنم. همانطور که می بینید، تلاش برای استفاده از فرمول های بالا غیر عملی خواهد بود.

در اکسل^(Excel) ابتدا ستونی را که می خواهید جدا کنید انتخاب کنید. سپس پیش بروید و روی زبانه Data کلیک کنید و سپس روی Text to Columns کلیک کنید .

با این کار جادوگر Text to Columns^{(Columns wizard)} ظاهر می شود. در مرحله 1^{(step 1)} ، شما انتخاب می کنید که آیا میدان محدود شده باشد یا عرض ثابت. در مورد ما، ما Delimited را انتخاب می کنیم .

در صفحه بعدی، جداکننده را انتخاب خواهید کرد. می توانید از برگه، نقطه ویرگول، کاما، فاصله انتخاب کنید یا یک مورد سفارشی^{(custom one)} را تایپ کنید .

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

روی Finish کلیک کنید و ببینید که چگونه داده های شما به شکل جادویی در ستون ها جدا می شوند. همانطور که می بینید، یک ردیف به پنج ستون و دیگری به چهار ستون تبدیل شد. ویژگی Text to Columns^{(Columns feature)} بسیار قدرتمند است و می تواند زندگی شما را بسیار آسان کند.

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

How to Separate First and Last Names in Excel

If you use Excel a lot, you have probably rυn across a situation where you have a name in a single cell and you need tо seрarate the name into different cells. This is a vеry common issue in Excel and you can probably do a Google search and dоwnload 100 different macros writtеn bу various people to do it for you.

However, in this post, I’ll show you how to setup a formula so you can do it yourself and actually understand what is going on. If you use Excel a lot, it’s probably a good idea to learn some of the more advanced functions so that you can do more interesting things with your data.

If you don’t like formulas and want a quicker solution, scroll down to the Text to Columns section, which teaches you how to use an Excel feature to do the same thing. In addition, the text to columns feature is also better to use if you have more than two items in a cell you need to separate. For example, if one column has 6 fields combined together, then using the formulas below will become really messy and complicated.

Separate Names in Excel

To get started, let’s see how names are usually stored in a Excel spreadsheet. The most common two ways I have seen are firstname lastname with just a space and lastname, firstname with a comma separating the two. Whenever I have seen a middle initial, it’s usually firstname midinitial lastname like below:

Using some simple formulas and combining a couple of them together, you can easily separate the first name, last name and middle initial into separate cells in Excel. Let’s start with extracting the first part of the name. In my case, we’re going to use two functions: left and search. Logically here’s what we need to do:

Search the text in the cell for a space or comma, find the position and then take out all the letters to the left of that position.

Here’s a simple formula that gets the job done correctly: =LEFT(NN, SEARCH(” “, NN) – 1), where NN is the cell that has the name stored in it. The -1 is there to remove the extra space or comma at the end of the string.

As you can see, we start out with the left function, which takes two arguments: the string and the number of characters you want to grab starting from the beginning of the string. In the first case, we search for a space by using double quotes and putting a space in-between. In the second case, we are looking for a comma instead of a space. So what is the result for the 3 scenarios I have mentioned?

We got the first name from row 3, the last name from row 5 and the first name from row 7. Great! So depending on how your data is stored, you have now extracted either the first name or the last name. Now for the next part. Here’s what we need to do logically now:

– Search the text in the cell for a space or comma, find the position and then subtract the position from total length of the string. Here’s what the formula would look like:

=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))

So now we use the right function. This takes two arguments also: the string and the number of characters you want to grab starting from the end of the string going left. So we want the length of the string minus the position of the space or comma. That will give us everything to the right of the first space or comma.

Great, now we have the second part of the name! In the first two cases, you’re pretty much done, but if there is a middle initial in the name, you can see that the result still includes the last name with the middle initial. So how do we just get the last name and get rid of the middle initial? Easy! Just run the same formula again that we used to get the second section of the name.

So we are just doing another right and this time applying the formula on the combined middle initial and last name cell. It will find the space after the middle initial and then take the length minus the position of the space number of characters off the end of the string.

So there you have it! You have now split the first name and last name into separate columns using a few simple formulas in Excel! Obviously, not everyone will have their text formatted in this way, but you can easily edit it to suit your needs.

Text to Columns

There is also another easy way you can separate combined text into separate columns in Excel. It’s a featured called Text to Columns and it works very well. It’s also much more efficient if you have a column that has more than two pieces of data.

For example, below I have some data where one row has 4 pieces of data and the other row has 5 pieces of data. I would like to split that into 4 columns and 5 columns, respectively. As you can see, trying to use the formulas above would be impractical.

In Excel, first select the column you want to separate. Then, go ahead and click on the Data tab and then click on Text to Columns.

This will bring up the Text to Columns wizard. In step 1, you choose whether the field is delimited or fixed width. In our case, we’ll choose Delimited.

On the next screen, you will choose the delimiter. You can pick from tab, semicolon, comma, space or type a custom one in.

Finally, you choose the data format for the column. Normally, General will work just fine for most types of data. If you have something specific like dates, then choose that format.

Click Finish and watch how your data is magically separated into columns. As you can see, one row turned into five columns and the other one into four columns. The Text to Columns feature is very powerful and can make your life a lot easier.

If you are having problems separating names not in the format I have above, post a comment with your data and I’ll try to help. Enjoy!

Related posts

• اضافه کردن Regression Trendline خطی به Excel Scatter Plot

• چگونه می توان یک Histogram در Excel

• چگونه به صورت امن Password Protect Excel File

• چگونه به ایجاد یک Flowchart در Word and Excel

• چگونه Create Labels در Word از Excel Spreadsheet

• 13 OneNote Tips & Tricks برای سازماندهی یادداشت های خود را بهتر

• چگونه به Automatically Backup Word Document به OneDrive

• از پنجره تماشای اکسل برای نظارت بر سلول های مهم در یک کتاب کار استفاده کنید

• نحوه اضافه کردن نظرات به سلول کاربرگ اکسل

• نحوه ردیابی تغییرات در اکسل

• آموزش مبانی مایکروسافت اکسل – آموزش استفاده از اکسل

• تنظیم خودکار عرض ستون و ارتفاع ردیف در اکسل

• چگونه برای ایجاد Distribution List در Outlook

• نحوه ثبت Macro در Excel

• نمودار داده های اکسل شما

• چگونه می توان تجزیه و تحلیل What-If را در مایکروسافت اکسل درک کرد

• چگونه یک نمودار یا نمودار ساده در اکسل بسازیم

• از عملکرد جدید Excel Mobile "Insert Data From Picture" استفاده کنید

• راهنمای همه پسوندهای فایل اکسل و معنای آنها

• از توابع خلاصه برای خلاصه کردن داده ها در اکسل استفاده کنید