40 votes
Accepted

Concatenating only filled cells

Try TEXTJOIN: =textjoin(" vs ",1,C10:C14) The second parameter is set to 1 indicating that empty cells should be ignored. Zero will include those empty cells.
pnuts's user avatar
  • 17.6k
16 votes

Concatenate several columns into one in Google Sheets

A shorter version of the formula in this other answer =FILTER({A:A;B:B;C:C}, LEN({A:A;B:B;C:C}))
14 votes

CONCATENATE a string with an ArrayFormula

In Google Sheets, the simplest solution is to just use CONCAT instead of CONCATENATE. =ARRAYFORMULA(CONCAT(H1:H,I1:I)) CONCAT will return only two values and can be used in this manner for an array ...
craig_in_jp's user avatar
6 votes

Concatenate several columns into one in Google Sheets

A little longer, but can be constrained to a limited array (not whole columns): =transpose(split(textjoin("|",1,{A1:C4}),"|")) where the order does not matter. Where it does: =transpose(split(...
pnuts's user avatar
  • 17.6k
6 votes
Accepted

Date Format Lost When Combining Two Cells Using "&" and CONCATENATE in Google Sheets

You can do the formatting on the fly: =text(B2,"m/d/yyyy")&C2 Google Sheets Help: TEXT()
pnuts's user avatar
  • 17.6k
5 votes

How do I prepend a string to a column of cells in Google Spreadsheets?

You can do this using the search and replace feature in Google Sheets with regular expression and specified range. In the search and replace dialog use these values Find: ^/(.+) Replace: http://$1 ...
Luke C's user avatar
  • 51
4 votes
Accepted

Problem with CONCATENATE when used inside of arrayformula

Please try: =ArrayFormula(if(E2:E>0,B2:B&" | "&C2:C&" | "&D2:D,"")) I would have expected: =arrayformula(if(E2:E>0,CONCATENATE(B2:B," | ",C2:C," | ",D2:D),"")) to produce the ...
pnuts's user avatar
  • 17.6k
4 votes

Concatenate ordered values in one single string

Use the JOIN() function: =join(" - ",sort(Sheet1!B:B, 1, TRUE)) SORT() returns an array of values. JOIN() concatenates an array with a joining string, in this case " - " without the quotes.
Karl_S's user avatar
  • 627
4 votes

How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

After analysis, I found kind of compact solution. a1:a, "en", "ko" are options it doesn't need ARRAYFORMULA: =TransPose(Split(GoogleTranslate(Join(". ",A1:A), "en&...
Sage's user avatar
  • 41
3 votes
Accepted

Join two cells data - COCATENATE not working properly

Date is a number. It's the number of days from Jan 1899. It's just formatted to look like yyyy-mm-dd. To get displayed value instead of actual value, use TO_TEXT =CONCATENATE(C2,"Dia", TO_TEXT(E2)) ...
TheMaster's user avatar
  • 3,352
3 votes

How can I insert formatted links in Google Sheet concatenations?

As the OP already realized, getValue() and getValues() return a string object for cells containing an hyperlink and the value returned when the object is concatenated is the link text that the OP ...
Rubén - Volunteer Moderator -'s user avatar
3 votes
Accepted

Constrain a Array<string> formula result to a list in a single cell

You can use JOIN to stitch the pieces together with a given delimiter: =join(",", f o r m u l a )
pnuts's user avatar
  • 17.6k
3 votes

Split content of one row into multiple rows?

Use the Google Sheets array handling features and FILTER to get the desired result =FILTER({A:A,B:B,E:E;A:A,C:C,E:E;A:A,D:D,E:E},LEN({A:A;A:A;A:A})) Note: If you spreadsheet use comma , as the ...
Rubén - Volunteer Moderator -'s user avatar
3 votes

Concatenate using an array formula to result in last name.firstname

perhaps try it like this: =ARRAYFORMULA(IF(LEN(A:A), B:B&"."&A:A, ))
user0's user avatar
  • 26.5k
3 votes
Accepted

How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

ARRAYFORMULA does not support GOOGLETRANSLATE. The best you can have is to pre-program the array like this: ={GOOGLETRANSLATE(B20, "en", "de"); GOOGLETRANSLATE(B21, "en", "de"); GOOGLETRANSLATE(...
user0's user avatar
  • 26.5k
2 votes

CONCATENATE a string with an ArrayFormula

Formula =ARRAYFORMULA(UNIQUE(IF(V2:V="","",(CONCAT(CONCAT(V2:V," - "),W2:W))))) Functions ARRAYFORMULA -   Enables applying the formula to an entire column UNIQUE -   ...
Jean-François's user avatar
2 votes
Accepted

How do I concatenate the data in column B based on the data from column A?

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 ...
pnuts's user avatar
  • 17.6k
2 votes

Query Multiple Ranges with Results in Row

We need to use five QUERY functions, one for each set of columns, but then add something else to remove the blank columns. The following formula use JOIN and SPLIT: =SPLIT(JOIN(",",IFERROR({QUERY({A2:...
Rubén - Volunteer Moderator -'s user avatar
2 votes
Accepted

Split Text and reorganize and concatenate back together

Assuming You provided a exact format of data, =REGEXREPLACE(F2,"(\d+) (\d+)", "$2/A$1/9")
TheMaster's user avatar
  • 3,352
2 votes
Accepted

Multiple IF conditions nested with a CONCATENATE

here it is: https://docs.google.com/spreadsheets/d/ =IF(A2<>"",CONCATENATE( IF(B2<>"",$B$1&": "&B2&CHAR(10),), IF(C2<>"",$C$1&"...
user0's user avatar
  • 26.5k
2 votes

How to concatenate cells using ARRAYFORMULA across rows and columns?

=ARRAYFORMULA(TRANSPOSE(SPLIT(TEXTJOIN("♪", 1, QUERY(TRANSPOSE(IF(B2:Z<>"", B2:Z&",", )), "select *", ROWS(B2:B))), "♪", 0)))
user0's user avatar
  • 26.5k
2 votes
Accepted

How to convert an array of unique records into an array where records are repeated N times?

=TRANSPOSE(SPLIT(JOIN(",", ARRAYFORMULA(REPT(SPLIT( INDIRECT("A2:A"&COUNTA(A2:A)+1), ",")&",", INDIRECT("B2:B"&COUNTA(B2:B)+1)))), ",")) =ARRAYFORMULA({TRANSPOSE(SPLIT(JOIN(",", REPT(...
user0's user avatar
  • 26.5k
2 votes

Partial cell formatting with CONCATENATE in Google Sheets

If you are after this then it's not possible: You can do this only manually. If you just want more visible star you can use one of these instead like: =CONCATENATE("✱", Sheet1!A1, Sheet1!...
user0's user avatar
  • 26.5k
1 vote

How to concatenate columns for multiple rows simultaneously in Google Sheets?

As we've have found, it's not difficult to write a formula to achieve this outcome. But an ARRAYFORMULA is another matter and it seems (famous last words!) that it easier said than done (assuming even ...
Tedinoz's user avatar
  • 5,701
1 vote

How to perform multiple operations where the IF statement is true?

So, if E2 is a number, you want to run REGEXREPLACE(TEXT(E2,"##############"),"\D","") on that number, and then do IF(LEFT(value, 2) = "44", REPLACE(value,1,2,"0"), CONCAT("0",value)) on the result of ...
exterrestris's user avatar
1 vote
Accepted

How to perform multiple operations where the IF statement is true?

IF statement consists of 3 parts. understanding how it works: if (something is something, do this, if not - do this) so in formula expression: =IF(E2="something", "its ok, do nothing", "E2 isn't ""...
user0's user avatar
  • 26.5k

Only top scored, non community-wiki answers of a minimum length are eligible