OFFSET and CONCATENATEMULTIPLE: can they be used together?

[itr674]

Can OFFSET be added to CONCATENATEMULTIPLE so the range will change accordingly as the formula is copied down the rows?

I am using UDF CONCATENATEMULTIPLE to combine text that in a range of cells to sentences in a single cell.

Using UDF CONCATENATEMULTIPLE to get a range of cells into one single cell.
IF(B2<>1,"",CONCATENATEMULTIPLE(C$2:C$3,""))

Then using SUBSTITUTE to add a carriage return and "*" at the beginning of each sentence.
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E2,".","."&CHAR(10)&"* ",1),
".","."&CHAR(10)&"* ",2),".","."&CHAR(10)&"* ",3),".","."&CHAR(10)&"* ",4),".","."&CHAR(10)&"* ",5),
".","."&CHAR(10)&"* ",6),".","."&CHAR(10)&"* ",7),".","."&CHAR(10)&"* ",8),".","."&CHAR(10)&"* ",9),".","."&CHAR(10)&"* ",10),
".","."&CHAR(10)&"* ",11))

 

[jasonb75]

Do you need to use a UDF? Newer versions of excel (2019 onward) have a native function called TEXTJOIN which will work just as well without the need for substitute.

=IF(B2=1,TEXTJOIN(CHAR(10)&"*",1,C2:INDEX(C:C,AGGREGATE(14,6,ROW(A2:A20)/(A2:A20=A2),1))),"")

With your UDF, perhaps this will work in the same way

=IF(B2<>1,"",CONCATENATEMULTIPLE(C2:INDEX(C:C,AGGREGATE(14,6,ROW(A2:A20)/(A2:A20=A2),1)),CHAR(10),"*"))

Without seeing the code for the UDF I can't say for certain what will work.

 

[itr674]

We don't have 2019 yet, wish we did.

I your formula and got the #VALUE! error.

This is the UDF code:
Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
Result = Result & Cell.Value & Separator
Next Cell
CONCATENATEMULTIPLE = Left(Result, Len(Result) - 1)
End Function

 

[jasonb75]

Try this version of the formula instead, remember to array confirm it with Ctrl Shift Enter.

=IF(B2<>1,"",CONCATENATEMULTIPLE(C2:INDEX(C:C,AGGREGATE(14,6,ROW(A2:A20)/(A2:A20=A2),1)),CHAR(10)&"*"))

There is an alternative UDF that I'm more familiar with, if this version of the formula doesn't work then I'll find the code and post that one instead.

 

[itr674]

That is very close...but I forgot to put in the criteria that I want CHAR(10) after each "." so each sentence is underneath each other...

 

[jasonb75]

You did include it, I just misunderstood exactly what you were trying to do. I didn't notice that some cells only had part sentences while others had multiple sentences.

See if this is any better.

=SUBSTITUTE(IF(B2<>1,"",CONCATENATEMULTIPLE(C2:INDEX(C:C,AGGREGATE(14,6,ROW(A2:A20)/(A2:A20=A2),1)),"")),".",CHAR(10)&"*")

It's preferable if you attach examples using XL2BB (button on reply box toolbar) instead of screen captures so that we can paste the data to excel and test formulas before posting them.

 

[Bo_Ry]

Please try at E2

=IF(B2=1,SUBSTITUTE(CONCATENATEMULTIPLE(C2:INDEX(C2:C10,MATCH(2,INDEX(1/(A2:A10=A2),)))," "),".",CHAR(10)&"*"),"")

 

[itr674]

It's preferable if you attach examples using XL2BB (button on reply box toolbar) instead of screen captures so that we can paste the data to excel and test formulas before posting them.

I would love that, but on work computer and they are locked down...

Your last formula returns this in the first cell, notice no space between "best" and "girl",

The bestgirl won the race

 

[itr674]

Also notice every other sentence is missing a space before the "*"
The rabbit ran
* Then stopped
*then ate some fruit
* Butthen changed the mind
*and they all lived together

 

[jasonb75]

Messy data = messy formula, try

=SUBSTITUTE(TRIM(IF(B2<>1,"",CONCATENATEMULTIPLE(C2:INDEX(C:C,AGGREGATE(14,6,ROW(A2:A20)/(A2:A20=A2),1))," "))),".",CHAR(10)&"* ")

edit:- moved position of TRIM function.