Hello Geeky, so today we are focusing on How to Split Text or Data in MS Excel. So please read this tutorial carefully so you may comprehend it in a better helpful way.
Guide: How to Split Text or Data in MS Excel
In your Excel spreadsheet, you will work on situations where you need to work with incoming data. In these cases, it is quite normal to find cells of information that are not in the desired format. For example, I have an Excel spreadsheet, where a section of cells show both the first and last names of customers together. In any case, I really want to divide the cells into two separate columns for the first and last names.
Using Word to Columns
- Microsoft’s official approach is to use Word to Columns to divide cells in Microsoft Excel. This method replicates any text in the cell directly to the right of the one being distributed. To avoid losing anything, create a new blank sheet next to the sharing cell.
- Select the cell you want to share. You can select multiple cells at the same time if you wish. Select the Data tab and select the “Text to Columns” option.
- Select “End” when done. Select the type of partition (s) your cell is using. Use the preview pane to see if your cell data is being shared correctly.
- Click Apply and select the data format for your new cell. This usually only appears in a desktop version. In most cases, you will end up with the default option, which is the same as your original cell. Click Finish.
- This option works for simple divisions. For more complex options, you will want to use Word to Columns or Excel functions (next section). Flash Fill helps you stream cells in Microsoft Excel by recognizing processes and full-automatic cells based on that process.
- Start by activating Fill Flash if you do not already have one. Go to “File -> Options -> Advanced” and scroll to the Edit Options area. Check “Add Automatic Flash” and “Enable Auto Finish for Cell Values.” Click OK to save your changes.
- Next, create a new document directly next to the cell you want to share. It must be on the direct side, or it will not work.
- In the new document, type the part of the cell you want to share. For example, if I was sharing my name, I would type “Crowder” into the cell next to “Crystal Crowder.”
- Depending on the version of Microsoft Excel you have, there are two options for the next step. Open the Data tab and see if you have a Flash option. If so, just type in the cell – “Crowder” for me. Then, click Fill Flash. This includes everything in the book.
- Pull the lower right corner of the cell down to the other. You will see a small icon appear. Click on and select “Fill Flash”. Make sure you get enough data from the next cell. If so, pull the corner down to fill in the blank paper as long as you need it. Select “Fill Flash” to automatically fill columns.
- If you need something stronger than Word to Columns, another option is available. You will need to use Left, Right, MID, Len, and search functions to divide cells in Microsoft Excel.
- The exact process will vary depending on how you divide the cell. For example, if you are sharing names, you will need one formula to pick the first and another to get the last. As a result, things get more complicated when dealing with middle names and merged names.
- Start by creating new columns next to the cell you want to share (do not have to be on the direct side). You will need a new document for each data item you want to share. For example, if you are dividing a cell into three parts, you will need three empty columns.
- Type in the first empty cell in your first column next to the cell you want to share. To make the example easier, I will share a name.
- The formula to get the first name will look like (type the cell then type formula / task area to enter the formula):
= OSI (A2, WÁ (“”, A2,1))
- The process looks to the left of the characters in the dividing cell. (A2 is the dividing cell.) The search division tells the formula to start at the point (use the division in your cell) and go left until it reaches the main character. For the last name, you will need to remove what you first saw from the result. The process will look like:
= RIGHTS (A2, LEN (A2) – Browse (“,, A2,1))
- The LEN function helps read the exact position of the character in your cell. Just like the left work, the right work starts from the right side of the field or flat.
- The MID function works to share data from the center of your cell. This helps you pick things like middle name.
Guide about How to Split Text or Data in MS Excel
In this guide, we told you about the How to Split Text or Data in MS Excel; please read all steps above so that you understand How to Split Text or Data in MS Excel in case if you need any assistance from us, then contact us.
How this tutorial or guide assisting you?
So in this guide, we discuss the How to Split Text or Data in MS Excel, which undoubtedly benefits you.
I hope you like the guide How to Split Text or Data in MS Excel. In case if you have any queries regards this article/tutorial you may ask us. Also, please share your love by sharing this article with your friends and family.