2

Image

I'm using Microsoft Excel 2021

I wish to be able to drag the cells in a column down in increments of 22 for example I typed in this in a column

L25
L47

and ideally, when I drag this it turns into

L25
L47
L69
L91

However, what I get after highlighting the two cells and dragging is this

L25
L47
L27
L49
L29

Please help in what I can do as I do not wish to go through every cell.

4
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Dec 4, 2023 at 7:32
  • I have L25 and L47 in two cells. =L25 on top and =L47 at the bottom. When I drag them downwards after highlighting the two values, I get in order, L25 L47 L27 L49 L29. That is not what I want. What I want is if I drag the two, I get in order, L25 L47 L69 L91 L113. Basically, I do not wish to go through every cell, remove the values after L, and type in the value I want. That is too tedious Dec 4, 2023 at 7:39
  • We need to see your spreadsheet.
    – harrymc
    Dec 4, 2023 at 8:30
  • I have L25 and L47 in two cells. =L25 on top and =L47 at the bottom. Please be more precise - L25 is not the same thing as =L25.
    – Paul
    Dec 4, 2023 at 16:22

2 Answers 2

7

There is a difference if you have formulas in your cells: =L25, =L47, etc. or text constants L25, L47, etc.
In the first case, when dragging, you will get the order: =L25, =L47, =L27, =L49, =L29, =L51
In the second case, the order will be: L25, L47, L69, L91, ...

3
  • Knowing this, one can use a workaround: 1. create the formula as a string using a quote sign at the beginning '=L25 and '=L47 2. drag to auto-fill the column 3. copy the whole column to the clipboard (Ctrl+C) 4. paste everything into a text-editor (like notepad) 5. Copy again from the text-editor (this removes the formatting as string) 6. paste into excel an the text will be formulas again.
    – Falco
    Dec 5, 2023 at 10:40
  • If you want we could add this to your answer as a solution to the immediate problem.
    – Falco
    Dec 5, 2023 at 10:41
  • 1
    Instead of using external text editor you can use internal Office clipboard (not standard Windows clipboard).
    – MGonet
    Dec 5, 2023 at 11:28
4

I tried the method you described and had no issue getting the sequence you need. I selected both cells, dragged down, and got L69, L91...

An alternative is to do it with a formula. Assuming you have "L25" in cell A1, writing the following formula in A2 and drag A2 down:

="L"&(RIGHT(A1, LEN(A1)-1)+22)

Another alternative is to write the following formula in any cell:

="L" & SEQUENCE(100, 1, 25, 22)

where:

  • 100 is the number of lines/iterations you need
  • 1 is the number of column
  • 25 is the starting number (i.e. L25)
  • 22 is the increment, i.e. the number added to the starting number at each iteration

Please note this will create an array formula (see the related MS documentation)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .