Apply formula to the entire column

问题:

I'm trying to recode all the zip code from Column A into Column B with the formula:

=TEXT(A1,"00000")

Like this:

I want every cell in Column B to be transformed using the formula above. But I have over 40,000 rows, so it is impossible to drag down the formula and apply it to the entire Column B.

Is there a formula I could use to solve this problem?

回答1:

I think it's a more recent feature, but it works for me:

Double clicking the square on the bottom right of the highlighted cell copies the formula of the highlighted cell.

Hope it helps.

回答2:

I think you are in luck. Please try entering in B1:

=text(A1:A,"00000")

(very similar!) but before hitting Enter hit Ctrl+Shift+Enter.

回答3:

It looks like some of the other answers have become outdated, but for me this worked:

1. Click on the cell with the text/formula to copy
2. Shift+Click on the last cell to copy to
3. Ctrl + Enter

(Note that this replaces text if the destination cells aren't empty)

回答4:

Let's say you want to substitute something in an array of string and you don't want to perform the copy-paste on your entire sheet.

Let's take this as an example:

• String array in column "A": {apple, banana, orange, ..., avocado}
• You want to substitute the char of "a" to "x" to have: {xpple, bxnxnx, orxnge, ..., xvocado}

To apply this formula on the entire column (array) in a clean an elegant way, you can do:

=ARRAYFORMULA(SUBSTITUE(A:A, "a", "x"))

It works for 2D-arrays as well, let's say:

=ARRAYFORMULA(SUBSTITUE(A2:D83, "a", "x"))

回答5:

Found another solution:

• Apply the formula to the first 3 or 4 columns
• Ctrl + C the formula in one of the last rows (if you copy the first line it won't work)
• Click on the column header to select the whole column
• Press Ctrl + V to paste it in all cells bellow

回答6:

To be clear when you us the drag indicator it will only copy the cell values down the column whilst there is a value in the adjacent cell in a given row. As soon as the drag operation sees an adjacent cell that is blank it will stop copying the formula down.

.e.g

1,a,b
2,a
3,
4,a

If the above is a spreadsheet then using the double click drag indicator on the 'b' cell will fill row 2 but not row three or four.

回答7:

You can use Ctrl+Shift+Down+D to add the formula to every cell in the column as well.

Simply click/highlight the cell with the equation/formula you want to copy and then hold down Ctrl+Shift+Down+D and your formula will be added to each cell.

回答8:

You may fill the column by double-clicking on the bottom right hand corner of the cell which you want to copy from (the point on the box that you would otherwise drag) and it will be applied to whole column.

NB: This doesn't work if you have the filter applied, nor if there is already something already in the cells below.