37
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 ...
37
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).
28
Try TEXTJOIN:
=textjoin(" vs ",1,C10:C14)
26
The following formula can be used to merge one column after another:
={A1:A5; M1:M5}
18
This is what you are looking for:
=ARRAYFORMULA((IF(ROW(J:J)=1,"formulaTest",H1:H & I1:I)))
16
A shorter version of the formula in this other answer
=FILTER({A:A;B:B;C:C}, LEN({A:A;B:B;C:C}))
15
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.
11
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
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
21.3k16 gold badges89 silver badges183 bronze badges
6
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}),"|"))
6
You can do the formatting on the fly:
=text(B2,"m/d/yyyy")&C2
Google Sheets Help: TEXT()
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 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
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
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
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 \
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.
3
perhaps try it like this:
=ARRAYFORMULA(IF(LEN(A:A), B:B&"."&A:A, ))
2
Use these GSheet functions to reverse the order of the words in a string.
Write in cell A1 ="TRICK NO THIS IS"
and in cell B1 =JOIN(" ";TRANSPOSE(SORT(TRANSPOSE(SPLIT(A1;" "));1;FALSE)))
It creates IS THIS NO TRICK from the input in A1.
Read the formula from inside to outside:
SPLIT creates and array in a row ("virtual" if the formula is used as shown) of ...
2
All is considered a string when you concatenate elements. Therefore you need to format the text as a date. The following formula does that.
Formula
="Happy " & TEXT(B2, "dd/MM/yyyy")
Reference
Google Spreadsheet help, TEXT()
answered Apr 20 '15 at 17:12
Jacob Jan Tuinstra
21.3k16 gold badges89 silver badges183 bronze badges
2
Try this formula.
Formula
=REGEXREPLACE(REPT(A1 & ":",2),".$","")
Explained
First the following string is being repeated twice: RESULT_OF_A1 combined with a semicolon.
The last semicolon in this repetition is being removed by the REGEXREPLACE formula. It simply replaces the last character by an empty space. The result of your formula is being used ...
Only top voted, non community-wiki answers of a minimum length are eligible
