Excelling at Excel - Pulling apart long lists quickly using text functions

Have you ever spent time pulling apart elements from one text field in Excel to break them into separate text fields? If your data is stored in predictable chunks—a product SKU list for instance—you can save a lot of time by using simple text functions.

For this example, all of our products have six digit numbers in front of the product names:

We want one column with just SKU numbers and another that contains only the product name. Wait, don't start cutting and pasting - there's a better way!

In the cell to the right of the first product list item, enter =LEFT(A2,6). This will extract just the leftmost six characters. See how easy that was? Now just fill down to complete the column of SKUs. In the next column, we’ll use a slightly more complicated formula to pick up the product name:

=RIGHT(A2,LEN(A2)-7)

We know that everything to the right of the six-digit SKU and the space that follows it is our product name, so we simply measure the length of the text string in A2 and then subtract 7. Now fill down to finish the job:

If there was text you wanted to grab from the middle of a text field, you would use the MID function (instead of LEFT or RIGHT) an include a third value telling it where to end extraction.

If you ever want to create a field that puts your data together, there's a function for that. This is actually the better way to create your original SKU Product List - start with all of the elements in separate columns and then combine them with your favorite new function, CONCATENATE:

=CONCATENATE(B2&" "&C2)

Note the use of quote marks with a space between the cell contents so they don't run together. Neat trick, and quite the timesaver with long lists.

Suzanne