Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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 in Excel.

Overview Image


Download to Practice


14 Ways 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 three columns in the dataset; these are Product & ID, Order Date, and Delivery Details.

Dataset

Not to mention, I have used Microsoft 365 version. You may use any other version at your convenience.


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.

Steps:

  • Firstly, select a cell where you want to keep your resultant value. Here, I selected the E5 cell.
  • Then, type the following formula in the selected cell or into the Formula Bar.
=REPLACE(D5, 1, 13, "")

Let’s see how the REPLACE function works. It has four arguments. In the old_text selected the D5 cell, and in start_num given 1, select 13 as num_chars. Finally, I selected blank space(“”) as new_text.

Here, I want to replace 13 characters from the beginning(1) with a blank space.

  • Therefore, press ENTER key. Now, you’ll get the replaced character which is the Delivery Address.

Using REPLACE Function to remove character from String

Note: 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. Applying 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.

Steps:

  • Firstly, select a cell where you want to keep your resultant value. Here, I selected the E5 cell.
  • Then, type the following formula in the selected cell or into the Formula Bar.
=RIGHT(B5, 4)

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

  • Apparently, press the ENTER key. Now, you’ll get the right 4 characters which are Order IDs.

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.

Using RiGHT Function to remove character from String


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


3. Employing RIGHT & LEN Functions 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 the LEN function,

Steps:

  • Firstly, in cell E5 insert the following formula.
=RIGHT(B5,LEN(B5)- 6)

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.

  • Eventually, press ENTER. Thus, you’ll get the right characters which will be Order ID.

Using RIGHT and LEN Function to remove character from String


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


4. Incorporating VALUE & RIGHT Functions

Previously, by using the RIGHT and LEN functions, 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.

Steps:

  • Firstly, select a cell where you want to keep your resultant value. I selected the E5 cell.
  • Secondly, type the following formula in the selected cell or into the Formula Bar.
=VALUE(RIGHT(B5, LEN(B5)-6))

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

You can see the use of the RIGHT & LEN functions in the method_3.

Incorporating VALUE and RIGHT Functions


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


5. Utilizing 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,

Steps:

  • Firstly, in cell E5 insert the below formula.
=LEFT(B5, 5)

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

Using LEFT Function to remove character from String


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


6. Engaging LEFT & LEN Functions

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,

Steps:

  • Firstly, move to cell E5 and insert the following formula.
=LEFT(B5, LEN(B5) - 5)

Here, to calculate the num_chars using the LEN function. In the text selected the B5 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.

In the end, you’ll get the left characters of the selected cell which will be the name of the Product.

  • Lastly, drag down the Fill Handle tool to get the same formula as other cells.

Engaging LEFT & LEN Functions


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


7. Applying LEFT & SEARCH Functions 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,

Steps:

  • Initially, in cell E5, type the following formula.
=LEFT(D5, SEARCH("_at_", D5) -1)

Here, the SEARCH function will search the given character “_at_” as I used it as find_text.In the within_text selected the D5 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 D5 cell to extract the left value from the searched character.

  • Consequently, press 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.

  • Lastly, you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Applying LEFT and SEARCH functions


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


Similar Readings:


8. Implementing 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.

Steps:

  • Firstly, select a cell where you want to keep your resultant value. Here, I selected the E5 cell.
=SUBSTITUTE(B5,"%","")

Here, the SUBSTITUTE function used the B5 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 a blank.

  • Apparently, press ENTER key.

Hence, the characters from the string will be removed.

  • Finally, drag down the Fill Handle tool to get the following output.

Implementing SUBSTITUTE function


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


9. Employing MID and LEN Functions 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.

Employing MID and LEN functions

Let’s start the procedure,

Steps:

  • In cell E5, type the following formula.
=MID(B5,7, LEN(B5)-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 B5 cell then subtracted 5 characters from the length of the selected text.

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

  • Eventually, drag it down after pressing ENTER.

MID and LEN functions


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


10. Applying Flash Fill Feature

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.

Steps:

  • Initially, navigate to the Home tab >> choose the Fill from Editing group >> select the Flash Fill option.

Applying Flash Fill Feature

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

Final Output of Flash Fill Feature


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


11. Enforcing Find & Replace to Remove Character from String

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

Let me show you the use of this feature.

Steps:

  • Firstly, open the Home tab >> go to Editing group and choose to Find & Select  >> select Replace.

ENforcing Find and 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 Replace All.

Find and Replace window

A message about how many replacements occurred will pop up. Here, it will show 30 replacements.

Warning Box

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

Final Output of Find and Replace


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 characters from strings in Excel.

Steps:

  • For using that, first, open the Data tab >> then select the Text to Columns option.

Text to columns feature

  • Then, a dialog box will pop up. From there choose the data type. I’ve chosen Delimited.
  • Then, click Next.

Convert text to Columns wizard Step 1

  • 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.

Convert text to Columns wizard Step 2

  • 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.

Convert text to Columns wizard Step 3

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

Final Output of Text to columns feature


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


13. Incorporating VBA to Remove First N Characters from String

To remove characters from a string you can use Excel’s VBA Macro feature.

Let’s see the use of the VBA editor to remove the first N characters from a string.

Steps:

  • First, hover over the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11 )

Open Developer tab

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

Visual basic Editor

  • Apparently, a Module will open then type the following code in the opened Module.
Public Function RFV(RV As String, CT As Long)
RFV = Right(RV, Len(RV) - CT)
End Function

Code to Remove first character from string

Code Breakdown

  • Here, I have created a function named RFV().
  • I’ve taken two arguments for the function one is RV which is String type variable, another one is CT 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 E5 cell.
  • Then, type the following formula in the selected cell or into the Formula Bar.
=RFV(B5,6)

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

  • Eventually, press ENTER. Now, you’ll get the characters you want.

Remove character from string with VBA in Excel

 


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


14. Applying VBA to Remove Last N Characters from String

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

Now, let’s see the use of the VBA editor.

Steps:

  • First, open the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11 )

Open Developer tab

Now, it will open a new window of Microsoft Visual Basic for Applications.

  • From there, open Insert >> select Module.

Visual basic Editor

  • A Module will open then type the following code in the opened Module.
Public Function RLV(RV As String, CT As Long)
RLV = Left(RV, Len(RV) - CT)
End Function

VBA code to remove last character from string in Excel

Code Breakdown

  • Here, I have created a function named RLV()
  • I’ve taken two arguments for the function one is RV which is String type variable, and another one is CT 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 E5 cell.

  • Then, type the following formula in the selected cell or into the Formula Bar.
=RLV(B5,6)

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

  • Subsequently, press ENTER. Now, you’ll get the rest of the characters.

Using RLV function


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.

Practice Section


Conclusion

That’s all about today’s session. And these are some easy methods to remove character from string in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of excel methods. Thanks for your patience in reading this article.


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