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.
- 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.
- Enter the following data into cell D1: 456*&^%
- Click on cell E1 - this is where the results of the function will be displayed.
- Click on the Formulas tab of the ribbon menu.
- Choose Text from the ribbon to open the function drop down list.
- Click on LEFT in the list to bring up the function's dialog box.
- In the dialog box, click on the Text line.
- Click on cell D1 in the spreadsheet.
- Click on the Num_chars line.
- Enter the number " 3 " (no quotes) on this line since we only want to keep the three leftmost characters of data.
- Click OK.
- The function will remove the unwanted characters ( *&^% ) leaving just the number 456 in cell E1.
- When you click on cell E1 the complete function = LEFT( D1, 3 ) appears in the formula bar above the worksheet.