When you’re playing with data in an office environment, what is critical in importance is time. You may perform a task in a manual manner without using any shortcuts or function of the main program and proudly achieve the required outcome but in terms of efficiency you might be lagging behind. It is recommended in such situations to search for relevant tips or keep yourself subscribed to ProgrammerFish to cover such issues. In this post, we will address a common issue faced by analysts, officers and executives while sharing, organizing and using information using Microsoft Excel 2013. Imagine a situation where you have thousands of rows of say, full names of people of a specific region. You want to separate the first name and the last name and save them in separate columns or cells, what would you do? Would you manually click on each cell, edit it and copy the latter part in order to paste it to a new cell reserved for last name. There may be some who could afford the time and effort to accomplish this monotonous task but if you ask us, time is surely of essence. To find out how to split text (may it be phrases, sentences or other forms of data strings), read on after the jump.
The solution to this problem employs a handy tool in Microsoft Office 2013 called Text to Columns. As highlighted earlier, the text can be a collection of phrases, names, dates and any other form of data expression but there must exist a barometer or a distinguishing point to differentiate between the splitting point and the rest.
1. The first step is to select as many rows as you require. Remember, you must remain in a single common column to successfully utilize this feature. In addition, you must also ensure that the neighboring column is empty so that the split text can easily be copied to that portion.
2. After selecting the rows, click the Text to Columns button accessible within the Data tab in the Microsoft Excel 2013 ribbon interface. You can choose between delimited (for commas or tabs separating each field) or fixed width (where there are spaces between each field).
3. If you select the first option, you must then specify the delimiters like Tab, semicolon, comma, space or any other aspect like a letter or a number. Otherwise, the latter option allows you to split text after a specific length. Each method is simplified using the Data Preview pane.