Hi,

I am looking for a way to concatenate within a dynamic range of cell references without the need for VBA. I’m not sure if this is beyond the realms of Excel formula?

The references will vary depending on the source data. Each cell listed is the start/end cell location address I wish to use. These points are subject to change based on data extracts.
The reference cells will contain individual text/numeric characters I wish to concatenate. The length of concatenate will also vary in length (number of cells.)

A1 is the first start point, B1 is the first end point... then C1-D1 the next indirect range, A2-B2, C2-D2, A3-B3 etc.
____A_____B_____C_____D
1] $B$10 $G$10 $P$10 $W$10
2] $B$12 $G$12 $P$12 $W$12
3] $B$14 $G$14 $P$14 $W$14
4] $B$16 $G$16 $P$16 $W$16
5] $B$18 $G$18 $P$18 $W$18

I understand concatenate range is not possible for below example, but are there alternate solutions?

=CONCATENATE(INDIRECT(A1):INDIRECT(B1)) – this is what I would like to do.

Which is really =CONCATENATE(B10,C10,D10,E10,F10,G10) or =B10&C10&D10&E10&F10&G10

=CONCATENATE(INDIRECT(C1):INDIRECT(D1))
=CONCATENATE(INDIRECT(A2):INDIRECT(B2)) etc.

Can you advise on how to limit the return to the start and end point?

There may 100’s of individual lists required of various length which rules out manual interaction.

Another example with different source data, could be as follows: (which rules out a set concatenate formula)

____A_____B_____C____D
1] $C$10 $H$10 $K$10 $V$10
2] $C$12 $H$12 $K$12 $V$12
3] $C$14 $H$14 $K$14 $V$14
4] $C$16 $H$16 $K$16 $V$16
5] $C$18 $H$18 $K$18 $V$18

Many thanks for any ideas!
hope this makes sense
Sam