Sometimes data is in one cell, which we would like to spread over several cells or several columns. This is called text columns. It normally happens on a couple of occasions.

Firstly, you may have some data that you’re importing from another system, and you’re importing it in a CSV or a text format; when you open it in excel, normally a text-to-column wizard or series of the dialog box will appear, which guides you through the process alternatively. 

Text to Columns in Excel                 

You can select text to columns in excel by going to data in the tab and then clicking on the text to columns because you’re taking data in one column and splitting it into several columns. You should always make sure that you’ve got some columns to the right, so you won’t overwrite essentially any data that you have in columns to the right.

To insert some columns to the right of this serial number, we select all the numbers below that serial number, and we go to the text to columns in excel. Firstly we have a choice, whether delimited or fixed-width on this occasion. We could actually use the fix width option, although delimited is the one that I use most of the time. 

If we click next, we then have the next stage of the wizard. This screen lets you set field widths. If we want to create a column break, we just click up here, and we get an arrow. If we want to click another column break, we click up here. We get another arrow, but we will display this dash. If we go to next and let’s say we want this to be the text, we must select each column and decide what format we want it to be.

You have to call that text as well so that we show this minus symbol. Then have a look at some of the other options of data format. We can have general text dates normally general suffices.

Additional Operators

If we go advanced, we can see some additional operators. For example: if we import a file and it’s got a decimal point or a comma, and we want it interpreted differently from excel’s standard interpretation, we can amend the settings here; if we click finish, we will see the result across the three columns now. 

This is perhaps not the best way of doing this because we have ended up with these text values with a minus in front. This is producing some warnings in excel because there’s really a mishmash of text and numbers that excel wants to confirm. We actually want to undo it with text to columns in excel.

Select Delimited Now

To select delimited now, we can see that we have this minus symbol, our delimiter. The delimiter separates the fields when excel comes in where it’s going to separate here. To click on other and unclick comma, we already have a minus sign in other.

Now, we can click next and finish. We have got rid of those minus signs, and we have got the numbers. Let’s undo text to columns. Now, look at another example just to bring out another aspect of functionality for text to columns.

How to Use Text to Column in Excel

If we click the text to columns with the cell j3 selected, we can go to delimited and click next. We have got a comma selected, and we can see that excel is correctly predicting where we want the sale to be split after hello world and before goodbye world.

If we click next and finish, we will see that we get this split across two cells.

However, if we are incautious with the defaults, we might end up doing something that we did not want to do. Clicking text to columns again goes into delimited. If we look at text qualifier, we change this to none excel’s actually going to put in three splits.

Spread Text across Four Cells

It is going to spread this text across four cells, which we don’t really want. So, here it’s across total cells. Now undo this and go back into the text to columns again and delimited. Next, we can see that we have this text qualifier. We can set it to none, in which case excel will look at all commas and do splits.

Wherever it sees a comma, if a comma is a delimiter, for example, the sentences are set up with quotation marks as we have here, and we select those quotation marks, then excel will ignore the comma that occurs within the quotation marks.

The final bit of functionality just to mention up here is to treat consecutive delimiters as one. For example, if we have three commas between text, we want excel to split that into four cells or two cells.