When we import data in Excel, we may often encounter some Inverted Comma in data that creates problems while calculating data because it may change the data type as well. So in this article, we will learn how to remove inverted comma in Excel using different approaches.
How to Remove Inverted Comma in Excel: 7 Easy Methods
There are several methods to remove inverted commas in Excel. Since the inverted comma is inserted by excel and does not affect the value, if we can return the value, we can have the data inverted comma free. So all the possible methods are described below with proper steps.
1. Remove Inverted Comma in Excel Using Multiplication
We can always get the values of any cell data by multiplying it by 1. Since multiplying only returns the value of the string or number in Excel, we can get the inverted comma removed in another cell by multiplying it with 1. For this method, the steps are below.
🔶 Steps:
- First, we will write 1 in any random cell of the worksheet and copy the cell by pressing Ctrl+C.
- Second, select the entire cell range where we want to remove inverted commas.
- Third, go to the Home tab of the ribbon and select Paste Special. A dialog box will appear.
- In the box, select Multiply and click on OK.
- Since we have multiplied the values by 1, we will get the values only without the inverted comma like in the following image.
2. Using SUBSTITUTE Function to Remove Inverted Comma
We can also use the SUBSTITUTE function to replace and remove inverted commas. If we can detect the inverted comma and replace it by a blank, we can literally get the desired data without inverted commas. The steps of this method are below.
🔶 Steps:
- Firstly we will need to create separate columns for the method where we will store the inverted comma-removed data. Select one cell of those columns and write the following formula in the formula bar.
=SUBSTITUTE(C6," ' "," ")
Here, in the formula, C6 is the targeted cell from where we will import the data. The second portion signifies which sign/letter/number to replace and the last portion implies what we will give in the place of replacement.
- Secondly, we will press Enter to see the output. Here we will see the inverted comma is removed from the previous data in cell C6.
- Thirdly drag the Fill Handle vertically downwards to get the other data without inverted commas as well.
The output will be like the following:
- Now select the cell F6 and type the following formula in the formula bar and press Enter.
=SUBSTITUTE(D6," ' "," ")
It will show the data of D6 without the inverted commas.
- Again drag the Fill Handle to get data from the rest of the cells.
Read More: How to Remove Comma in Excel Using Formula
3. Flash Fill Feature to Remove Inverted Comma
We can use the Flash Fill feature to remove inverted commas in Excel. The flash fill automatically fills up the data based on a pattern or previous cell action. The steps to this method are below.
🔶 Steps:
- First, we will create a new column adjacent to the column containing data with inverted commas.
- Second, select the first adjacent cell to the inverted comma containing data and write the entire first data without the inverted comma. We can copy and paste the data without the inverted comma if the data is too long.
- Third, press Enter to visualize the new data that doesn’t contain the inverted comma.
- Now select the newly containing cell. The fill handle will be shown on the right side. Drag the Fill handle to the end of the column to get the rest of the data without inverted commas like the below image.
The table will now look like the below image.
- Then we will select the tiny box at the end of the Fill Handle box and select Flash Fill.
- Lastly, we will get an image like the following:
4. Using LEFT and LEN Functions
We will use LEFT and LEN functions to find out the string length and by that method, we can remove the inverted comma by finding out the position of the inverted comma. The steps are below.
🔶 Steps:
- At first, we will create a new column. We will select the first cell of the new column where we will get our first data without inverted commas and write the following formula in the formula bar.
=LEFT(C6,LEN(C6))
Here the LEFT function decides the side of the string from where we will start. Here the inverted comma was on the left, so we selected the LEFT function. The LEN function indicates the length of the string but does not take the inverted comma into account. So it avoids the inverted comma and takes the actual value only.
- Then pressing Enter will give us the desired cell data without inverted commas.
- At last drag the Fill Handle to fill up the rest of the cells.
5. Applying NUMBERVALUE Function
We can use the NUMBERVALUE function to extract the value of the cell content and paste it into the other cell. The steps are given below.
🔶 Steps:
- First, select the targeted cell where we want to get the new value like the image below.
- Then write the following code in the formula bar and press Enter.
=NUMBERVALUE(C6)
- Eventually, drag the Fill Handle to fill up the rest of the cells.
6. Use of REPLACE and LEN Functions to Remove Inverted Comma
We can use the combination of REPLACE and LEN functions to detect and replace the inverted comma. The steps are below.
🔶 Steps:
- In this method, we will also select a new cell where we want to get the data without inverted commas and write the following code in the formula bar.
=REPLACE(C6,LEN(C6)+1,LEN(C6),"")
- So we will get the data of the C6 cell without the inverted comma in the new cell like in the image below.
- Then drag the Fill Handle to fill up the rest of the cells.
- For the ID. Number column, we can do the same except the formula will be like the following:
=REPLACE(D6,LEN(D6)+1,LEN(D6),"")
- Again drag the Fill Handle to fill up the rest.
7. Applying Text to Column Feature
Here we can use the direct Excel-defined option to remove inverted commas in excel. In this method, we will not create any separate columns for removing inverted commas. The steps are as below.
🔶 Steps:
- In the beginning, we will select a column containing the data with inverted commas.
- Then we will go to the Data tab in the Ribbon.
- In the Data tab, select the Text to Columns option in the Data Tools section.
- Next, we will see a dialog box like the below image. Click on Next.
- Again click on Other and write the inverted comma symbol (‘) in the box and click on Next.
- In the next tab, select Finish like the below image.
As a result, we will get our data table like the image below.
- Repeating the entire process for the other column as well, we will get results like the following.
Things to Remember
- The SUBSTITUTE function replaces all the inverted commas with a blank space. So we get the actual string value.
- The Flash Fill feature distinguishes two cells containing similar data and finds out the differences. Then apply the differences to the rest of the cells.
- Applying the Text to Column feature will convert all numeric values to numbers, date values to date, and all other formats to text.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
These are all the methods that answer the question of how to remove inverted comma in Excel. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions.
