How to Remove Character from String in Excel (14 Ways)

Removing characters from string or any other values is necessary because data comes from various sources and may not be the one you want or not in the format you want. By removing characters from a string as per your need you may create a new dataset. Not only these reasons but also data may contain unwanted characters. Following this article, you will get to know how to remove character from string Excel.

To make the explanation clearer I’m going to use a dataset where I tried to cover various types of character related problems. The dataset represents information about a particular fruit shop. There are 3 columns in the dataset; these are Product & ID, Order Date, and Delivery Details.

Sample dataset to Remove Character from String

 


Download to Practice


14 Ways to Remove Character from String Excel

1. Using REPLACE Function to Remove Character from String

Here, I’m going to explain how you can use the REPLACE function to remove characters from a string.

From the Delivery Details column, I will remove the delivery status and extract only the Address using the REPLACE function.
Let’s start the procedure of using the REPLACE function.

Step1:
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=REPLACE(D4, 1, 13, "")

REPLACE Function to Remove Character from String

Let’s see how the REPLACE function works. It has four arguments. In the old_text selected the D4 cell, and in start_num given 1, selected 13 as num_chars. Finally, I selected blank space(“”) as new_text.
Here, I want to replace 13 characters from the beginning(1) with blank space.

Step2:
Press the ENTER key. Now, you’ll get the replaced character which is the delivery Address.

If you have the same number of characters to remove from the other cells then you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: How to Remove Characters in Excel (6 Methods)


2. Using RIGHT Function

You can use the RIGHT function to remove characters from a string from the left side.

From the Product & ID column, I will extract only the Order Id using the RIGHT function.
Here, I’ll show you how you can use the RIGHT function.

Step1:
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=RIGHT(B4, 4)

 RIGHT Function to Remove Character from String

Here, the RIGHT function has two Arguments. In the text selected the B4 cell, and in num_chars given 4 as I want to extract 4 characters from the right.

Step2:
Press the ENTER key. Now, you’ll get the right 4 characters which is Order Id.

Step3:
As I want to extract the same number of characters from the right so I used the Fill Handle to AutoFill the formula for the rest of the cells.

 RIGHT Function to Remove Character from String

Read More: Excel Remove Characters From Right (5 ways)


3. Using RIGHT & LEN Function to Remove Character from String

You also can use the RIGHT function along with the LEN function to remove characters from a string.

To demonstrate the procedure, from the Product & ID column I will extract only the Order Id. 
Let’s start the procedure of using the RIGHT function along with LEN function,

Step1:
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=RIGHT(B4,LEN(B4)- 6)

RIGHT & LEN Function to Remove Character from String

Here, calculate the num_chars using the LEN function. In the text selected the B4 cell then subtracted 6 characters from the length of the selected text. Finally, the RIGHT function will extract the remaining characters from the right.

Step2:
Press the ENTER key. Thus, you’ll get the right characters which will be the Order Id.

If you have the same number of characters to remove from the other cells then you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: How to Remove Characters from Left in Excel (6 Ways)


4. Using VALUE & RIGHT Function

Previously, by using the RIGHT and LEN function, we got the Order Id number but these functions treat numbers as strings. In case you want to convert the strings to numbers you can use the VALUE function.

Step1:

First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=VALUE(RIGHT(B4,LEN(B4)-6))

VALUE & RIGHT Function to Remove Character from String

Here, the VALUE function will take the extracted characters from the right as text and will convert it into a number.

You can see the use of RIGHT & LEN function in section_3

Step2:
Press the ENTER key. Thus, you’ll get the right characters in number format.

If you have the same character to remove from the other cells then you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Related Content: Remove Last Character from String Excel (5 Easy Methods)


5. Using LEFT Function to Remove Character from String

To remove the string characters from the left you can use the LEFT function.

Here, I will extract the Product name from the Product & ID column.
Let’s start  the procedure,

Step1:
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=LEFT(B4, 5)

Using LEFT Function to Remove Character from String

Here, the LEFT function has two Arguments. In the text selected the B4 cell, and in num_chars given 5 as I want to extract 5 characters from the left.

Step2:
Press the ENTER key.
As a result, you’ll get the left 5 characters which will be the Product name.

