How to remove last character in Excel

Excel is a very powerful tool to conduct multiple calculations with numeric data. However, it provides a variety of tools and functions to handle and manipulate text and alphanumeric data as well. Sometimes, when you receive data from someone else or from web sources, there are unwanted characters with the data in each cell just like shown in the dataset above;

We would like to get rid of the $ symbol added before each email address. For this purpose, we’ll follow the below mentioned methods,

• Flash Fill
• Replace function
• LEFT function
• Find and Replace Tool

All methods are very simple to use and let’s explore each of them by following the steps mentioned in the sections below.

Method 1 Use Flash Fill

Step 1 – Create a sample data point with clean data

• To use the Flash fill feature of Excel, we need to create sample data point for Excel to follow the pattern. So, write the desired email address without the $  sign, right in the cell next to the raw data as shown below.

abc@gmail.com

• Now we’ll use the Flash Fill too by pressing CTRL+E, this will clean the whole data by removing the first unwanted character i.e., $  sign from the data.

Method 2 Use REPLACE function

The syntax of this function is very simple as shown below,

REPLACE(old_text, start_num, num_chars, new_text)

• Old_text    

Text in which you want to replace some characters.

• Start_num    

The position of the character in old_text that you want to replace with new_text. We’ll use LEN(A2) because we want to remove the last character.

• Num_chars    

The number of characters in old_text that you want REPLACE to replace with new_text. We’ll again use 1 as we wish to remove only the last character.

• New_text  

The text that will replace characters in old_text. We’ll replace the first character by a void string using “” characters.

Step 1 – Create the appropriate formula using REPLACE

• We can use the REPLACE function to remove the first character from the text very easily. This function is pretty straightforward to use, therefore, the following formula can be used to remove the first character from the data contained in cell A2.

=REPLACE(A2,LEN(A2),1,””)

• This formula can be extended to the whole dataset by double clicking the fill handle as shown below;

Method 3 Use LEFT function

The syntax of this function is very simple as shown below,

LEFT(text, [num_chars])

The LEFT function has the following arguments

• Text    

This is the text string that contains the characters you want to extract. In our case, we wish to extract all characters except the last one.

• [num_chars]

This is an optional argument. It specifies the number of characters you want LEFT to extract, counting from LEFT side of the text string. So, in our case, we’ll use a number which will be 1 less than the total length of the string, because we wish to extract all characters except the last one. So, we’ll use LEN(A2) -1 to get the number 1 less than length of string.

• num_chars must be greater than or equal to zero.
• If num_chars is greater than the length of text, LEFT returns all of text.
• If num_chars is omitted, it is assumed to be 1.

Step 1 – Create the appropriate formula using LEFT

• We can use the LEFT function to remove the first character from the text very easily. We’ll use the following formula to remove the first character from the data contained in cell A2. We used LEN(A2) -1 to get the number 1 less than the length of the string and this will return all characters except the first one.

=LEFT(A2, LEN(A2)-1)

• This formula can be extended to the whole dataset by double clicking the fill handle as shown above.

Method 4 Use Find & Replace Tool

If you don’t like to use formulas and want to clean the data by keeping it in its original place then you should use the Find and Replace tool. However, this will change your source data as well. All above methods didn’t change the source data, rather we created another column for the cleaned data.

Step 1 – Use CTRL + H to open up Find & Replace Tool

• Press CTRL + H to open up the Find & Replace tool.
• Write $ in Find what and blank space in Replace with as shown above.

Step 2 – Use Replace All to clean data

• Press Replace All button to clean the data. It will automatically replace all instances of $ sign from the source data.