concatenategoogle sheetsgoogle-sheets-arrayformula
How do I apply the formula as described here..
https://webapps.stackexchange.com/a/76395/279173
which is this part:
=CONCATENATE(""&LEFT(A1,10)&" ...")
to an entire separate column along side it?
basically I want to trim a column to say max 10 characters and put the result on a new entire column side by side.
I have tried and enclosed it in ARRAYFORMULA like this one:
=ARRAYFORMULA(CONCATENATE(""&LEFT(A1:A,10)&"..."))
but throws a limit error and it seem not to work.
I was able to reduce the formula and add ARRAYFORMULAE to make it work with ranges.
=ARRAYFORMULA(
IF(ISBLANK(A:A),
"",
SUBSTITUTE(
ARRAYFORMULA(
A:A &
IF(ISERROR(FIND("?", A:A, 1)), "?", "&") &
"utm_campaign=" &
B:B &
"&utm_medium=" &
C:C &
"&utm_source=" &
D:D &
IF(E:E<>"","&utm_content=","") &
E:E &
IF(F:F<>"","&utm_term=","") &
F:F
)
, " ", "%20"
)
)
)
copy / paste
=ARRAYFORMULA(IF(ISBLANK(A:A), "", SUBSTITUTE(ARRAYFORMULA(A:A & IF(ISERROR(FIND("?", A:A, 1)), "?", "&") & "utm_campaign=" & B:B & "&utm_medium=" & C:C & "&utm_source=" & D:D & IF(E:E<>"","&utm_content=","") & E:E & IF(F:F<>"","&utm_term=","") & F:F), " ", "%20")))
It is not possible to use CONCATENATE formulae in combination with ARRAYFORMULAE. Therefore we need to go about this differently, by using an AMPERSAND to join different strings together. If the concatenated string is prepared and the ranges are chosen, then substitute all empty spaces with %20. This, together with an IF statement to check column A for presence, is all wrapped inside yet another ARRAYFORMULA.
I've created an example file for you: Apply complicated formula to whole column
You might try:
=join(" ",FILTER(B:B,A:A=A1))
copied down to suit (I haven't because you data sample lacks consideration for those who rely on screen readers) then Copy/Paste special values only over the top and if say in ColumnC then:
=unique(C:C)
Copy/Paste special values only over the top again and delete surplus.
Best Answer
CONCATENATEwill try to join everything inside its parentheses; so applying it to an entire column will cause Sheets to try to form one string from all data in that column, hence the limit error.The ampersand alone does this in arrays.
This is how I would approach this (keeping in mind that I can't see your actual data):
ArrayFormula(IF(A:A="",,IF(LEN(A:A)<=10,A:A,LEFT(A:A,10)&"...")))