How to add sign in Excel before a Number

In Excel, when you add the plus sign before a number, it disappears just like it was the case when you add leading zeros. This is because Excel assumes that the sign is redundant. However, it may be necessary at times to add the sign before numbers for example when showing changes, before a phone number. This article gives you simple steps you can use to add the plus sign before numbers so that they do not disappear.

Method 1: Use the Custom number Format

Create a custom format that covers the number you want to add a plus sign before. Remember the plus sign indicates a positive number. The custom format method does not change the value of the number you have, it only changes how the number is displayed in Excel. Use the following simple steps;

1. Highlight the numbers you want to add a  plus sign before

2. Right-Click

3. Open Format Cells in the Number Tab

4. Select Custom Choice

5. Go to the typing field and type in +0;-0; 0

6. Select OK

The positive numbers will thus have the plus sign added to them, leaving everything else as it was. This means that the other content value does not change. To do this to the rest of the cells, just copy and paste the same formatting method to the rest of the cells.

The result when you add sign in excel before nunbers using format cells method

Use the leading apostrophe

If you have a small set of data you can add sign in Excel before number using the leading apostrophe. The custom method can mostly be used when you have a large data set. In this method, simply add an apostrophe then put on the plus sign. Adding the apostrophe makes Excel assume that the numbers are in text format and thus the plus sign will not be deleted once you add it before the number.

The apostrophe will not be visible in the number only the plus sign will be visible.  

How to remove the plus sign in Excel

You can remove the plus sign one by one if you wish to; however, this method is tedious. The following are methods that can be used to remove the sign from cells without wasting time.

Use the =SUBSTITUTE() function

Use the following method

1. Choose the cells that have the plus sign you want to remove

2. Type the formula; =SUBSTITUTE(A1,”1”,””)+0

3. Click Enter

4. In case your data has a minus sign, you can also remove it following the formula =SUBSTITUTE(A1”-“”,””)+0

Note that the formulas can only work when you have numbers with plus or minus signs in your data.

Leave a Reply

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading