32
It looks like you want the & operator
I'm not quite clear which thing you are concatenating to which in the question; however, you can do either
=$BJ$2 & "24"
or
=$BJ$2 & BH1
or
="Paper" & BH1
assuming you are on row 2 and the number is on row 1 (the $ locks the cell so that it doesn't change when copying/pasting).
Here's an example ...
31
Yes, by FILTERing the array:
= JOIN( " vs " ; FILTER(C10:C14; NOT(C10:C14 = "") ))
Thus, the JOIN method will only operate on non-empty cells.
I have set up an example spreadsheet.
Also, check the Google Spreadsheets function list (search for FILTER).
25
The following formula can be used to merge one column after another:
={A1:A5; M1:M5}
23
This worked great for me:
=A1&TEXT(A2," mmmm d, yyyy")
Source
22
Try TEXTJOIN:
=textjoin(" vs ",1,C10:C14)
15
This is what you are looking for:
=ARRAYFORMULA((IF(ROW(J:J)=1,"formulaTest",H1:H & I1:I)))
14
A shorter version of the formula in this other answer
=FILTER({A:A;B:B;C:C}, LEN({A:A;B:B;C:C}))
13
You can do that with a formula, so that e.g. column B will display the URL:
="http://foo.com" & A1
(the & is the string concatenation operator in Google Spreadsheets)
Put this formula in B1, and copy-paste it to other cells in the B column.
9
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 formula. You cannot use CONCATENATE because (as mentioned by others) it will return the whole array.
8
If the columns are A, E, G (for example), then the following command combines them:
={filter(A:A, len(A:A)); filter(E:E, len(E:E)); filter(G:G, len(G:G))}
Here, I first filter the columns by len() to remove blank cells. Then I combine the columns together.
An example combining columns in different sheets:
={filter(sheetone!A:A, len(sheetone!A:A)); ...
6
CONCAT() and & do the same thing, so in that case there isn't a difference between them (just personal preference I suppose)
CONCATENATE() can also be used to join 2 items, however it's typically used to join greater than 2 terms.
Which one is best? I'd say whichever option makes the formula the most readable, in this case I would say CONCAT() only ...
6
You can do the formatting on the fly:
=text(B2,"m/d/yyyy")&C2
Google Sheets Help: TEXT()
5
You can put this in another cell: ="http://foo.com" & A1
This will result in:
A | B
----------------------------------------
1| /foo/bar/ | http://foo.com/foo/bar/
2| /foo/foo/ | http://foo.com/foo/foo/
3| /foo/baz/ | http://foo.com/foo/baz/
Or, if you prefer, you can use the CONCATENATE function:
=CONCATENATE("http://foo.com",A1)
...
5
For completeness, use this formula to add in B1:
=ARRAYFORMULA("http://foo.com" & A1:A)
See similar post here on Web applications: CONCATENATE a string with an ArrayFormula
answered Feb 19 '14 at 15:53
Jacob Jan Tuinstra
20.3k14 gold badges82 silver badges172 bronze badges
5
With the following custom function (as you're accustomed to writing in JavaScript) it is possible as well:
Code
function myReverse(range) {
var output = [];
if(range[0].length > 1) {
throw ("function can only handle a column range");
} else {
for(var i in range) {
var name = range[i][0].split(",");
output.push([name.reverse()....
5
You can transform the columns by using TRANSPOSE, i.e., assume "First member, Second member, Third member" are A1:A3, you can use function in somewhere,
= TRANSPOSE(A1:A3)
will fill column as below,
First member
Second member
Third member
To repeat the column before and after team members, you can try,
= TRANSPOSE (SPLIT(REPT(CONCAT(A1, ","), ...
5
Both of the solutions above work if there is at least one cell containing text. However:
= JOIN(" vs ",SPLIT(JOIN("%",C10:C14),"%",0))
Would return %%%% if C10:C14 were all empty and.
= JOIN( " vs " ; FILTER(C10:C14; NOT(C10:C14 = "") ))
Would return #N/A if C10:C14 were all empty.
However, you can slightly amend the first solution to replace the % ...
4
I've found another solution:
=JOIN(" vs ",SPLIT(JOIN("%",C10:C14),"%",0))
The % can be any symbol really that isn't present in the list, like a comma, or ampersand, or question mark.
4
This works OK for me:
=concatenate("http://example.com/search.php?Code="; B2)
4
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.
3
Please try:
Find: (.+)
Replace with: $1-banner.jpg
Search: This sheet
Search using regular expressions
Replace all.
3
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(textjoin("|",1,{A1:A4;B1:B4;C1:C4}),"|"))
3
You can use JOIN to stitch the pieces together with a given delimiter:
=join(",", f o r m u l a )
3
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 referred as the alias.
We could use getFormula() and getFormulas() to get the cells formulas and to parse the URL from the them. See Extract the link text and URL ...
3
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))
Concatenate will work across different sheets.
2
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 decimal separator, replace the comma in the above formula by backslash \
Only top voted, non community-wiki answers of a minimum length are eligible
