Forum Discussion

LonardTilford's avatar
LonardTilford
Copper Contributor
Apr 15, 2024

Creating a concat formula with incrementing cell references

Hello, 

 

I am looking to concat three cells together.  So naturally I use the formula =Concat(A2, B2, C2) however, I have to do this 5000 times.  So I would like to copy and paste the formula and the cell references increase every time they paste.  In other words, =Concat(A2, B2, C2) becomes =Concat(A3, B3, C3) and so on and so forth.  

 

Any help is appreciated.  This is my first time on the excel forums.  

 

Thanks

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 15, 2024

    LonardTilford 

    Option 1:

    Enter =CONCAT(A2:C2) in a cell, then fill down, or copy/paste to the target range.

    Excel will automatically adjust the reference.

     

    Option 2:

    Select (for example) D2:D5000. D2 should be the active cell in the selection.

    Enter the formula =CONCAT(A2:C2) then confirm it by pressing Ctrl+Enter. Excel will propagate the formula to the entire range, adjusting the reference.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 15, 2024

    LonardTilford 

    Option 1:

    Enter =CONCAT(A2:C2) in a cell, then fill down, or copy/paste to the target range.

    Excel will automatically adjust the reference.

     

    Option 2:

    Select (for example) D2:D5000. D2 should be the active cell in the selection.

    Enter the formula =CONCAT(A2:C2) then confirm it by pressing Ctrl+Enter. Excel will propagate the formula to the entire range, adjusting the reference.

Resources