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:
=FIND(",",A2,1). The arguments inside the parenthesis for the FIND function are what text to find, where are you searching and where do you want to start in the text.
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.
=LEFT(B2,A4-1). The arguments inside the LEFT function are the location of the text (B2 since we inserted a column) and how many characters (we will go to one less than where the comma is located.
Now we carry on for the next person. First find the second comma.
=FIND(",",$B$2,+A4+1). We build on the prior formula. The find is the same as before, except we change the starting point to one right of the first comma. Now you see why we kept the comma location in a separate cell.
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:
=MID($B$2,A4+2,+A5-A4-2). The arguments are the text being used, the starting point (we specify two spaces to the right of the first comma) and the length (we take the difference between the two commas, minus 2 for the space and the comma). If you are not quite sure on the length (i.e. how many spaces to subtract, take a guess; you can easily adjust).
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.
=LEN(B2). The only argument for the LEN function is the text being used.
Then we grab the final name.
=RIGHT(B2,A9-A8-1). We can do this using the RIGHT function which has two arguments- the text and the length. We adjust the number by one for the space after the last comma.
We also could have used the MID function:
=MID($B$2,A8+2,+A9-A8-1). Since we already have a MID function for the prior names, this is easier to create by just dragging down the last MID function and then adjusting the length by one since there is no final comma to the right.
The text functions can be great for working with lists. Some pointers to recap and remember:
Use the functions to build on each other- the second name can feed off the first name formulas, etc.
Split up locators as we did for the formulas that located the commas. We could have combined the formulas in the two columns, but then it would have been as simple to copy down to create other names.
Splitting functions also makes it easier to debug
Try to set the formulas up so you can copy the formulas to speed your work. That includes using appropriate absolute references with $ signs.
By using text formulas, you can work around messy lists and convert them into usable form.
Return To Excel Tips Index