Create Formula from another Formula - Nesting Concatenate Concat

[mreinsmith]

I've often wanted to look up if it was possible to concatenate the results of a column of concatenate formulas (of course, CONCAT only starting in Excel 2016)

I thought maybe there would be a function like "FUNCTIONTEXT" but "FUNCTIONVALUE"

So I started looking and I can't seem to find anything relevant

It's possible I'm just missing the obvious (not seeing the forest for the trees so to speak)

So any help would be appreciated

In the meantime I'll stick with Paste --> Values

Just to be clear here is what I mean:

[table="width: 700, class: grid, align: left"]
[tr]
[td]Lastname[/td]
[td]Firstname[/td]
[td][/td]
[td]FORMULATEXT[/td]
[/tr]
[tr]
[td]Smith[/td]
[td]John[/td]
[td]John Smith[/td]
[td]=CONCATENATE(B2," ",A2)[/td]
[/tr]
[tr]
[td]Harrison[/td]
[td]George[/td]
[td]George Harrison[/td]
[td]=CONCATENATE(B3," ",A3)[/td]
[/tr]
[tr]
[td]Best[/td]
[td]Pete[/td]
[td]Pete Best[/td]
[td]=CONCATENATE(B4," ",A4)[/td]
[/tr]
[tr]
[td]Lee[/td]
[td]Geddy[/td]
[td]Geddy Lee[/td]
[td]=CONCATENATE(B5," ",A5)[/td]
[/tr]
[tr]
[td]Pastorius[/td]
[td]Jaco[/td]
[td]Jaco Pastorius[/td]
[td]=CONCATENATE(B6," ",A6)[/td]
[/tr]
[tr]
[td]Balzary[/td]
[td]Michael[/td]
[td]Michael Balzary[/td]
[td]=CONCATENATE(B7," ",A7)[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[tr]
[td][/td]
[td][/td]
[td]#VALUE![/td]
[td]=CONCATENATE(C2:C7)[/td]
[/tr]
[tr]
[/table]

And of course you get an Error

 

[J.Ty.]

It is not possible to concatenate a whole column of texts without a UDF.
I have made a formula solution with a helper column of cells.

However, it puzzles me so much that I have to ask: why do you want to concatenate so many names?

J.Ty.

Excel 2016 64 bit
	A	B	C	D
1	Smith	John	John Smith	John Smith
2	Harrison	George	George Harrison	John SmithGeorge Harrison
3	Best	Pete	Pete Best	John SmithGeorge HarrisonPete Best
4	Lee	Geddy	Geddy Lee	John SmithGeorge HarrisonPete BestGeddy Lee
5	Pastorius	Jaco	Jaco Pastorius	John SmithGeorge HarrisonPete BestGeddy LeeJaco Pastorius
6	Balzary	Michael	Michael Balzary	John SmithGeorge HarrisonPete BestGeddy LeeJaco PastoriusMichael Balzary
7
8			John SmithGeorge HarrisonPete BestGeddy LeeJaco PastoriusMichael Balzary
Sheet1

Cell Formulas
Range	Formula
D1	=C1
D2	=CONCATENATE(D1,C2)

 

[mreinsmith]

It's possible I'm just missing the obvious (not seeing the forest for the trees so to speak)

Like I said, just one of those things

just can't use a range

 

[mreinsmith]

Yes, That's works perfect

Thanks a milion

M

 

[jtakw]

Like I said, just one of those things

just can't use a range

Hi,

You're right, CONCATENATE can't accept ranges, you'll have to input individual cell references, but you don't really need a helper column:

Excel 2010
	A	B	C
1	Lastname	Firstname
2	Smith	John	John Smith
3	Harrison	George	George Harrison
4	Best	Pete	Pete Best
5	Lee	Geddy	Geddy Lee
6	Pastorius	Jaco	Jaco Pastorius
7	Balzary	Michael	Michael Balzary
8
9			John SmithGeorge HarrisonPete BestGeddy LeeJaco PastoriusMichael Balzary
Sheet1

Cell Formulas
Range	Formula
C9	=CONCATENATE(C2,C3,C4,C5,C6,C7)

 

[mreinsmith]

Oh I missed that. This is just an example

I do quite a bit of testing for transactional message queue systems

I need to create large sets of dummy transactions with certain criteria in X12 format and various proprietary translation maps.

Makes it easy to create strings and transactions over and over with different testing criteria

Sometimes the only way to reveal a bug in a complex system is to hammer away it until it occurs and identify the scenario the triggered it.

Not pretty but it works every time.

I've never hit the current limit (1,048,576 rows by 16,384 columns) in excel but I used to back in the day (if anyone even remembers works for windows)

It was just one of those things. I've done this so often and always in a rush trying to identify the cause of a problem.

I just never took the time to figure it out.

Thanks for your help

M

 

[J.Ty.]

Hi,

You're right, CONCATENATE can't accept ranges, you'll have to input individual cell references, but you don't really need a helper column:
[...]
[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<thead></thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=CONCATENATE(C2,C3,C4,C5,C6,C7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Indeed, as long as you work with a few rows. This solution does not scale directly to more than about 250 rows, and always requires manual modifications to the formula. There is CONCAT which seems to concatentate a given range of texts, but MS has a strange licesing policy and this function does not work on my newest Excel 2016. Helper column works for any number of rows, as long as you do not exceed the length limit of Excel strings.

J.Ty.

 

[jtakw]

Yeah, as I'm using 2010, I was merely pointing out that the CONCATENATE function does not accept ranges but individual cell references (as that was OP's mistake in Post #1), and Yes, you don't need a helper column. OP did not mention Large ranges until after my post, so I would agree with using a helper column in such a case. I've never used Excel newer than 2010, so I have no idea what new Functions might be available, although, I'm contemplating on upgrading...