Home // Blog & Excel Tips // How to remove unwanted characters in Excel spreadsheets through the Left, Right and Mid functions

Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.

 

How to remove unwanted characters in Excel spreadsheets through the Left, Right and Mid functions

Comments Closed
Uncategorised

When you import data or copy content from another application into Excel spreadsheets more often than not, you will be left with some characters that you want to remove. The Left, Right and Mid functions available in Excel are useful for this purpose. You can also use the Left, Right and Mid functions to cut up the content of a specific cell in order to use it for another purpose. As such these are excellent functions. If you understand their uses it will allow you to manipulate the content of cells to ensure accurate and usable Excel spreadsheets.

Below a YouTube video on using the LEFT, RIGHT and MID functions

When to use each of the functions in Excel spreadsheets

If you import information from another application and you want to for instance, split up the data you will use the functions as follows:

RIGHT: to identify and remove unwanted characters on the left side of the data that you want to keep.
LEFT: to identify and remove unwanted characters on the right side of the data you want to keep.
MID: to identify and remove unwanted characters in the middle of data you wish to retain.

Not only can you remove characters, but you will also be able to copy those characters to cells where you want and need them. You may for instance, import a specific set of account numbers into a worksheet and now wish to make the data usable for comparison with other reports. The account numbers are made up of for instance city, customer number, and then the department number. If we want to separate the information to keep or copy the information for the city or the department to other worksheets or cells we can make use of the above functions.

View our training video on the steps to use the LEFT, RIGHT and MID functions in Excel spreadsheets.