# Increment number with letter in cell

I have a column of random numbers that all begin with 'B*****' where the stars are numbers. I need a formula to add 1 (+1) to the cell on the right of the number and take away 1 (-1) to the left of the number.

E.g. If B00001 is in the centre

B000002 should be on the right B000000 should be on the left of it

How do I achieve this? Tried to select a couple of cells and dragging them to the right and 'down' but it will just copy and not add!

• You are on right track. Just add B000000 and B000002 at left and right first. Dragging works only when there are at least two values selected. Select B000001 and B000002 and then drag to right to fill correct values. – Sandeep May 31 '18 at 9:28
• You Q in some places refers to 5 digits and other s 6. Please clarify what is required. – chris neilsen Jun 1 '18 at 3:08

If your B***** cell is at B1 cell,

``````Left: ="B"&TEXT(INT(RIGHT(B1,LEN(B1)-1))-1,"00000")
Right: ="B"&TEXT(INT(RIGHT(B1,LEN(B1)-1))+1,"00000")
``````

If you have 6 digits after "B", use "000000".

1. Put B in cell A1
2. Put 0 in A2
3. Type "=CONCATENATE(\$A1,TEXT(A2,"00000"))" in A3
4. Drag A2 and A3 together to the right