How to Remove text from numbers in Excel

In Excel, you may need to remove text from numbers in certain situations, such as when you want to perform mathematical calculations on a column of data that includes both numbers and text. Excel treats text and numbers differently, and if you have numbers stored as text, it can lead to errors or unexpected results in calculations.

Case 1 – When alphanumeric data follow the same pattern

Here’s a practical scenario where we have a sales data table for a store that sells fruits. The data includes the number of fruits sold and their respective weights, but the weights are stored as text and include the unit “kg”. We need to remove the “kg” from the weights to convert them into numerical values for further analysis.

Method 1 – By using VALUE and SUBSTITUTE Functions

Step 1 – Selecting the cell

• Select any empty cell in which we want the result containing numbers only.
• In this cell, we will apply the VALUE and SUBSTITUTE Functions to get our desired results.

Step 2 – Writing the formula

• Copy and paste the following formula into the selected cell.

=VALUE(SUBSTITUTE(C2, ” kg”, “”))

• After writing the formula, press Enter and the result would appear in the cell.

Step 3 – Applying the formula to the whole range

• For applying the formula to the whole range, select the cell in which the result is present. For example, it is a D2 cell in our case.
• Then move your cursor to the right bottom corner of the cell, and your cursor would turn into a + shape which is called the fill handle.
• Double-Click on this fill handle and the formula would be applied to the whole range.

Method 2 – By using a function created with VBA code

Step 1 – Adding a module

• For adding a module, navigate to the Developer tab.
• After that, click on the first option named Visual Basic.
• You’ll see a new window on your screen and you can also open this window by using the shortcut key (Alt+F11) as well.
• Then, click on the Insert tab in this window and click on the Module option.
• Now, a new module would open.

Step 2 – Writing the code

• Just copy and paste the following code in the white area of the entered module.

Function Rmv_Text(textValue As String) As Double     Dim numericValue As Double     Dim numericPart As String         ‘ Extract numeric part from the text     numericPart = “”     For i = 1 To Len(textValue)         ‘ Check if the character is numeric or a decimal point         If IsNumeric(Mid(textValue, i, 1)) Or Mid(textValue, i, 1) = “.” Then             ‘ Append the numeric character to the numeric part             numericPart = numericPart & Mid(textValue, i, 1)         End If     Next i         ‘ Convert the numeric part to a number     numericValue = Val(numericPart)         Rmv_Text = numericValue End Function

• Then, use the shortcut key “Ctrl+Q” to close the VBA Editor.

Step 3 – Selecting the cell

• Select an empty cell in which you want to get the result with separated text from numbers such as cell D2.
• In this selected cell, we will apply the function created by us using VBA code to remove text from numbers.

Step 4 – Using the created Function

• Press the = (equal sign) button on your keyboard in the selected cell. For example, cell D2.
• Then, type “Rmv_Text” (without quotes) and select this function from the list by using the tab button. 
• Now, select or enter the name of the cell from which you want to remove the text. For instance, it is the cell C2.
• Add closing parenthesis and press Enter.
• The result would appear in cell D2 which contains numbers without text.

Step 5 – Applying the formula to the whole range

• For applying the formula on the whole range, select the cell in which the result is present. For example, it is a D2 cell in our case.
• Then move your cursor to the right bottom corner of the cell, and your cursor would turn into a + shape which is called the fill handle.
• Double-Click on this fill handle and the formula would be applied to the whole range.

Case 2 – When alphanumeric data is mixed

The dataset below can be used for various purposes, such as analyzing geographical distributions or conducting contact-based analysis. This dataset contains information about individuals including their names, addresses, and phone numbers. Each row represents a different person. The “Name” column lists the individual’s name. The “Address” column provides the full address of each person, including the street name, city, state, and zip code. The “Phone Number” column displays the contact phone number for each individual. The “Address in numbers” column should ideally contain the numerical representation of the address, with any text or non-numeric characters removed and in today’s tutorial, we will learn how to remove text from these numbers.

Method 1 – By Using a VBA code

Step 1 – Adding a module

• For adding a module, navigate to the Developer tab.
• After that, click on the first option named as Visual Basic.
• You’ll see a new window on your screen and you can also open this window by using the shortcut key (Alt+F11) as well.
• Then, click on the Insert tab in this window and click on the Module option.
• Now, a new module would open.

Step 2 – Writing the code

• Just copy and paste the following code in the white area of the entered module.
• You will have to modify the code according to your need.

Sub ExtractNumbersFromAddress()     Dim lastRow As Long     Dim addressRange As Range     Dim cell As Range         ‘ Set the range of the “Address” column     lastRow = Cells(Rows.Count, “B”).End(xlUp).Row     Set addressRange = Range(“B2:B” & lastRow)         ‘ Loop through each cell in the “Address” column     For Each cell In addressRange         ‘ Remove non-numeric characters and update the “Address in numbers” column         cell.Offset(0, 1).Value = ExtractNumbers(cell.Value)     Next cell End Sub Function ExtractNumbers(ByVal text As String) As String     Dim result As String     Dim i As Integer     Dim c As String         ‘ Loop through each character in the input text     For i = 1 To Len(text)         c = Mid(text, i, 1)                 ‘ Check if the character is a digit         If IsNumeric(c) Then             result = result & c         End If     Next i         ‘ Return the extracted numbers     ExtractNumbers = result End Function

• Then, use shortcut key “Ctrl+Q” to close the VBA Editor.

Step 3 – Running the macro 

• Go to the “Developer” tab on the Excel ribbon and click on the “Macros” button in the “Code” group.
• Select the Macro named “ExtractNumbersFromAddress”.
• Double-Click on it or click on Run.
• It will run the macro and all numbers from the Addresses and will be extracted and written in the “Address in numbers” column.
• In this way, we can remove text from numbers.

Explanation of the formula used in Case 1 Method 1:

We used the formula; =VALUE(SUBSTITUTE(C2, ” kg”, “”)): 

Here is the breakdown of the formula,

C2: This refers to cell C2 in the Excel worksheet. It represents the first cell in the “Weight” column, where the original weight value is stored (e.g., “0.5 kg” for Apple).

SUBSTITUTE(C2, ” kg”, “”): 

The SUBSTITUTE function is used to replace a specified text within a given text (C2) (” kg” in this case but you will write the text that you want to remove) with another text (an empty string “”). So, this part of the formula replaces the ” kg” text in the weight value with nothing, effectively removing it. For example, if C2 contains “2.5 kg”, this part of the formula would remove ” kg” and leave “2.5”.

VALUE(SUBSTITUTE(C2, ” kg”, “”)): 

The SUBSTITUTE function returns a text value. However, in order to perform calculations or use numerical functions on the weight values, we need to convert them into numbers. The VALUE function is used here to convert the resulting text (after removing ” kg”) into a number. For instance, if the result of the SUBSTITUTE function is “2.5”, the VALUE function would convert it to the numeric value 2.5.

Explanation of the code used in Case 1 Method 2:

The “Rmv_Text” function iterates through each character in the input text, identifies the numeric characters and decimal points, and joins them into a separate string. This string is then converted to a double value and returned as the result of the function.

Explanation of the code used in Case 2 Method 1:

The “ExtractNumbers” function is designed to extract all numeric digits from a given text string and return them as a new string.

The “ExtractNumbers” function iterates through each character in the input text, checks if each character is a numeric digit, and accumulates the numeric digits into a separate string. This string is then returned as the result of the function.