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.

Download Practice Workbook

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

6 Quick Methods to Remove Sign from Numbers in Excel

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 (8 Easy Methods)

2. Utilize Format Cells Option to Remove Sign from Numbers

Format cells option is a tremendous feature in excel. It enables us with formatting 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 the “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?

Read More: How to Remove Currency Symbol in Excel (6 Ways)

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

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-

• 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 (2 Easy Techniques)

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 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 to click the “Fill” icon and choose the “Flash fill” option.

• Check out the result below, we have got 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 function you can also delete signs from numbers. Follow the steps below-

Steps:

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

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 the 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-

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

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. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.

Related Articles

• How to Remove Percentage Symbol in Excel Without Changing Values
• How to Remove Symbol in Excel (8 Ways)
• How to Remove Percentage Symbol in Excel (7 Ways)