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.
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.
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 in Excel
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.
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. Removing Character from String with Excel 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.
Read More: Excel Remove Characters From Right (5 ways)
3. Combining RIGHT & LEN Functions to Remove Character from String in Excel
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.
Read More: How to Remove Characters from Left in Excel (6 Ways)
4. Inserting Excel VALUE & RIGHT Functions to Delete Character from String
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.
Related Content: Remove Last Character from String Excel (5 Easy Methods)
5. Using LEFT Function to Remove Character from String in Excel
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.
Read More: How to Remove First Character from String in Excel (6 Easy Ways)
6. Applying LEFT & LEN Functions to Erase Character from String
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.
Related Content: Remove Last Character from String in Excel with VBA (2 Easy Ways)
7. Using LEFT & SEARCH Functions to Remove Character from String in Excel
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.
Read More: VBA to Remove Characters from String in Excel (7 Methods)
Similar Readings
- How to Remove Single Quotes in Excel (6 Ways)
- Remove Semicolon in Excel (4 Methods)
- How to Remove Apostrophe in Excel (5 Easy Methods)
- Remove Parentheses in Excel (4 Easy Ways)
- How to Remove Dollar Sign in Excel (7 Easy Ways)
8. Deleting Character from String with SUBSTITUTE Function in Excel
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.
Related Content: How to Remove Blank Characters in Excel (5 Methods)
9. Combining MID and LEN Functions to Remove Character from String in Excel
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.
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.
Read More: Remove Specific Character from String Excel (5 Methods)
10. Applying Excel Flash Fill Feature to Remove Character from String
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.
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. Erasing Character from String with Excel Find & Replace Option
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.
- 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.
A message about how many replacements occurred will pop up. Here, it will show 30 replacements.
Therefore, from all the strings the given character will be removed.
Related Content: How to Remove First 3 Characters in Excel (4 Methods)
12. Using Text to Columns to Remove Character from String in Excel
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.
- Then, a dialog box will pop up. From there choose the data type. I’ve chosen Delimited.
- Then, click Next.
- 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.
- 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.
Related Content: How to Remove Numeric Characters from Cells in Excel (5 Methods)
13. Applying Excel VBA to Get Rid of 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 )
- Then, it will open a new window of Microsoft Visual Basic for Applications. From there, open Insert >> select Module.
- 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 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.
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.
Now, let’s see the use of the VBA editor.
Steps:
- First, open the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11 )
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 RLV(RV As String, CT As Long)
RLV = Left(RV, Len(RV) - CT)
End Function
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.
Related Content: How to Remove the First Character from a String in Excel with VBA
Download Practice Workbook
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.
