Clients are always asking me why they see extra characters in Excel. There could be many explanations to this. In this Excel tip, I'll show a common explanation for this and show how to correct it using the =LEFT function. Usually the reason is that the data came from another location, such as a comma-delimited file. Often when the data comes from another source, extra characters may appear. You may simply have data that contains similar information and want to group accordingly.
With any of these situations, truncating characters or displaying the left-most characters will provide clients with the data needed.
Follow these simple steps to use the =LEFT function and show only the data you want to show.
Create a new column for the truncated data.
Type "=LEFT(A2,4)" where "A2" is the cell containing the full data and "4" is the number of characters you want to display. If you would like to show six (6) characters, type "6" after the comma.
Copy the formula to all cells where data truncation is required.
The new column now shows the truncated data.
Let's show another example. Let's say your Human Resources Department would like to group all employees by the first letter of their last name. Simply follow the steps above, but change the formula to the following.
This will default to displaying only the first letter of field A2 in the cell with the "=LEFT" formula.
Should you need to show the right portion of your data, use the "=RIGHT" function in the same manner.
Simply follow the same steps above, only this time the number following the comma causes Excel to display the number of characters from the right side of the field.
Let's use this example.
Setup a new column with the formula =RIGHT(A2,4) placed in the cell.
Column A contains inventory codes with a 4 letter code at the end of each number. Let's say the 4 letter code is a department code. If field A2 contains "98765MARK," and "4" is used in the formula, the data returned in the new column would be "MARK."
As you can see, the =LEFT and =RIGHT functions in Excel can really help you display the data needed, while using a simple formula.