Excel Formula to Remove Text and Keep Numbers in Cells

Sharing is caring!

Excel is a powerful spreadsheet tool widely used for data analysis and management. One common task users often encounter is removing text from cells while keeping the numbers intact. This article will guide you through various methods to achieve this using formulas in Excel.

Understanding the Problem

When working with Excel, you may come across cells that contain both text and numbers. In many cases, you only need the numeric data for further analysis or calculations. To remove the text and keep the numbers, you can use built-in Excel formulas such as VALUE, TEXT, LEFT, RIGHT, MID, and SUBSTITUTE. These formulas allow you to extract the desired information efficiently.

Before we dive into the solutions, let’s understand the problem at hand. Consider the following examples:

In each case, we want to remove the text portion and keep only the numeric value. The text can appear before, after, or on both sides of the number. Additionally, the text may vary in length and content.

Using the VALUE Formula to Remove Text and Keep Numbers

The VALUE formula is a straightforward way to extract numbers from a cell containing both text and numbers. Here’s how to use it:

1. Select the cell where you want to display the extracted number.
2. Type =VALUE(cell_reference), replacing cell_reference with the address of the cell containing the mixed data.
3. Press Enter to display the result.

The VALUE formula will return the numeric value from the specified cell, ignoring any non-numeric characters. For example, if cell A2 contains “ABC123”, the formula =VALUE(A2) will return 123.

However, the VALUE formula has some limitations. It only works when the number appears at the beginning of the cell contents. If the number is preceded by text, the VALUE formula will return a #VALUE! error.

Combining LEFT, RIGHT, and MID Functions

If the numbers in your cell are consistently located at the beginning, end, or middle of the string, you can use a combination of the LEFT, RIGHT, and MID functions to extract them. Here’s an example:

In the formulas above:

• The RIGHT function extracts the last 3 characters from the cell.
• The LEFT function extracts the first 3 characters from the cell.
• The MID function starts from the 4th character and extracts the next 3 characters.

Adjust the number of characters to extract based on your specific data.

These functions are useful when the number consistently appears at a specific position within the cell contents. However, they may not be suitable if the position of the number varies or if there are multiple numbers in a single cell.

Using the SUBSTITUTE Function to Remove Specific Text from a Cell

The SUBSTITUTE function allows you to remove specific text from a cell. It’s particularly useful when the text you want to remove is consistent across multiple cells. Here’s how to use it:

1. Select the cell where you want to display the extracted number.
2. Type =SUBSTITUTE(cell_reference, "text_to_remove", ""), replacing cell_reference with the address of the cell containing the mixed data and text_to_remove with the specific text you want to eliminate.
3. Press Enter to display the result.

For example, if your cell contains “Price: 99.99 USD”, you can use the formula =SUBSTITUTE(A2, "Price: ", "") to remove the “Price: ” text and keep only the numeric value.

The SUBSTITUTE function is case-sensitive, so make sure the text you specify matches the case of the text in your cells. If the text appears multiple times within the cell contents, the SUBSTITUTE function will remove all occurrences.

Combining SUBSTITUTE and VALUE Functions

In some cases, your data may have text both before and after the number. To handle such scenarios, you can combine the SUBSTITUTE and VALUE functions. Here’s an example:

In the first formula, we use the SUBSTITUTE function to remove “USD” from the cell contents. The resulting string is then passed to the VALUE function, which extracts the numeric value.

In the second formula, we used the SUBSTITUTE function twice to remove both “Total: ” and ” EUR” from the cell, leaving only the numeric value. The VALUE function then extracts the number from the resulting string.

By combining SUBSTITUTE and VALUE, you can handle more complex scenarios where the text appears on both sides of the number or when there are multiple pieces of text to remove.

Handling Error Values

When using formulas to extract numbers, you might encounter error values such as #VALUE! if the cell doesn’t contain a valid number. To handle these errors gracefully, you can wrap your formula with the IFERROR function. Here’s an example:

=IFERROR(VALUE(SUBSTITUTE(A2, "Price: ", "")), "")

If the formula encounters an error, the IFERROR function will return an empty string instead of displaying the error value. This helps maintain a clean and error-free worksheet.

You can also use the IFERROR function to provide a default value or a custom error message. For example:

=IFERROR(VALUE(SUBSTITUTE(A2, "Price: ", "")), "No valid number found")

This formula will display “No valid number found” if the cell doesn’t contain a valid number after removing the text.

Alternative Methods to Remove Text and Keep Numbers in Excel

While formulas are the most common way to remove text and keep numbers in Excel, there are a few alternative methods worth mentioning:

1. Text to Columns: If your data consistently follows a specific pattern, such as numbers always appearing after a specific delimiter, you can use the Text to Columns feature. This feature allows you to split the cell contents into separate columns based on a delimiter. You can then extract the numeric data from the relevant column.
2. Flash Fill: If you have a pattern in your data that Excel can recognize, you can use the Flash Fill feature to automatically extract the numbers. Start by manually entering the desired output for a few cells, and Excel will attempt to fill the remaining cells based on the pattern it detects.
3. VBA Macros: For more complex scenarios or when dealing with large datasets, you can create custom VBA macros to remove text and keep numbers. Macros allow you to automate the process and apply the desired transformations to multiple cells or worksheets efficiently.

Final Thoughts

Removing text from Excel cells while keeping the numbers is a common task that can be achieved using various formulas. The VALUE function is a straightforward way to extract numbers, while the LEFT, RIGHT, and MID functions are useful when the numbers are consistently located at specific positions within the cell. The SUBSTITUTE function allows you to remove specific text, and combining it with the VALUE function enables you to handle more complex scenarios. Remember to use the IFERROR function to gracefully handle potential error values.

In addition to formulas, you can explore alternative methods such as Text to Columns, Flash Fill, and VBA macros, depending on your specific requirements and the complexity of your data.

FAQs

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.