How to remove apostrophe in excel

One of the keyboard punctuation marks is the apostrophe, and in Excel spreadsheets it usually means the text format of numbers. This symbol often appears in inappropriate places, this problem also happens with other characters or letters. Let’s find out how to clear the table of interfering useless characters.

How to remove apostrophe visible in a cell

An apostrophe is a specific punctuation mark, it is written only in specific cases. For example, it may appear in proper names or in numerical values. However, sometimes Excel users write apostrophes in the wrong places. If there are too many extra characters in the table, you can replace them with others. Let’s figure out how to do it in a few quick steps using the program’s tools.

1. Select the cells where the wrong characters are located. On the “Home” tab, find the “Find and select” button and click on it.

2. Select the “Replace” item in the menu that opens or press the hot keys “Ctrl + H”.

3. A dialog box will open with two fields. In the line under the heading “Find” you need to enter a symbol that is written incorrectly – in this case, an apostrophe. We write in the line “Replace with” a new character. If you only want to remove the apostrophe, leave the second line blank. For example, let’s substitute a comma in the “Replace with” column and click the “Replace All” button.

4. Now in the table instead of apostrophes there are commas.

You can replace apostrophes not only on one sheet, but throughout the book. Click the “Options” button in the replacement dialog box – new options will appear. To insert one character instead of another on all sheets of the document, select the “In the book” option in the “Search” item and click “Replace All”.

How to remove an invisible apostrophe before a string

Sometimes when copying values ​​from other programs, an apostrophe appears before the number in the formula bar. This character is not in the cell. The apostrophe indicates the text format of the contents of the cell – the number is formatted as text, and this interferes with the calculations. Such characters cannot be removed by changing the format, tools Excel or functions. You must use the Visual Basic Editor.

1. Opening the Visual Basic for Applications window using the Alt+F key combination
2. The editor is only available in English. We find on the top menu bar Insert (Insert) and click on the item Module (Module).

3. Write a macro to remove the apostrophe.

Attention! If it is not possible to create a macro yourself, use this text.

4. Select the range of cells where the extra character appears, and press the key combination “Alt + F8”. After that, the apostrophes will disappear and the numbers will take the correct format.

Removing extra spaces from a table

Extra spaces are put in Excel tables to divide large numbers into parts or by mistake. If you know that there are too many spaces in the document that should not be, use the Function Wizard.

1. Select a free cell and open the Function Manager window. The list of formulas can be accessed by clicking on the “F(x)” icon next to the formula bar or via the “Formulas” tab on the toolbar.

2. Open the “Text” category, it is listed in the dialog box or on the “Formulas” tab as a separate section. You must select the TRIM function. The image shows two ways.

3. Only one cell can become a function argument. We click on the desired cell, its designation will fall into the argument line. Next, click “OK”.

4. We fill in several lines, if necessary. Click on the top cell where the formula is located and hold down the black square marker in the lower right corner. Select all cells where you want values ​​or text without spaces and release the mouse button.

Important! It is impossible to clear the whole sheet of extra spaces, you will have to use the formula in different columns each time. The operation takes a little time, so there will be no difficulties.

How to remove invisible special characters

If a special character in the text is not readable by the program, it must be removed. The TRIM function does not work in such cases, because such a space between characters is not a space, although they are very similar. There are two ways to clear a document from unreadable characters. The first method to remove unfamiliar Excel characters is to use the “Replace” option.

1. Open the replacement window through the “Find and select” button on the main tab. An alternative tool that opens this dialog box is the keyboard shortcut “Ctrl+H”.
2. Copy the unreadable characters (the empty space they occupy) and paste them into the first line. The second field is left blank.
3. Press the “Replace All” button – the characters will disappear from the sheet or from the entire book. You can adjust the range in the “Parameters”, this step was discussed earlier.

In the second method, we again use the features of the Function Wizard. For example, let’s insert an entry with a line break into one of the cells.

1. The “Text” category contains the PRINT function, it reacts to any non-printable characters. You need to select it from the list.

2. We fill in the only field in the dialog box – there should appear a cell designation where there is an extra character. Click the “OK” button.

Some characters cannot be removed using the function, in such situations it is worth turning to replacement.

• If you need to put something else instead of unreadable characters, use the SUBSTITUTE function. This method is also useful in cases where mistakes are made in the words. The function belongs to the “Text” category.
• For the formula to work correctly, you need to fill in three arguments. The first field contains a cell with text in which characters are replaced. The second line is reserved for the replaced character, in the third line we write a new character or letter. Many words repeat letters, so three arguments are not enough.

• The occurrence number is a number indicating which character of several identical ones should be replaced. The example shows that the second letter “a” was replaced, although it is in the word correctly. Let’s write the number 1 in the “Occurrence number” field, and the result will change. Now you can click OK.

Conclusion

The article considered all the ways to remove the apostrophe. Following simple instructions, each user will be able to cope with the task without any problems.