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

Sometimes we insert both the texts and numbers into the same Excel cell. For some reason, we may want to remove the texts from the cell keeping only the numbers. For this purpose, Excel offers multiple ways to remove text while keeping the numbers. In this article, you will learn 8 ways to remove texts from an Excel cell but leave the numbers there.

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

The easiest way to remove text from a cell leaving the numbers is to use the Find and Replace command.

Now follow the steps below to use the Find and Replace feature.

❶ First select the cells having texts and numbers merged.

❷ Then hit CTRL + H to avail the Find and Replace dialog box.

❸  Type the text that you want to remove within the Find what box.

❹ Leave the Replace with box blank.

❺ Now hit the Replace All button.

❻ Finally hit the Close button to exit the Find and Replace dialog box.

Thus you have deleted all the text from the Excel cells leaving the numbers only in their places.

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

2. Deleting Text from Excel Cell Without Numbers with SUBSTITUTE Function

You can use the SUBSTITUTE function instead of using the Find and Replace dialog box. Both do the same task.

For that,

❶ Click on cell C5.

❷ Now insert the following formula:

Here,

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

❸ After that hit the ENTER button.

❹ Now 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.

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

You can also use the TEXTJOIN, ROW, INDIRECT, LEN, & IFERROR Functions to make a formula. This formula will delete all the texts from an Excel cell but leave the numbers.

For this,

❶ Select cell C5 first.

❷ Then insert the following formula:

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.

❸ Now hit the ENTER button.

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

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

4. Removing Text from Cell but Leave Numbers Joining RIGHT and LEN Functions

Follow the steps below to combine the RIGHT and LEN functions to remove text from an Excel cell leaving the numbers.

❶ First of all, select cell C5.

❷ Then insert the following formula in cell C5.

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.

❸ After that hit the ENTER button.

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

Finally, you will have all the cells with numbers only as in the screenshot below:

Read More: How to Remove Text After Character in Excel

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

You can use the following array formula to remove text from an Excel cell leaving all the numbers. To use the array formula:

❶ First select cell, C5.

❷ Then insert the following array formula there:

❸ After that hit the ENTER button to execute the array formula.

❹ Place your mouse cursor at the right bottom corner of cell C5 and drag down the Fill Handle icon.

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

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

The Text to Columns command splits off the text from the numbers.

Now follow the steps below to learn the process to do so.

❶ Select all the cells containing texts with numbers.

❷ Then go to Data > Data Tools > Text to Columns.

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

❹ Again hit the Next button in the Convert Text to Columns Wizard dialog box.

❺ Then make sure the General option is selected and hit Finish.

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

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

Remove texts from an Excel cell using the Flash Fill feature.

❶ Insert only the numbers in an adjacent cell.

❷ Then go to Home > Editing > Fill > Flash Fill.

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

8. Deleting Text from Excel Cell but Leave Numbers with VBA Macro

We will create a user-defined function called DeleteTextsButNumbers with a VBA script to remove text from an Excel cell leaving the numbers.

For that,

❶ Press ALT + F11 to open the VBA editor.

❷ Go to Insert > Module.

❸ Then 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, I’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.

❺ Now come back to the datasheet and select cell C5.

❻ Insert the following formula there.

❼ Then hit ENTER.

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

After that, you will 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.

Conclusion

To sum up, we have discussed 8 methods to remove text from an Excel cell but leave the numbers. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.

Related Articles

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