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?

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

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

`=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 (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-

`=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 **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-

`= 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
**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.