41
votes
Accepted
Concatenating only filled cells
Use TEXTJOIN instead of JOIN
TEXTJOIN* (unlike JOIN) can skip blank values by setting its ignore_empty argument to TRUE or 1:
=textjoin(" vs ", 1, C10:C14)
=textjoin(" vs ", TRUE, ...
- 17.7k
15
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 ...
- 151
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()
- 17.7k
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(...
- 17.7k
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
...
- 51
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.
- 637
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 ...
- 17.7k
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&...
- 41
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 )
- 17.7k
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))
...
- 3,342
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 ...
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 ...
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, ))
- 27k
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(...
- 27k
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 - ...
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 ...
- 17.7k
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:...
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")
- 3,342
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&"...
- 27k
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)))
- 27k
2
votes
Add columns based on rows
To add columns based on data, use the Spreadsheet Service or the Spreadsheet Advanced Service of Google Apps Script.
You should decide how the code will be called. This could be from the Apps Script ...
2
votes
Add columns based on rows
=ARRAYFORMULA(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(SPLIT(REPT(TRANSPOSE(
QUERY(TRANSPOSE(A1:C2&"♂"),,999^99))&"♀",
COUNTA(Sheet2!A3:A)), "♀")),,999^99)), "♂"))
- 27k
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(...
- 27k
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!...
- 27k
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 ...
- 5,811
1
vote
Accepted
Generate custom sequence in Google Sheets
=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE((CHAR({{
TRANSPOSE(SPLIT( TEXTJOIN(",",1,REPT(ROW(INDIRECT("A"&65&":A"&68))&",",3844)),","));
TRANSPOSE(SPLIT(","&TEXTJOIN(...
- 27k
1
vote
Combining ARRAYFORMULA with functions whose arguments can be rectangular ranges
=ARRAYFORMULA(REGEXREPLACE(
TRANSPOSE(QUERY(TRANSPOSE({A3:F10;"","","","","",0}),"where Col1!=0"))," ",""))
this solution exploits the broken logic of QUERY. it can take a rectangular range as an ...
- 27k
1
vote
1
vote
How to concatenate cells using ARRAYFORMULA across rows and columns?
In a general sense, the OP seeks to concatenate an array of text with dimensions RxC rowwise, returning an Rx1 array of concatenated text. An earlier answer can be streamlined, using the OP's range of ...
- 111
Only top scored, non community-wiki answers of a minimum length are eligible