Removing characters in an Excel cell

March 28, 2016

0 comments



We’re a team of problem solvers, world travelers, idea guys, and doers, working to make using Excel on a Mac a better experience.

That’s enough about us -- whether you are new to Excel for Mac or an experienced user, you probably want to spend less time manipulating data and more time thinking. The Excel Skin will help you do that. If you are a PC user hesitant about switching to Mac because you don’t want to give up your customized menus and shortcuts, well, the Excel Skin just might be the myth-buster you need. Excel for Mac is just as capable as it is for the PC. And with the Excel Skin, you’ll relearn your old shortcuts in no time.

The Excel Skin makes it easy to be as proficient and efficient in Excel for Mac as on any other platform. Powerful shortcuts are presented intuitively on an elegant silicone keyboard skin. Shortcut and modifier keys are printed by color and heat fused onto our high quality silicone to ensure durability.

The Excel Skin a small product that will make a big difference in your life.

 

How to remove characters in an Excel cell                       

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:

=RIGHT(A1, LEN(A1)-5)

 

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!



Leave a comment

Comments will be approved before showing up.