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.

Method 2 – Deleting Text from Excel Cell with SUBSTITUTE Function

Steps:

• Click on cell C5.
• Insert the following formula:
`=SUBSTITUTE(B5,"KKV","")`

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:
`=TEXTJOIN("", TRUE,IFERROR(MID(B5, SEQUENCE(LEN(B5)), 1) *1, ""))`

In this formula:

• B5 refers to cells having texts and numbers.
• LEN(B5) returns the length of the contents of cell B5.
• SEQUENCE(LEN(B5)) returns the sequence of cell B5 which is {1;2;3;4;5;6;7}.
• MID(B5,SEQUENCE(LEN(B5)), 1) returns the position of the blank encountered from the left. The output is {“K”;”K”;”V”;” “;”5″;”0″;”6”}.
• IFERROR(MID(B6,SEQUENCE(LEN(B6)), 1) *1, “”) handles any errors within MID(B5,SEQUENCE(LEN(B5)), 1).
• TEXTJOIN(“”, TRUE,IFERROR(MID(B5,SEQUENCE(LEN(B5)), 1) *1, “”)) removes text by replacing the texts with blanks. Then it joins those blanks with the numbers.
• 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:
`=RIGHT(B5, LEN(B5)-3)`

In this formula,

• LEN(B5) calculates the length of contents in cell B5.
• LEN(B5)-3) removes 3 characters from the total length of the contents of cell B5.
• RIGHT(B5, LEN(B5)-3) removes 3 characters from the right side of the B5 cell contents. Thus we have just the numbers without any texts.
• 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:

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

Steps:

• Insert the following formula in C5:
`=SUM(MID(0&B5,LARGE(INDEX(ISNUMBER(--MID(B5, ROW(\$1:\$99),1))*ROW(\$1:\$99),),ROW(\$1:\$99))+1, 1)*10^ROW(\$1:\$99)/10)`
• 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.

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.

• Insert the following formula:
`=DeleteTextsButNumbers(B5)`
• 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:

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

1. Great did the job thank you

2. I’m looking for a formula to delete any random text and leave numbers. Your example rows all had the same length and text position so none of the solutions are of any use for my data.

Raiyan Zaman Adrey Jul 2, 2023 at 4:35 PM

Dear MARK GROENING,

Thank you for reading our articles. You wanted a formula to delete any random number and leave only text.

Using formula of method 3:
=TEXTJOIN(“”, TRUE,IFERROR(MID(B5, SEQUENCE(LEN(B5)), 1) *1, “”))

Using formula of method 5:
=SUM(MID(0&B5,LARGE(INDEX(ISNUMBER(–MID(B5,ROW(\$1:\$99),1))*ROW(\$1:\$99),),ROW(\$1:\$99))+1, 1)*10^ROW(\$1:\$99)/10)

Hope, this worked for your problem.

Regards,