If you have the same character to remove from the other cells then you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: Remove First Character from String in Excel (6 Quick Ways)


6. Using LEFT & LEN Function

To remove string characters from the right side you also can use the LEFT function along with the LEN function.

To demonstrate the procedure, from the Product & ID column I will extract only the Product. 

Let’s start  the procedure,

Step1:
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=LEFT(B4, LEN(B4) - 5)

Using LEFT & LEN Function to Remove Character from String

Here, to calculate the num_chars using the LEN function. In the text selected the B4 cell then subtracted 5 characters from the length of the selected text. Finally, the LEFT function will extract the remaining characters from the right of the selected cell value.

Step2:
Press the ENTER key.
In the end, you’ll get the left characters of the selected cell which will be the name of the Product.

Step3:
You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Using LEFT & LEN Function to Remove Character from String

Related Content: Remove Last Character from String in Excel with VBA (2 Easy Ways)


7. Using LEFT & SEARCH Function to Remove Character from String

You can use the SEARCH function with the LEFT function to remove string characters from the right side and it will keep the left value.

To demonstrate the procedure, from the Delivery Details column I will extract only the left value which will be delivery Status.

Let’s jump into the procedure,

Step1:
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=LEFT(D4, SEARCH(“_at_”, D4) -1)

Using LEFT & SEARCH Function to Remove Character from String

Here, the SEARCH function will search the given character _at_ as I used it as find_text.In the within_text selected the D4 cell then subtracted 1 character.
The SEARCH function will give the position number then the LEFT function will use it as num_chars and also select the D4 cell to extract the left value from the searched character.

Step2:
Press the ENTER key.
In the end, you’ll get the left characters of the searched character from the selected cell which will be the name delivery Status.

Step3:
You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Using LEFT & SEARCH Function to Remove Character from String

Read More: VBA to Remove Characters from String in Excel (7 Methods)


Similar Readings:


8. Using the SUBSTITUTE Function

The SUBSTITUTE function substitutes any given character. By using this function, you can remove string characters from Excel.

To demonstrate the procedure, I used some unwanted special characters in the datasets Product column.

Using the SUBSTITUTE Function to Remove Character from String

Step1:
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=SUBSTITUTE(B4,"%","")

Here, in the SUBSTITUTE function used the B4 cell as text, given the character (%) as old_text, then used blank as new_text.
So, the SUBSTITUTE function will substitute the given character with blank.

Step2:
Press the ENTER key.
Hence, the characters from the string will be removed.

 

Using the SUBSTITUTE Function to Remove Character from String

Step3:
You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Related Content: How to Remove Blank Characters in Excel (5 Methods)


9. Using MID and LEN Function to Remove Character from String

You also can use the MID function along with the LEN function to remove characters from a string.

To demonstrate the procedure, I slightly changed the dataset.

Using MID and LEN Function to Remove Character from String

Let’s start the procedure,

Step1:
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=MID(B4,7,LEN(B4)-5)

Here, by using the LEN function I will calculate the num_chars and use it in the MID function. In the text selected the B4 cell then subtracted 5 characters from the length of the selected text.

Now, in the MID function selected B4 cell as text, used 7 as start_num as I want to extract the characters from the 7th position.

Step2:
Press the ENTER key.
Lastly, you’ll get the Order Id where the rest of the string will be removed.

Using MID and LEN Function to Remove Character from String

Step3:
You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Using MID and LEN Function to Remove Character from String

Read More: Remove Specific Character from String Excel (5 Methods)


10. Using Flash Fill 

You can use the Flash Fill command to remove string characters in excel.
To use the Flash Fill command first, you will need to create a pattern to follow.

Here, I’ve created a pattern of delivery Status by removing the Address part from the Delivery Details column.
Then, open the Data tab >> select Flash Fill

Using Flash Fill to Remove Character from String

Here, the rest of the characters of cells are removed by following the pattern.

Related Content: Remove Characters after a Specific Character in Excel (4 Tricks)


11. Using Find & Replace to Remove Character from String

You have another convenient option to remove characters by using the Ribbon That is the Find & Replace feature.

Let me show you the use of this feature.
First, open the Home tab >> go to Editing group >> from Find & Select  >> select Replace

Using Find & Replace to Remove Character from String

