Excel Formula to Remove Text and Keep Numbers in Cells

Did you know that Excel provides a powerful formula that allows you to effortlessly remove text and keep only the numbers in your cells? This handy feature can save you valuable time and effort when working with large datasets or analyzing numeric information. Whether you need to clean up data for financial analysis, statistical calculations, or any other purpose, mastering this formula will significantly enhance your productivity.

Key Takeaways:

• Excel has a native formula that can remove text and retain numbers in cells.
• The formula works in Excel 365, Excel 2021, and Excel 2019.
• By utilizing this formula, you can quickly clean up mixed text and number data.
• It allows you to separate text and numbers into two separate columns for better analysis and processing.
• You can also create custom functions or use special tools in Excel to achieve the same result.

How to Remove Text and Keep Numbers in Excel Cells

In order to remove text and keep numbers in Excel cells, you can use the TEXTJOIN formula. This formula works in Excel 365, Excel 2021, and Excel 2019.

To remove text and keep numbers, follow these steps:

1. Open Excel and navigate to the cell containing the mixed text and numbers.
2. In a new cell, enter the following formula:
   =TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1, ""))
3. Replace A2 with the cell reference of the mixed text and numbers.
4. Press Enter to apply the formula to the cell.

The TEXTJOIN formula will remove all text characters from the cell and keep only the numbers. You can then copy the formula down to as many cells as needed to remove text from multiple cells.

By using the TEXTJOIN formula in Excel, you can easily remove text and extract only the numbers from your data. This can be helpful when working with large datasets or performing calculations that require numeric values. Try it out and streamline your data analysis process!

How to Remove Numbers from Text String in Excel

If you need to remove numbers from a text string in Excel, you can use the TEXTJOIN formula with the ISERROR function. This formula works in Excel 365, Excel 2021, and Excel 2019.

To remove numbers from a cell, enter the formula =TEXTJOIN("", TRUE, IF(ISERROR(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), "")) in a new cell, replacing A2 with the cell containing the text string.

This formula will remove all numbers and keep only the text characters. Copy the formula down to remove numbers from multiple cells.

Example

Let’s say you have a cell with the text string “ABC123XYZ456”. If you apply the TEXTJOIN formula with the ISERROR function as described above, the result will be “ABCXYZ”.

Benefits

• Quickly remove numbers from text strings in Excel without the need for complex manual operations.
• Efficiently clean up data by eliminating numeric characters from text fields.
• Saves time and effort by automating the removal of numbers in bulk.

By utilizing the TEXTJOIN formula with the ISERROR function, you can easily remove numbers from text strings in Excel, ensuring clean and accurate data for your analysis and reporting needs.

Custom Functions to Remove Text and Numbers in Excel

If the native formulas are too complex or you are using an older version of Excel, you can create custom functions to remove text and numbers in Excel. These custom functions provide a more flexible and customizable approach to meet your specific data cleansing needs.

1. RemoveText Function

The RemoveText function utilizes VBA code to loop through each character in a string and remove any non-numeric characters. This function is useful when you want to eliminate text and keep only the numeric values in your cells.

Function Signature:

Function RemoveText(textString As String) As String
    Dim cleanString As String
    Dim character As Variant

    cleanString = ""

    For Each character In textString
        If IsNumeric(character) Then
            cleanString = cleanString & character
        End If
    Next character

    RemoveText = cleanString
End Function

2. RemoveNumbers Function

The RemoveNumbers function employs VBA code with regular expressions to remove any numeric characters from a string. This function comes in handy when you need to eliminate numbers and retain only the text in your cells.

Function Signature:

Function RemoveNumbers(textString As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\d"
    regex.Global = True

    RemoveNumbers = regex.Replace(textString, "")
End Function

These custom functions can be added to your Excel workbook and used just like any other formula. Simply enter =RemoveText(A2) or =RemoveNumbers(A2) in a cell to remove text or numbers from the specified cell.

With these custom functions, you can have more control over removing text and numbers in Excel, saving you time and effort in data cleansing tasks.

Using Special Tools to Remove Text and Numbers in Excel

Introduction

In addition to the formulas and custom functions mentioned earlier, Excel offers special tools that can efficiently remove text and numbers from cells, saving you valuable time and effort. One such tool is the DOSE Utility, which provides a range of options for targeted character removal. Let’s explore how this tool can streamline the process of removing specific types of text and numbers from your data.

The DOSE Utility

The DOSE Utility in Excel is a powerful tool that allows you to remove text and numbers from cells with just one click. It offers a variety of options to cater to your specific needs. Whether you want to remove only text, eliminate numbers with periods, or strip away all non-alpha characters, the DOSE Utility has got you covered.

Here are some key features of the DOSE Utility:

• Remove only text: This option allows you to remove all text from your cells, keeping only the numeric values.
• Remove numbers with periods: If your data contains numbers with decimal places, this option enables you to remove them, leaving only the whole numbers.
• Remove all text: This option completely eliminates all text from your cells, leaving behind only the numbers.
• Remove all text in capital letters: If you have uppercase text in your cells, this option removes it, preserving only the numeric values.
• Remove all text in small letters: Similarly, this option removes lowercase text, leaving only the numbers.
• Remove non-numeric characters: If you want to remove any non-numeric characters, such as symbols or punctuation marks, this option helps you achieve that.
• Remove non-alpha characters: This option removes any non-alphabet characters, such as symbols or numbers, leaving only alphabetic characters in your cells.
• Remove non-printing characters: If your data contains non-visible characters, such as line breaks or tabs, this option removes them, leaving only the visible text and numbers.
• Remove non-alphanumeric characters: If you need to remove any characters that are not alphabetic or numeric from your cells, this option can assist you.
• Remove custom characters: With this option, you can specify the exact characters you want to remove from your data, allowing for customized text and number removal.

Example: Using the DOSE Utility

Let’s walk through an example to see how the DOSE Utility can quickly remove text and numbers from Excel cells. Suppose you have a column of data with mixed text and numbers, and you want to remove the text while keeping only the numeric values.

Here is the original data:

By using the DOSE Utility and selecting the “Remove only text” option, you can effortlessly remove the text from the cells, resulting in the following data:

This simple and efficient process demonstrates the power of the DOSE Utility in removing unwanted text and numbers from your Excel data.

Splitting Text and Numbers into Separate Columns in Excel

If you have data in Excel that includes both text and numbers in a single column, you may need to split them into separate columns for analysis or further processing. Fortunately, Excel provides a convenient solution with the SplitTextNumbers custom function.

This custom function utilizes VBA code to iterate through each character in a cell and extract the text and numbers into different variables. The function can be customized to remove either the text or the numbers, depending on your specific requirements.

To use the SplitTextNumbers function, simply enter =SplitTextNumbers(A2, True) in a cell to extract the numbers from cell A2. Conversely, if you want to extract the text, enter =SplitTextNumbers(A2, False). This allows you to effortlessly split the data into two separate columns.

By splitting the text and numbers into separate columns, you gain greater flexibility in analyzing and manipulating your data. This can be particularly useful when dealing with large datasets or performing complex calculations that require distinct values for text and numbers.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.