Text Splitting

You may come across directory information that would be nice to carve up.  In some cases, you could open the file and use Excel to automatically split the data, such as for comma delimited files.  That's a topic for another lesson.

Suppose that won't work.  That's okay.  You can use the text functions in Excel to split up text in a long line by building on your prior formulas.

Even if you could use Excel automatically, it still can come in handy to know how to do it with functions.

The exercise and the result are in the Text Splitting file.

Here is the list:

Let's split the list into a vertical list.  There are different ways to do it.  Here is one.

The first step is to find the first comma:

We will also insert a column and move the formula to the left hand column.

The next step then is to pick up the text left of the first comma.  We will build on this first formula.

Now we carry on for the next person.  First find the second comma.

Now we go and build the formula for the second name.  Since we are working in the middle of a text string, we will use the MID function, which has 3 arguments:

Now here is the beauty.  Since we have set up the formulas like we have, it now becomes a copy job to get the other three names.

One more name to go.  The last one is a little different, since there is no comma to the right.  First, since it is the last one, the far right point will equal the length on the whole text string.

Then we grab the final name.

We also could have used the MID function:

The text functions can be great for working with lists.  Some pointers to recap and remember:

By using text formulas, you can work around messy lists and convert them into usable form.

Return To Excel Tips Index