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. 

Dataset wtih question marks

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.

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

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. 

Everything is removed

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.
Enter a tilde followed by the question mark in Find what

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.

All question marks are removed

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

Method #2: Remove Question Mark Using SUBSTITUTE Function

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

Dataset wtih question marks

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,"?","")
Enter Substitute formula
  1. Click the Enter button on the formula to enter the formula without moving the cell selector.
Enter the formula
  1. Double-click or drag down the fill handle to copy the formula down the column.
Copy it for all the cells
  1. Select the range B1:B7 containing the cleaned data and press Ctrl + C to copy it.
Copy the data
  1. 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.

Click on Paste Special
  1. In the Paste Special dialog box, select Values, and click OK.
Select Values in Paste special

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

Formula converted to values
  1. Delete column B so that we remain with only the cleaned data in column A.
Delete the extra column

Method #3: Remove Question Mark Using Excel VBA

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

Dataset wtih question marks

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

We proceed as follows:

  1. Select range A2:A7.
Select the data
  1. 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.
Click on 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.

Click on Customize the ribbon

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

Check the Developer option
  1. In the Visual Basic Editor, insert a new module using any of the following ways:
  • Open the Insert menu and click the Module option.
Insert a module
  • Right-click the ThisWorkbook object in the Project Explorer window and select Module on the Insert flyout menu.
  1. 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
  1. Save the procedure and save the workbook as a Macro-Enabled Workbook.
  2. Place the cursor anywhere in the procedure and press F5 to run the code.
  3. Press Alt + F11 to switch back to the active worksheet. We can also click the View Microsoft Excel button on the toolbar.
click the View Microsoft Excel button

The procedure replaced all the question marks with empty strings:

All question marks are replaced

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.
Click on record macro
  1. In the Record Macro dialog box, open the Store macro in drop-down and select Personal Macro Workbook and click OK.
select Personal Macro Workbook
  1. On the Developer tab, click Stop Recording.
Click on Stop Recording
  1. Save and close the workbook and any other open workbook. Click Save on the message box that appears.
Click Save on the message box

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.
Notice the PERSONAL.XLSB  in the Project Explorer Window
  1. Expand the PERSONAL.XLSB object and double-click Module 1 to open its Code window.
double-click Module 1
  1. 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
  1. 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. 

Dataset wtih 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.
Open the Name manager
  1. Click the New button.
  2. 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))
Enter the formula in Edit Name
  1. Click OK 
  2. Click Close to dismiss the Name Manager dialog box.
  3. Select cell B2 in our example dataset and type in the following formula:
=RemoveQuestionMarks(A2,"?")
Enter the formula in B2
  1. On the formula bar, click the Enter button. This enters the formula and leaves the cell selector as is. 
Enter the formula
  1. Double-click or drag down the fill handle to copy the formula down the column.
Enter the formula in all cells

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. 

Dataset wtih question marks

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)
Enter formula in cell B2
  1. Click the Enter button on the formula bar to enter the formula without moving the cell selector.
Enter the formula
  1. Double-click or drag down the fill handle to copy the formula down the column.
Enter formula in all cells

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.

Dataset wtih question marks

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)
Enter formula
  1. Click the Enter button on the formula to enter the formula and leave the cell selector as is.
Enter formula
  1. Double-click or drag down the fill handle to copy the formula down the column.
Enter formula in all cells

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: