# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Add 1 to all numbers in a column

## geo1378

Ok i need help please. lets say i have a column with 3000 different numbers, 1 number in each box in the column, and i want to subtract 1 from each number or add 1 to each number in the column. is this possible if so please help me.

----------


## MrShorty

Of course it's possible.  A couple of approaches:

A.  Place 1 in any empty cell.  copy that cell to the clipboard.  Select range to change.  Edit -> paste special -> check add/subtract -> OK.  Values in range change

B.  Place 1 in an empty cell (D1 for example).  In the column where you would like the new values output enter the formula =a1+$D$1 and copy down the column.  Now whatever value you put in D1 will be added to the values in column A.  if you want to subtract, just enter -1 into D1.

----------


## pcexpertq8

> Ok i need help please. lets say i have a column with 3000 different numbers, 1 number in each box in the column, and i want to subtract 1 from each number or add 1 to each number in the column. is this possible if so please help me.



Hello,
he meant or I need help same my brother above, and i will explain;

96512345678
96587654321
96523456789
96598765432

NOW how can I add "zero zero" or "+" before each number?
I have thousand of numbers in one column, one number in each box.!

I hope to find a solution.
many thanks,

----------


## fassi

try this
select the cells in which you want to add + before the number
right click-->format cells--->go to number tab--->go to custom
write
+General or +0
in type tab....ok
now it shows sign of + before every number

----------


## pcexpertq8

> try this
> select the cells in which you want to add + before the number
> right click-->format cells--->go to number tab--->go to custom
> write
> +general
> in type tab....ok
> now it shows sign of + before every number



wooow

you are perfect  :Smilie: 
i appreciate your help

----------


## mirshadm007

Hi,

This is very easy. you have to use a formula in excel to do that. here we go....

lets say you have a list of mobile numbers in colum B.

and you want to add +91 before each number. for this put 91 in colum A.(if you want to put 91 at the end that Put 91 in C colum.)

Now in the same row but colum C put this formula.. =CONCATENATE(A1,B1) and now ENTER. note A1 and B1 are the cell number that you want  to join.

your job is done.

----------


## Gary7558

I appreciate this is an old thread and in part it does answer my query, but not quite, so is anyone able to point my towards a solution for this issue.
All I want to do is add 1 to every number that appears in a column. I thought the solution was to use the paste special option and that has worked to a degree, all the numbers in the column have indeed had 1 added to them, BUT the numbers in my original column appear at randam places and in between them are blank cells ( eg.  in a column, there might be 3 blank cells between 2 numbers, followed by 5 blank cells between the next) Although the paste special option has added one to all the values, it has also inserted the value 1 in all the blank cells between the two numbers. I found the rather obvious "skip blanks" check box in the Paste Special check boxes but that hasn't solved the problem.
I seem to have got close to answering the problem myself, but frustratingly I've tripped at the final hurdle.
I'm saying to Excel, " look down this column, and wherever you see a number, add 1 to it... BUT DO NOT, add anything to cells that are empty. Any advice would be gratefully received.

----------


## Sam Capricci

Gary, it appears you've been a member long enough that you should know not to post a problem on someone else's post.  Start your own post and if you think this has relevant information then copy the link and refer to it.   :Wink:

----------

