Apply formula to the entire column

Question

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

User · Answer

The exact formula is   ArrayFormula text A1 A  quot 00000 quot     ArrayFormula works on multiple rows  in the above example  every row   and results are placed in the cell with the formula and the cells below it in the same column  It looks as if the same formula was copied into all those rows  If any of the cells in that column are not empty  they won t get overwritten  Instead  you will get an error message  To save yourself typing  you can use the trick from the answer above by pnuts  Type   text A1 A  quot 00000 quot   and then hit the following key combination   On windows  Ctrl Shift Enter  On a MAC  Command Shift Enter   This will convert the formula to ArrayFormula  After hitting the key combination  you need to hit Enter  to actually apply the converted formula  If your sheet contains header row s   and you want to apply formula from  for example  row 5 on  you would use  text A5 A  quot 00000 quot   instead  This answer includes information from pnuts s answer and LOAS s comment

User · Answer

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

User · Answer

For Mac  Click on the first cell having the formula and press Ctrl   Shift   down arrow  This will select the last cell in the column used on the worksheet  command   D   don t use ctrl   This will fill the formula in the remaining cells

User · Answer

This worked for me   Write the formula in the first cell  Click Enter  Click on the first cell and press Ctrl   Shift   down arrow  This will select the last cell in the column used on the worksheet  Ctrl   D  This will fill copy the formula in the remaining cells

User · Answer

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

User · Answer

It looks like some of the other answers have become outdated  but for me this worked    Click on the cell with the text formula to copy Shift Click on the last cell to copy to Ctrl   Enter    Note that this replaces text if the destination cells aren t empty

User · Answer

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

User · Answer

Just so I don t lose my answer that works   Select the cell to copy Select the final cell in the column Press CTRL D

User · Answer

This is for those who want to overwrite the column cells quickly  without cutting and copying   This is the same as double-clicking the cell box but unlike double-clicking  it still works after the first try    Select the column cell you would like to copy downwards Press Ctrl Shift  dArr  to select the cells below Press Ctrl Enter to copy the contents of the first cell into the cells below   BONUS   The shortcut for going to the bottom-most content  to double-check the copy  is Ctrl  dArr   To go back up you can use Ctrl  uArr  but if your top rows are frozen you ll also have to press Enter a few times

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Found another solution    Apply the formula to the first 3 or 4 cells of the column 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

[google-sheets] Apply formula to the entire column

Examples related to google-sheets

Examples related to array-formulas

Examples related to google-sheets-formula