Sometimes have data that 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 dialog box will appear. Which guide you through the process alternatively .
How To use Text To Column In Excel select text to columns
You can select text to columns by going to data in the tab and then clicking on 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 on essentially any data that you have in columns to the right .
To insert some columns to the right of this ssn number .If we select all the numbers below ssn and we go to text to columns. we get this wizard that appears.
Firstly we have a choice, whether delimited or fixed width on this occasion .we could actually use fix with 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 text we need to select each column at a time and decide what format we want it to be.
To call that text as well 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 are of data format. We can have general text date normally general suffices.
How To Use Text To Column In Excel additional operators
If we go to 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 now0.
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 and excel wants to confirm. We actually want to undo that this time with text to columns.
select delimited now
To select delimited now, we can see that we have this minus symbol which is actually our delimiter. The delimiter separates the fields. when excel comes in where its going to separate here.
To click on other and unclick comma, Now 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. Now undoing 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 text to columns with the cell j3 selected and we go to delimited and click next. We have got 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 going into delimited going into next. 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 full cells. Now undo this and go back into 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 make splits.
Wherever, it sees a comma ,if comma is the delimiter if 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 treat consecutive delimiters as one for example. If we have got three commas between text then we want excel to split that into four cells or two cells.