There have been multiple times where I export some data from a website, and the export adds unwanted characters in each cell. There is always that slight pause and dilemma of going through each cell and manually deleting these characters, or just leaving them as they were exported. In this blog post we will tackle how to remove those characters with one simple Excel function.
For this example, our export added the abbreviation “cust_” before each customer ID:
Next to the first cell type the following formula:
This formula tells Microsoft Excel to count 5 characters in from the right of cell A1. Once you click enter, it will return the remaining characters (in this example the customer ID number).
Now click the cell B1 with only the customer ID number showing. Double click the square in the bottom right of the cell and this formula will be applied to the entire column.
And it is as simple as that! You can edit the =RIGHT(A1, LEN(A1)-5) formula to target any cell or number of digits.
Here is the raw formula: =RIGHT(CELL, LEN(CELL)-CHARACTER SPACES)
Just replace CELL with the cell you are targeting, and CHARACTER SPACES with the number of spaces from the beginning of the cell.
Note, you can also count from the end of the cell by replacing RIGHT with LEFT in your formula.
As always, please let us know if you have any questions and we will be happy to help!
Comments will be approved before showing up.