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

Get FREE Advanced Excel Exercises with Solutions!

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.

Use Find and Replace to Remove Text from an Excel Cell but Leave Numbers

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

Remove Text from an Excel Cell but Leave Numbers

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:

=SUBSTITUTE(B5,"KKV","")

Here,

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

❸ After that hit the ENTER button.

Delete Text from an Excel Cell but Leave Numbers with SUBSTITUTE Function

❹ 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:

=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.

❸ Now hit the ENTER button.

Combination of TEXTJOIN, ROW, INDIRECT, LEN, & IFERROR Functions to Remove Text but Leave Numbers

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

=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.

❸ After that hit the ENTER button.

Remove Text from an Excel Cell but Leave Numbers Using RIGHT and LEN Functions

❹ 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:

=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)

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

Use an Array Formula to Remove Text from an Excel Cell but Leave Numbers

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

Remove Text from an Excel Cell but Leave Numbers Using Text to Columns Feature

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

Convert Text to Column Wizard: Remove Text from an Excel Cell but Leave Numbers Using Text to Columns Feature

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

Remove Text from an Excel Cell but Leave 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.

Use Flash Fill to Remove Text from an Excel Cell but Leave Numbers

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.

Create new module to Delete Text from an Excel Cell but Leave Numbers with VBA Scripts

❸ 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.

Delete Text from an Excel Cell but Leave Numbers with VBA Scripts

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.

=DeleteTextsButNumbers(B5)

❼ 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Mrinmoy Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

4 Comments
  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.

    • Reply Avatar photo
      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.
      You can follow method 3 and method 5 of this article for your problem. The formulas mentioned here should work with your problem.

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

      Image showing formula to remove text and leave numeric values

      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)

      Image showing formula to remove text and leave numbers only

      Hope, this worked for your problem.

      Regards,
      Raiyan Zaman Adrey

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo