25
The following formula can be used to merge one column after another:
={A1:A5; M1:M5}
25
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).
23
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 ...
22
Always enclose string literals in double quotation marks.
= "http://example.com/search.php?Code=" & B2
19
This worked great for me:
=A1&TEXT(A2," mmmm d, yyyy")
Source
12
There isn't a format function in Google spreadsheets. You have to use the TEXT(number, format) function.
I have to admit I always assume it will be format, but Excel uses Text so Google has to use it also.
12
This is what you are looking for:
=ARRAYFORMULA((IF(ROW(J:J)=1,"formulaTest",H1:H & I1:I)))
10
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.
10
A shorter version of the formula in this other answer
=FILTER({A:A;B:B;C:C}, LEN({A:A;B:B;C:C}))
9
Try TEXTJOIN:
=textjoin(" vs ",1,C10:C14)
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
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.
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
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
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 ...
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
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
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.
Only top voted, non community-wiki answers of a minimum length are eligible
