The text to columns tool lets you split the string from one column into separate and multiple columns based on a delimiter. I find this tool to be most useful if I'm looking at a set of records in a table that haven't been split out. So if I have a data set with five attributes in it, I expect to see five columns in it. But if I'm seeing each record as one huge column, it means my data hasn't been split out. And I can use text to columns to do that for me. A good example of this, which I've illustrated in the slide here, is the address field.
We know that an address contains a street, suburb, postcode and state. So why not split that up into four columns, using the text to columns tool, and using a comma as our delimiter. We can see in the third picture pane that we can split our address into four different fields. Let's start A new workflow and import our 6.1 text to columns worksheet, we'll go to the Insert tab, dragging out input data tool and select 6.1 text to columns. What we're going to do here is split out the address into four different columns. So the first column will be the address line one, second is the city, third postcard and fourth country.
And seeing that each value can be separated by a comma. We're going to use that as our delimiter to split the data into multiple columns. So let's go to the pause tab, and drag in a text to columns tool. If you're unable to see the pause tab, you can click on the Add Remove button and check pause here In the configuration pane on the left, we have the option to select the column we want to split by, which in our case will be address. We then have the delimiter that we'd like to split the column by, which will be a comma. If you have multiple delimiters you want to use then you can just add them one after the other here.
If you want to split by tab, you have to use backslash T. If you want to split by newline, it's backslash n, spaces backslash s, and space or tab is backslash, S, backslash T. Underneath we have the option to either split by columns or split by rows, which will either split the data across multiple columns or multiple rows. Let's choose split by columns. Next, we need to define how many new columns we're expecting to see. And because we know that there are four line items in our address or split them into four columns, Next, we need to set how ultrix will react if it finds more than four columns. We can either leave all extra text in the last column, drop or remove extra text without warning, or just terminate and error out, or select error as we only want the defined number of columns. Lastly, in advanced options, you can choose to ignore delimiters in quotes, single quotes, parentheses, or brackets.
For example, if you have a maybe Product Description field, which naturally contains a lot of commas, you don't want to split that into a new column. Otherwise, you'd have an inconsistent number of columns. So let's add a browse tool to the end of our workflow with Ctrl Shift v. And then we'll run our workflow with Ctrl R. And here we have how columns split into four ultrix is giving us an error here because it's saying that there are leading spaces. At the end of each of these values. What we can do is go to the preparation tab and add a data cleansing Tool and move out browse the heal, run out workflow again, and everything's green. The address field in our data has now been split into multiple columns.