Technoration.com : Technorate Your World!: Extract text from a cell in Excel

Sometimes it is useful (or necessary) to extract part of a cell into another cell in Excel. For example, you may have a cell that contains a combination of text and numbers, or a cell that contains two numbers separated by a delimiter such as a comma.

To do this, you can use one of the text manipulation functions available in Excel. These include LEFT() and RIGHT(). You may also need to use the LEN() function, which calculates the length of a value in a cell.

RIGHT() function syntax

The RIGHT() function has the following syntax:

=RIGHT(text, number of characters)

The RIGHT() function works by taking the specified number of characters starting from the right end of the text value entered.

Example of the RIGHT() function in action

To use the RIGHT() function, follow these steps:

Click on a cell and enter the RIGHT() function:
Enter the two arguments for the function as shown in the following example:
The value shown in the third cell is the result of the formula, i.e. the five characters taken from a starting point of the right-most value (i.e. the last character) in the cell (A3 in this example).
- Note that in this example, the result of the RIGHT() formula, 12345, is a number and will be treated as such by Excel.
- This means that the result of the formula in this example can be used in other numerical calculations within your Excel spreadsheet.

LEFT() function syntax

The LEFT() function has the following syntax:

=LEFT(text, number of characters)

The LEFT() function works by taking the specified number of characters starting from the left end of the text value entered.

Example of the LEFT() function in action

To use the LEFT() function, follow these steps:

Click on a cell and enter the LEFT() function:
Enter the two arguments for the function as shown in the following example:
The value shown in the third cell is the result of the formula, i.e. the 6 characters taken from a starting point of the left-most value (i.e. the first character) in the cell (A5 in this example).

MID() function syntax

The MID() function has the following syntax:

=MID(text,start_num,num_chars)

The MID function is similar to the LEFT function, except you can specify which point in the text string from which to start extracting text.

Example of the MID() function in action

In the previous example, you might enter this formula:

=MID(A5,7,3)
This would return 123 as the result; these are the three characters in the string "wilson12345", starting from the 7th character in the string.

FIND() function syntax

The FIND function is very useful in the above functions if you don't know the num_chars (i.e. the number of characters to extract) or, in the case of the MID() function, you don't know the start_num (where to start extracting text from). The FIND function can help you calculate this, which means you can write LEFT, RIGHT or MID functions that work with text strings of different lengths and components.

Examples of the FIND() function in action.

Let's work with an example where we have two text strings of different lengths, and we need to write a formula that will work with both of them.

Our example strings are wilson.12345 and tennyson.4523. We'll assume that the first value is in A1 and the second string is in A2.
Note that the number of characters and the number of digits is different in each example.
We need to convert both strings so that we are extract only the numbers from each.

The formula we need would like this:

=RIGHT(A1,LEN(A1)-FIND(".",A1))

This formula says: Take values from the right of the string, starting from the point immediately after the decimal point.

The RIGHT function says take from the right, not the left or somewhere in the middle
The num_chars value in the RIGHT function is calculated by taking the length of the value in A1 (using the LEN function) and subtracting the position of the decimal point from the length of the cell (which is determined using the FIND function).

This function will work on both of our example strings of text because the starting point is calculated using the length of the string and the position of the decimal point.

Note that this is a simple example. More complex examples might include strings that include more than one decimal point (you'd have to use the MID function as part of the solution in this case), or strings that don't have a clear delimiter (like the decimal point in this example).

There is a lot more you can do with text manipulation of cells in Excel. The LEFT(), RIGHT() and MID() functions are essential building blocks to any kind of text manipulation or text extraction in Excel.