99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
How To Convert Text To Columns In Excel
Sun 19th September 2010
The Text to Column functionality is extremely useful in this case and there is even a wizard to help. In version 2010, go to the Data tab of the ribbon and choose Text to Column. This will present a wizard with a step by step guide on how to separate the text. In previous versions, go to Data and select Text to Columns. Many rows of data can be converted in one go, but if you attempt to select more than one column then Excel will give a warning message since the functionality is for the conversion of one column at a time.
The first step in the wizard is to decide on what exactly separates the text in the column. In our names example there is a space between the first name and the surname. If spaces separate the columns then select Fixed Width. If another character such as a comma or full stop separates them then choose delimited. In this example we will assume that the separator is a comma. Click next and some simple options are given for the separation character or select other and type your own choice. More than one separator can be selected. In the wizard viewer a vertical line will appear to show where Excel plans to separate the text.
The next tab allows the user to choose the data type for each column. Highlight each column in the viewer, one at a time and select the appropriate data type. It is important to get this right at this stage since if you need to complete further calculations on the data then this may be a problem if it is in an incorrect format. Sometimes there will be information which is considered irrelevant. For a data sheet that has customer names and addresses for example, there may also be an id field containing an id per row from a database you do not use yourself. If this is the case then highlight the column and select the checkbox labelled 'do not import column (skip).' When Excel performs the conversion it will ignore this column.
Make sure that you choose the correct destination cells. If you are converting column A, but there is already data in columns B and C which are required, select some new destination cells in the box provided. If you do not then you are in danger of overwriting the data in columns B and C since Excel will default to the next available column.
When using the Fixed width separator option in the wizard, the second tab will show a similar display with vertical lines as the delimited wizard. The difference is that the user can manually add a column break by clicking on the viewer or move an existing column break by clicking and dragging it. The wizard then has the same options to set the format of the columns.
The success of this functionality will depend on the quality of your starting data. The more data included in one cell, the more likely it is that you will have to go back and manually format some of the results. Our first name and surname example is very simple, but in reality data is often presented in a much more complicate way. An example of things getting more complicated is when an entire address is included in one cell. For a database this information would need to be split into Address1, Address2, Town, City and Post Code. Unfortunately the first address contains four words and the second address contains three. Unless each section is separated by a comma, this can be extremely complicated. Many columns will be created since each column will be separated using the spaces.
This functionality works best when the original data is simple and organised, but can help with complicated starting data, even if some manual adjustments must be performed upon completion.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Assistant Marketing Manager
I can't fault Monica's training, she was patient, friendly, knowledgeable and always had a smile on her face!
Course was slightly fast paced for people who have not used Excel much, but I found it great as there were new short cuts I did not know. Also, 1st half needs to allow people to have a go like brief exercises, but I understand time can be short. Otherwise very well presented course.
Keyboards and mouse very dirty and should be cleaned beforehand.
Lunch was superb!!
Seven Investment Management
Great course - relaxed atmosphere. Easy to take in the information. Really enjoyed learning.