If you have a large text file in an Excel column that you need broken up and used in other cells, the LEFT, RIGHT and MID functions will make light work of it. This is especially true if all of the text is the same length.
This is because the syntaxes for LEFT, RIGHT and MID are as follows:
LEFT(text,num_chars)
RIGHT(text,num_chars)
MID(text,start_num,num_chars)
What this means is that Excel will count the number of characters you specify to “cut” and use the text in the cells to which the function is applied.
For example, we have a text list with numbers in ten-digit phone number format. We want to break down the list into an area code column, a prefix column, and a suffix column.
click to enlarge
click to enlarge
Next, we want to extract text from the first column and fill in the Prefix column. To begin, we will select the first cell in the Prefix column and go to the Formula Bar. Enter =MID( and Excel prompts us for the text, start number and number of characters we want to use. We know we want to extract the text from cell B4. We know the start number is “5” because we want to start with the fifth character (we don’t want to use the dash). Finally, we know that we want to extract three characters. Therefore, the formula for this cell will be =MID(B4,5,3).
click to enlarge
Once we have the formulas entered into each of the initial cells, we can select those three, grab the fill handle, and copy the formulas all the way down.
click to enlarge
If you have a different situation and need to combine text from different columns, check out this Bright Hub tutorial, Use Excel’s CONCATENATE Function to Combine Cells. Also, if you're looking for more tips and tricks, be sure to take a look at the other Microsoft Excel function tutorials available.