➤A dialog box of Find and Replace will pop up.

From there in Find what, provide the character you want to find to remove.
➤I’ve provided the character (%) and kept the Replace with blank.

Then, click Find All

Now, you will see which cell’s value contains that character.
➤Then, click Replace All

Using Find & Replace to Remove Character from String

A message of how many replacements occurred will pop up.
➤Here, it will show 15 replacements.

Therefore, from all the strings the given character will be removed.

Using Find & Replace to Remove Character from String

Related Content: How to Remove First 3 Characters in Excel (4 Methods)


12. Using Text to Columns

You also can use the Text to Columns command from the ribbon to remove character from strings in Excel.

Step1:
For that, first, open the Data tab >> then select Text to Columns

Using Text to Columns to Remove Character from String

Step2:
➤ A dialog box will pop up.

From there choose the data type
➤ I’ve chosen Delimited then click Next

Step3:
➤Another dialog box will pop up

Now select the Delimiters your data has.
➤ I selected underscore (_) in the Other option as my data has.

You can see how your data will be split in the Data preview

Then, click Next.

Using Text to Columns to Remove Character from String

Step4:
➤ Again a dialog box will pop up.
From there select the destination of your choice to place the separated values.

➤I selected the E4 cell.
Finally, click Finish.

Here, you will see the values of the Delivery Details column are separated into 3 new columns.

Using Text to Columns to Remove Character from String

From the 3 new columns, I don’t need the 2nd column so I will delete it.
Now, select the cell and right click on the mouse then from the context menu select Delete.

➤A dialog box of the Delete option will appear.
➤I selected the Shift cells up option then clicked OK.

Using Text to Columns to Remove Character from String

Finally, you will see that, the particular character is removed from the string of the Delivery Details column.

Related Content: How to Remove Numeric Characters from Cells in Excel (5 Methods)


13. Using VBA to Remove First N Characters from String

To remove characters from a string you can use the VBA.

Let’s see the use of VBA editor,
First, open the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11 )

Using VBA to Remove First N Characters to Remove Character from String

Then, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open then type the following code in the opened Module.

Public Function RemoveFirstx(rng As String, cnt As Long)
RemoveFirstx = Right(rng, Len(rng) - cnt)
End Function

Using VBA to Remove First N Characters to Remove Character from String

Here, I have created a function named RemoveFirstx()
I’ve taken two arguments for the function one is rng which is String type variable, another one is cnt which is Long type variable.
Then used the RIGHT function.

Now, Save the code and go back to the worksheet.

To use the created function,
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=RemoveFirstx(B4,6)

The RemoveFirstx function will remove the number of characters I provided in the selected range from the beginning.

Press the ENTER key. Now, you’ll get the characters you want.

Using VBA to Remove First N Characters to Remove Character from String

Related Content: How to Remove Non-Alphanumeric Characters in Excel (2 Methods)


14. Using VBA to Remove Last N Characters from String

To remove the last characters from a string you can use the VBA.

Let’s see the use of VBA editor,
First, open the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11 )

Using VBA to Remove Last N Characters from String

Now, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open then type the following code in the opened Module.

Public Function RemoveLastx(rng As String, cnt As Long)
RemoveLastx = Left(rng, Len(rng) - cnt)
End Function

Using VBA to Remove Last N Characters from String

Here, I have created a function named RemoveLastx()
I’ve taken two arguments for the function one is rng which is String type variable, and another one is cnt which is Long type variable
Then used the LEFT function

Now, Save the code and go back to the worksheet.

To use the created function,
First, select a cell where you want to keep your resultant value.
➤ I selected the E4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

=RemoveLastx(B4,6)

The RemoveLastx function will remove the number of characters I provided in the selected range from the end.

Press the ENTER key. Now, you’ll get the rest of the characters.

Using VBA to Remove Last N Characters from String

You can use the Fill Handle to AutoFill formula for the rest of the cells.

Using VBA to Remove Last N Characters from String

Related Content: How to Remove the First Character from a String in Excel with VBA


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained ways. You can download it from the above link.


Conclusion

In this article, I’ve explained 14 different types of ways to remove character from string Excel. I’ve shown how you can remove characters from last, first, mid and etc. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo