How to Remove Question Marks from Text in Excel?

If you import data into Excel from other sources, the data may come in with unwanted characters such as question marks.

You will need to clean the data by removing the unwanted characters. 

Removing these extra characters and question marks by deleting them one by one is not a viable option. Especially when you are dealing with a large dataset.

In this tutorial, I will show you five techniques for removing question marks from text values in Excel.

Method #1: Remove Question Mark from Text Using Find and Replace Feature

Suppose we have the following dataset that has unwanted question mark characters that we want to remove. 

We want to clean the data by removing the question marks using Excel’s Find and Replace feature.

A question mark is also a wildcard character

It is important to note that we cannot remove a question mark character from text in Excel using the Find and Replace feature in the usual way.

This is because a question mark is a wildcard character. It already means something other than the text value. 

Let’s attempt to remove the question marks from our dataset using the Find and Replace feature in the usual way.

• Open the Find and Replace dialog box using any of the following ways:

Press Ctrl + H.

Or

Click the Home tab and then click on the Find & Select option, and then select the Replace option from the drop-down.

• Type a question mark (?) in the Find what box, leave the Replace with box blank, and click the Replace All button.

Something we did not intend to happen occurs. Our dataset is wiped out and Excel returns a message box indicating that 124 replacements have been made. 

This unintended result occurred because the question mark is a wildcard character. It represents each character in the dataset.

The Find and Replace Feature understood the command to “Find each character in the dataset and replace it with  an empty string.”

Click OK on the message box and Close on the Find and Replace dialog box. Press Ctrl + Z to undo the changes and restore the dataset to its original state.

The Right Way to Remove Question Marks Using Find and Replace

To work around a question mark being a wildcard character and have it removed from the dataset, we have to precede it with a tilde (~).

We use the following steps:

1. Press Ctrl + H to open the Find and Replace dialog box. 
2. Type a tilde followed by a question mark (~?) in the Find what box, leave the Replace with box blank, and click Replace All.

All question marks in our dataset are removed and replaced with empty strings and Excel returns a message box indicating that 23 replacements have been made.

We got the intended result this time round because when you add a tilde (~) before the question mark in the Find what box in the Find and Replace dialog box, Excel treats the question mark as any other regular text character instead of a wildcard character. This allows Excel to find all the question marks and remove them while leaving the other characters unchanged

Also read: How To Add Text To The End Of A Cell In Excel

Method #2: Remove Question Mark Using SUBSTITUTE Function

Suppose we have the following dataset that has unwanted question mark characters. 

We want to clean the data by removing the question marks using Excel’s built-in SUBSTITUTE function. The SUBSTITUTE function substitutes a new text string for an existing text string. 

We use the following steps:

1. Select cell B2 and type in the following formula:

=SUBSTITUTE(A2,"?","")

2. Click the Enter button on the formula to enter the formula without moving the cell selector.

3. Double-click or drag down the fill handle to copy the formula down the column.

4. Select the range B1:B7 containing the cleaned data and press Ctrl + C to copy it.

5. Select cell A1 and open the Paste Special dialog box using any of the following ways:

Press Ctrl + Alt + V

Or

Click the Home tab, open the Paste drop-down list in the Clipboard group, and select the  Paste Special option.

6. In the Paste Special dialog box, select Values, and click OK.

The values returned by the formulas in range B2:B7 are pasted into range A2:A7.

7. Delete column B so that we remain with only the cleaned data in column A.

Also read: How To Remove Formulas In Excel

Method #3: Remove Question Mark Using Excel VBA

Suppose we have the following dataset that has unwanted question mark characters. 

We want to clean the data by removing the question marks using Excel VBA.

We proceed as follows:

1. Select range A2:A7.

2. Open the Visual Basic Editor in any of the following ways:

• Press Alt + F11.
• In the Code group on the Developer tab, select Visual Basic.

Note: If you do not see the Developer tab, activate it by right-clicking any of the tabs and selecting Customize Ribbon on the shortcut menu.

Then check the Developer option in the Excel Options dialog box that appears, and click OK.

3. In the Visual Basic Editor, insert a new module using any of the following ways:

• Open the Insert menu and click the Module option.

• Right-click the ThisWorkbook object in the Project Explorer window and select Module on the Insert flyout menu.

4. Type the following procedure in the module:

'This VBA Macro code to remove question marks from text is written by Steve Scott from https://spreadsheetplanet.com

Sub RemoveQuestionMark()
Selection.Replace What:="~?", Replacement:=""
End Sub

