CODEWITHSUNDEEP
excelexcel-formula
fred
fred

Reputation: 33

How to concat 2 cells in excel

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

Upvotes: 2

Views: 89

Answers (3)

VBasic2008
VBasic2008

Reputation: 54807

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,))

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

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

Upvotes: 1

Gedas Miksenas
Gedas Miksenas

Reputation: 1059

Try:

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

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

Upvotes: 0

Related Questions