1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Truncate Text Data in Excel with the LEFT Function


Remove Unwanted Characters from Data in Excel
Truncate Text Data in Excel

Truncate Text Data in Excel

© Ted French

Truncate Text Data in Excel with the LEFT Function

When data is imported or copied into an Excel spreadsheet unwanted characters or words can sometimes be included with the new data.

Excel has several functions that can be used to truncate or remove such unwanted characters. Which function you use depends upon where the unwanted characters are located in the cell.

  • If you have unwanted characters on both sides of your good data, use the MID function to remove them.

  • If these unwanted characters appear on the left side of your good data, use the RIGHT function to remove them.

  • If the unwanted characters are on the right side of your good data, use the LEFT function to remove them.

The LEFT Function

The syntax for the LEFT function is:

= LEFT ( Text , Num_chars )

Text - the piece of data you want to change.

This can be a cell reference indicating where the data is stored.

Num_chars - specifies the number of characters to be retained from the Number specified above.

Example Using Excel's LEFT Function to Truncate Text

For help with this example see the image above.

  1. Enter the following data into cell D1: 456*&^%

  2. Click on cell E1 - this is where the results of the function will be displayed.

  3. Click on the Formulas tab of the ribbon menu.

  4. Choose Text from the ribbon to open the function drop down list.

  5. Click on LEFT in the list to bring up the function's dialog box.

  6. In the dialog box, click on the Text line.

  7. Click on cell D1 in the spreadsheet.

  8. Click on the Num_chars line.

  9. Enter the number " 3 " (no quotes) on this line since we only want to keep the three leftmost characters of data.

  10. Click OK.

  11. The function will remove the unwanted characters ( *&^% ) leaving just the number 456 in cell E1.

  12. When you click on cell E1 the complete function = LEFT( D1, 3 ) appears in the formula bar above the worksheet.

Related Tutorials

©2014 About.com. All rights reserved.