How to Remove Text from an Excel Cell but Leave Numbers (8 Ways)

Method 1 – Using Find and Replace to Remove Text from Excel Cell but Leave Numbers

Steps:

• Create a helper column.
• Copy the values from the first column to the helper.
• Select the helper columnn, now with cells that have texts and numbers merged.

• Hit CTRL + H to open the Find and Replace dialog box.
• Type the text that you want to remove within the Find what box. Make sure to add the space as well.
• Leave the Replace with box blank.
• Hit the Replace All button.
• Select the Close button to exit the Find and Replace dialog box.

This deletes all the text from the Excel cells leaving the numbers only.

Read More: How to Remove Specific Text from Cell in Excel

Method 2 – Deleting Text from Excel Cell with SUBSTITUTE Function

Steps:

• Click on cell C5.
• Insert the following formula:

Here, B5 refers to the cells having texts and numbers, and “KKV” is the text to replace with blanks (“”).

• Hit Enter.

• Drag the Fill Handle icon from cell C5 to C12.

So you will see the SUBSTITUTE function has replaced all the texts with blanks. Thus, only the numbers are remaining.

Method 3 – Combining TEXTJOIN, ROW, INDIRECT, LEN, & IFERROR to Remove Text Only

Steps:

• Select cell C5.
• Insert the following formula:

• Press the Enter key.

• Drag the Fill Handle icon from cell C5 to C12.

Finally, you will have just numbers in the cells without any text.

Method 4 – Removing Text from Cell but Leaving Numbers by Joining RIGHT and LEN Functions

Steps:

• Select cell C5 and insert the following:

• Hit the Enter button.

• Drag the Fill Handle icon from cell C5 to C12.

• You will have all the cells with numbers only as in the screenshot below:

Read More: How to Remove Text After Character in Excel

Method 5 – Using Array Formula to Remove Text from Excel Cell but Leave Numbers

Steps:

• Insert the following formula in C5:

• Hit Enter to apply it.

• Drag down the Fill Handle icon from C5 to C13.

Now you will see that the array formula has deleted the texts from the Excel cells leaving only the numbers.

Read More: How to Remove Text between Two Characters in Excel

Method 6 – Removing Text from Excel Cell Excluding Numbers Using Text to Columns Tool

Steps:

• Select all the cells containing texts with numbers.
• Go to Data, Data Tools, and Text to Columns.

• Select Fixed Width from the Convert Text to Columns Wizard dialog box and hit the Next button.

• Hit the Next button again in the Convert Text to Columns Wizard dialog box.

• Select the General option and hit Finish.

Now you have successfully removed text from all the Excel cells leaving the numbers.

Method 7- Using Flash Fill to Remove Text from Excel Cell but Leave Numbers

Steps:

• Insert only the numbers in an adjacent cell.
• Select the cell range you want to fill, including the first cell.
• Go to Home and select Editing.
• Choose Fill and Flash Fill.

After hitting the Flash Fill command, you will get only the numbers in the cell without the texts.

Read More: How to Remove Letters from Cell in Excel

Method 8 – Deleting Text from Excel Cell but Leaving Numbers with VBA Macro

Steps:

• Press Alt + F11 to open the VBA editor.
• Go to Insert, then to Module.

• Copy the following VBA code:

Function DeleteTextsButNumbers(xTxt1 As String) As String

With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
DeleteTextsButNumbers = .Replace(xTxt1, "")
End With

End Function

• Paste and save the code in the VBA editor.

Here, we’ve created a function named DeleteTextButNumbers by using the VBA Replace function where it will take the cell value as a String to replace the texts with blanks and, as a result, will leave the numbers.

• Return to the datasheet and select cell C5.
• Insert the following formula:

• Hit Enter.

• Drag the Fill Handle icon from cell C5 to C12.

You should see that the function has deleted all the texts leaving the numbers as in the picture below:

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

Related Articles

• How to Remove Everything After a Character in Excel
• How to Remove Text before a Space with Excel Formula

<< Go Back To Excel Remove Text | Data Cleaning in Excel | Learn Excel