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
  • Please reread the question. This doesn't answer it. – fixer1234 Jun 2 '18 at 9:13
  • Yes, it does answer the question. It will have B00000 in A1, B00001 in A2, B00002 in A3, B00003 in A4, ... – marijnr Jun 3 '18 at 15:36
  • That's not what the question asked. The question says you have B followed by 5 random numbers. The task is to produce a string on the left and right of it where the number portion is decremented and incremented by 1, respectively. You need to read the entire question, not just look at the example. The trivial example was chosen because it makes the desired action visible. – fixer1234 Jun 3 '18 at 19:03
  • But you can use other numbers to form the string. Just put them in row 2. From a given number, you can drag and calculate the new values. – marijnr Jun 4 '18 at 5:51
  • The problem is that the strings already exist, so the task is to accomplish the action from that starting point, not to create the pattern from scratch. – fixer1234 Jun 4 '18 at 5:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.