How to Remove Sign from Numbers in Excel (6 Suitable Methods)

When working with a dataset, you might find signs like #, @, $, and * which are not needed in front of a number or between numbers. You might need to remove those signs from the row or column otherwise while applying formulas or calculating numbers they won’t act as numbers. Fortunately, it’s possible to remove those signs from numbers in Excel. In this article, I am going to show you how to remove sign from numbers in Excel.

Remove Sign from Numbers in Excel: 6 Quick Methods

Following I have shared 6 quick methods to remove signs from numbers in Excel.

Suppose we have a dataset of some product’s total Sales Amount, Product Code, and Quantity Code. Now as you can see we have some unwanted signs in the Quantity Code column. We will delete those signs from the numbers.

1. Use Find & Replace Feature to Remove Sign from Numbers

Using the find and replace feature from the “Editing” option you can remove signs from numbers in the same column within seconds. Follow the below steps to do so-

Steps:

Press Ctrl+H to open the “Find and Replace” window.

In the “Find and Replace” window put the hash mark(#) mark in the “Find what” section as we are removing the hash sign(#) from the column.
Click “Replace All”.

A confirmation window will appear confirming the replacement of the hash mark from the worksheet.

Thus we can remove signs from all the numbers in the dataset.

Note: If you have different types of signs in different cells you can repeat the process by putting the desired sign in the “Find what” row you want to remove.

Read More: How to Remove Pound Sign in Excel

2. Utilize Format Cells Option to Remove Sign from Numbers

Format cells option is a tremendous feature in Excel. It enables us to format cells just the way we want to format. In this section, I am describing how you can remove signs from numbers by utilizing the format cells option in Excel.

Suppose in a workbook we have some dash sign (–) in front of numbers in a column. Using Format cells we will remove those characters from the numbers.

Steps:

Select cells (E5:E14) and click the right button of the mouse to get options.

From the options choose “Format cells”.

A new window will appear.
From the new window, choose “Number” and click your desired format.
Press OK to continue.

Here in the workbook, you will see the selected cells have no more dash signs (–). Simple isn’t it?

Limitations: The format cells feature is limited to removing some signs like dash sign (-) and dollar sign ($). In order to remove other signs, you can use formulas for a better experience.

3. Use SUBSTITUTE Function to Remove Sign from Numbers

With the use of a formula, you can also delete signs from numbers. In some cases, you might find signs between the numbers or at the end of the numbers. You don’t have to remove those signs manually anymore. Follow my instructions below-

3.1 Remove Single Sign

Using the SUBSTITUTE function in Excel you can remove signs from the beginning of a number.

Steps:

Choose a cell. Here I have selected cell (G5).
Apply the formula in the cell-

=SUBSTITUTE(E5, "#", "")

Where,

The SUBSTITUTE function will find a sign or string and replace it with another string.

Hit the Enter button to get the result in the cell.
Drag down the Fill Handle to fill the cells.

This way you will get all the signs removed from the numbers in a different column.

3.2 Remove Multiple signs

Sometimes you will get to see some signs between the numbers or multiple signs within a number. Using the SUBSTITUTE function you can remove multiple signs located in different places from a string.

In the following screenshot, you will see we have different signs at the beginning and at the middle of numbers in the Quantity Code column. Now we will delete those signs from the numbers.

Steps:

Choose a cell (G5) to enter the formula.
Put the formula in the cell-

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E5, "#",""), "/", ""), "\", "")

Press Enter.
Drag the Fill Handle down to get the result.

You will find we have successfully removed all the signs from the numbers.

Read More: How to Remove Plus Sign in Excel

4. Flash Fill Feature to Remove Sign from Numbers

In order to remove signs from numbers, you can use the flash fill feature. You can call this process an automatic one because it’s that easy. Don’t forget to check the Things to Remember section at the end of this article as it does have some limitations.

Steps:

Type the numbers without signs in a new column just by the side of the “Quantity Code” column.
Type at least two numbers from the list to enable the fill handle.

Selecting both cells drag down to fill the column.
You won’t get the proper output in the column.
To get the precious output click the “Fill” icon and choose the “Flash Fill” option.

Check out the result below, we have a new list of numbers without signs.

5. Combine SUBSTITUTE, CHAR, CODE, and LEFT Functions to Remove Sign from Numbers

With the combination of the SUBSTITUTE, CHAR, CODE, and LEFT functions you can also delete signs from numbers. Follow the steps below-

Steps:

Select a cell (G5) to apply the formula.
Write the formula down-

=SUBSTITUTE(E5,CHAR(CODE(LEFT(E5))),"")

Where,

The LEFT function will extract numbers from the selected cell (E5).
The CODE function will return a numeric code from the given string.
The CHAR function identifies characters based on the number.
The SUBSTITUTE function will find a sign or string and replace it with another string.

Click the Enter.
Pull the Fill Handle down to collect output from the column.

Thus we will get our dataset ready without any signs.

6. Merge RIGHT, LEN Functions to Remove Sign from Numbers in Excel

To remove the sign placed at the front of a number you can apply the combination of RIGHT and LEN functions.

Steps:

Select a cell to apply the formula. Here I have selected cell (G5) to enter the formula.
Put the formula down-

= RIGHT(E5,LEN(E5)-1)

Where,

The LEN function returns the length of a given string.
The RIGHT function extracts a given number of characters from the right side of a string. Here (E5)-1 ordered the string to remove one character from the right side.

Click the Enter button from the keyboard.
Drag down the Fill Handle to get the output.

So, we have our final dataset deleting signs from numbers in Excel.

Things to Remember

By using you can remove the minus sign (–) within a cell. Just apply the formula- ABS(). In the bracket portion put the cell number and enjoy the result as the ABS function returns the absolute value of a number.
You can use the VALUE function from Excel formulas to delete the dollar sign($) from numbers. But remember the dollar sign must be on the right side of the given numbers.
While using the Flash Fill feature to remove signs in a different column. You must choose the immediate side-by-side column or row to get the output. Otherwise, it won’t work.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Remove Sign from Numbers.xlsx

Conclusion

In this article, I have tried to cover the simple steps to remove sign from numbers in Excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. Stay tuned and keep learning.