How to concat 2 cells in excel

user_13243197 0
Viewed 63

I have it in my cells A5:


A:E

and in my cell B6, I have it :

3

I try to say in other cells :

A3:E3

to select all row from A3 to E3

I tried to use Concat with excel but it put me error thanks for reading me

excel-formula excel
3 Answers

EDIT

Is this what you are trying to achieve, as BigBen Sir, rightly mentioned you need INDIRECT Function

=INDIRECT(LEFT(A5)&B6&":"&RIGHT(A5)&B6)

If you dont want to use INDIRECT Function as just show as A3:E3, then simply,

=LEFT(A5)&B6&":"&RIGHT(A5)&B6

IMAGE

user_81625200

An INDEX/INDIRECT Combination

  • This looks like the most 'elegant' solution to me:

    INDEX(INDIRECT($A$5),$B$6,) ' short for INDEX(INDIRECT($A$5),$B$6,0)

  • Credits should also go to P.b, who figured it out earlier in the comments.

  • It took me a while since I started with the wrong formula INDEX(INDIRECT($A$5),$B$6) ignoring the column argument.

  • Only when I realized that =COLUMNS(INDIRECT($A$5)) returned 5, I got back to INDEX and figured it out.

  • Here are a few examples of how you can utilize it:

    =COUNT(INDEX(INDIRECT($A$5),$B$6,))
=SUM(INDEX(INDIRECT($A$5),$B$6,))
=AVERAGE(INDEX(INDIRECT($A$5),$B$6,))
user_98140690

Try:

=LEFT(A1;FIND(":";A1)-1)&A2&RIGHT(A1;FIND(":";A1))&A2

A2 contains "3", A1 contains "A:E"

user_91980740
Related