When working with text, you may often need to take the last characters from cells in a column and place them into a neighboring column. Or, you might want to keep only the last 2, 3, 4… N characters directly in the original cells.
Unfortunately, Excel does not have a built-in tool to do this in just a couple of clicks — but you can use a formula.
Last N characters — Excel formula
It’s fairly simple to display the last character or last N characters from a cell using a formula. The RIGHT function can help.
Example of extracting a single character from cell A1:
=RIGHT(A1;1) Keep the last N characters in cells in one click
Since formulas take time and always require creating additional columns, I developed dedicated tools for my add-in.
They are available from the !SEMTools panel in just a couple of clicks. You simply enter the number of characters to extract into the input box provided.
Advantages of this approach:
- The tool will process all selected data. You can select:
- a single cell,
- multiple cells,
- an entire column,
- or even multiple non-contiguous ranges
- You can undo the operation in one step
- The “Output result to the right” option lets you place the extracted data into neighboring column(s) instead of replacing the original
Example below:
These tools are available in the full version of the add-in, but you can try them for free.
P.S. After extracting the last characters into a neighboring column, you might also want to remove the last N characters from the original column.
This post is also available in RU.