5. Save the procedure and save the workbook as a Macro-Enabled Workbook.
6. Place the cursor anywhere in the procedure and press F5 to run the code.
7. Press Alt + F11 to switch back to the active worksheet. We can also click the View Microsoft Excel button on the toolbar.

The procedure replaced all the question marks with empty strings:

How to add the code to the Personal Macro workbook

In case you want to reuse this code in any Excel file, you can save the code in the Personal Macro Workbook so that it becomes available in all workbooks on your computer.

The Personal Macro Workbook is a hidden workbook into which you can add code that you want to use in any workbook on your computer. 

Before we can use the Personal Macro Workbook, we have to create it by doing the following:

1. Click the Developer tab and select Record Macro in the Code group.

2. In the Record Macro dialog box, open the Store macro in drop-down and select Personal Macro Workbook and click OK.

3. On the Developer tab, click Stop Recording.

4. Save and close the workbook and any other open workbook. Click Save on the message box that appears.

The Personal Macro Workbook is created and opens in the background whenever you launch Excel.

Add  Code in the Personal Macro Workbook

1. Re-open the workbook you were working on.
2. Press Alt + F11 to open the Visual Basic Editor. Notice the PERSONAL.XLSB  in the Project Explorer Window.

3. Expand the PERSONAL.XLSB object and double-click Module 1 to open its Code window.

4. Delete what is in the code window and type in the following procedure:

'This VBA Macro code to remove question marks from text is written by Steve Scott from https://spreadsheetplanet.com

Sub RemoveQuestionMark()
Selection.Replace What:="~?", Replacement:=""
End Sub

5. Save the procedure.

The procedure is now available in all workbooks on your computer.

Method #4: Remove Question Mark Using Custom LAMBDA Function

Suppose we have the following dataset that has unwanted question marks. 

We want to remove the question marks using a custom LAMBDA function. The LAMBDA function is a special function available only in Excel 365. It enables one to create custom functions without coding. 

We use the following steps:

1. Press Ctrl + F3 to open the Name Manager dialog box.

2. Click the New button.
3. In the New Name dialog box that appears, type RemoveQuestionMark in the Name box, set the scope to  workbook, and replace what is in the Refers to box with the following formula:

=LAMBDA(string, chars, IF(chars<>"", RemoveQuestionMarks(SUBSTITUTE(string, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), string))

4. Click OK 
5. Click Close to dismiss the Name Manager dialog box.
6. Select cell B2 in our example dataset and type in the following formula:

=RemoveQuestionMarks(A2,"?")

7. On the formula bar, click the Enter button. This enters the formula and leaves the cell selector as is. 

8. Double-click or drag down the fill handle to copy the formula down the column.

All the question marks are removed and replaced with empty strings. 

Method #5: Remove Question Mark Using From Beginning or End of Text

Sometimes we may want to remove question marks from the beginning or end of the text. We can use formulas that use the RIGHT, LEFT, and LEN functions.

The RIGHT function returns a specified number of characters from the end of a text string.

The LEFT function returns a specified number of characters from the beginning of a text string.

The LEN function returns the total number of characters in a text string.

Remove Question Marks from the Beginning of the Text

Suppose we have the following dataset that has question marks at the beginning of the text. 

We want to remove the question marks using a formula that combines the RIGHT and LEN functions.

We use the steps below:

1. Select cell B2 and type in the following formula:

=RIGHT(A2,LEN(A2)-2)

2. Click the Enter button on the formula bar to enter the formula without moving the cell selector.

3. Double-click or drag down the fill handle to copy the formula down the column.

Remove Question Marks from the End of the Text

Suppose we have the following dataset that has question marks at the end of the text.

We want to remove the question marks using a formula that combines the LEFT and LEN functions.

We proceed as follows:

1. Select cell B2 and type in the following formula:

=LEFT(A2,LEN(A2)-3)

2. Click the Enter button on the formula to enter the formula and leave the cell selector as is.

3. Double-click or drag down the fill handle to copy the formula down the column.

In this tutorial, we have looked at 5 techniques for cleaning data by removing question marks from the text in Excel.

Other articles you may also like:

• Show Negative Numbers in Parentheses/Brackets in Excel (2 Easy Methods)
• How to Remove Apostrophe in Excel (3 Easy Ways)
• How to Remove Commas in Excel (from Numbers or Text String)
• How to Remove Dashes (-) in Excel?
• How to Remove Dollar Sign in Excel
• How to Remove Text after a Specific Character in Excel
• How to Remove a Specific Character from a String in Excel