Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [concatenate]

Generally, but not necessarily, alongside tag [google-sheets], use this tag when the issue is joining two or more elements into a single element such as with the functions CONCATENATE/JOIN/TEXTJOIN (or CONCAT in Excel) or the operator &.

1
vote
1answer
25 views

JOIN two columns in one cell

What should I do if I want every time I fill out column C, to all data from B6 to C14 would appear on the notes?
1
vote
1answer
12 views

How to concatenate cells using ARRAYFORMULA across rows and columns?

I have a document that's a recurring CSV import/export. I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down. The problem is, I don't know how many ...
1
vote
2answers
29 views

Create random pairs without duplicates

I try to randomly pair people into unique pairs. I have two columns one with helping values created via Rand() and one that ranks upon these values with =INDEX($A$2:$A$164,RANK(S2,$S$2:$S$164)) ...
2
votes
3answers
28 views

How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
0
votes
1answer
531 views

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

I have the following 3 rows of data in cells A1:A3 in Google Sheets: Doe, Jane Doe, Joe John Doe I want the following in B1:B3: Jane Doe Joe Doe John Doe Essentially, I'm creating a new column ...
1
vote
1answer
235 views

Multiple IF conditions nested with a CONCATENATE

I have a formula as follows which I'm using for a scheduling system within Google Sheets: =IF(B2="","",(CONCATENATE($B$1&" "&B2&CHAR(10)&$C$1&" "&C2&CHAR(10)&$D$1&"...
1
vote
2answers
487 views

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

The function below is what I use to handle contact information (email/phone numbers). If the entry is an email id (has text), it will keep it as is. When a phone number is entered, it does a variety ...
2
votes
1answer
24 views

Placing text from one cell between certain characters of another sentence

Is it possible to take a word from one cell and place it in between certain characters/words of another cell? (Obviously, I can do this one time but I need to do the same thing multiple times) For ...
1
vote
2answers
110 views

Is it possibile to concatenate with bidimensional arrayformula?

I have a table like this: +----------+----------+----------+----------+ | Photo1-1 | Photo1-2 | Photo1-3 | | +----------+----------+----------+----------+ | Photo2-1 | Photo2-2 | | ...
-1
votes
1answer
33 views

Join two cells data - COCATENATE not working properly

I'm using the following formula to get the data of two cells: =CONCATENATE(C2,"Dia",E2) But it's not working properly. Take a look at the screenshots: It should be returning the Traffic + Date, but ...
1
vote
1answer
50 views

How can I insert formatted links in Google Sheet concatenations?

I am using Google Sheets to run mail merges using the script editor; it references a cell with an email address and a cell with the body of the email. I am able to concatenate a number of other ...
4
votes
1answer
43 views

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

I'd like to turn the image below: into the following: A1: Yellow // B1: The quick brown fox jumps over the lazy dog. A2: Blue // B2: The earliest known appearance of the phrase is from The Boston ...
1
vote
1answer
100 views

Split Text and reorganize and concatenate back together

I want a formula to split data "052 089652" and to reorganize it as "089652/A052/9" in Google Sheets. The formula needs to add "A" to the start of "052", add a "9" at the end, keep the leading zero, ...
2
votes
1answer
888 views

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

I'm trying to figure out "how to" maintain the date format when I concatenate a date formatted cell with a nondate formatted cell. Result: The Date Format Is Lost Formulas Used Desired Result: I ...
1
vote
1answer
599 views

Problem with CONCATENATE when used inside of arrayformula

I'm trying to update this formula so it will work inside of an 'arrayformula'. This works on its own and when I fill it down. =if(E2>0,CONCATENATE(B2, " | ",C2," | ",D2), "") This does not ...
4
votes
1answer
259 views

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

I'm using a formula that returns an Array of strings, so the result overwrites a range of cells, but I want to get the result in just that one cell. (The actual formula I'm using is WIKISYNONYMS ...
0
votes
3answers
68 views

Get an item value from a column item concatenated

How do I achieve a specific result concatenated with commas (ACT1, ACT2) from a filtered text ("Not delivered") and referenced from the position of the columns:
3
votes
3answers
743 views

Concatenate ordered values in one single string

I have a Google Spreadsheet where a person will add one name per row in a defined column (B). I need to generate a string like "<p>Adam - Becky - John - Robert</p>" in one single cell with ...
2
votes
2answers
57 views

Fill a column with consecutive numbers concatenated with a letter

I want to concatenate a string after a numerical value in some columns automatically so that way they are still treated as numeric values. For example: A 4010H 4011H 4012H 4013H I want to be able ...
1
vote
2answers
2k views

How to concatenate date value with a string?

I need to create the following value: // "ABC" + current date + some random number ABC20160405123 I do as follows: =CONCATENATE("ABC", NOW(), 123); But instead of getting expected value I get ...
10
votes
3answers
9k views

Concatenate several columns into one in Google Sheets

I want to concatenate/combine columns A, B and C into column D as displayed. The number of filled entries in A, B and C will vary over time, so absolute cell addresses cannot be used. What formula ...
6
votes
1answer
2k views

How do I concatenate data from multiple columns on one sheet into a single column on another sheet?

I need to take separate columns containing first and last names on one spreadsheet, and combine them into a single column on a different sheet. Right now my formula is =CONCATENATE(IMPORTRANGE("URL", ...
2
votes
1answer
27 views

How to concatenate “1 ” in front of many cells at once on a Google Sheets doc?

I am trying to concatenate many cells of a Google Sheets with "1 " in front of every cell. There are 360 cells so changing each individually would be a difficult task.
2
votes
1answer
1k views

CONCATENATE inside IMPORTRANGE inside Query

I found this formula: =QUERY(IMPORTRANGE("key","A:Z"),"SELECT Col1, Col2, Col3 WHERE Col1 contains 'value' ORDER BY Col1") here and I'd like to add a concatenate function where value is taken from ...
2
votes
1answer
134 views

Formula to grab specific cells and concatenate them together but only if they are not blank

I have a formula that breaks when 'nothing' is in any of the cells: =If(ISBLANK(A1),,A1&IF(ISBLANK(B1),,CHAR(10)&B1&IF(ISBLANK(C1),,CHAR(10)&C1))) I'm expecting this of course, as I ...
3
votes
1answer
253 views

Calling a date with CONCATENATE is resolving to strange number

Using: =concatenate("Review Check on ", B2) B2 is 4/20/2015, that I created using the date insert shortcut (command+; on Mac). The final cell resolves to: Review Check on 42114 The /s have ...
10
votes
1answer
19k views

How do I concatenate a string literal of my cell to a string of another cell?

I am trying to make life easier for myself by organising my teaching materials for private tutees of mine from various subjects/educational levels in google spreadsheets. I want to know how I can ...
2
votes
1answer
37 views

Duplicate then concatenate in Google Spreadsheets

How do I join a to both sides of : to result in a:a? I could use something like =join(":","a","a"), but in this case, my "a" is another formula and it is very long. I'm trying to find a way so I don'...
2
votes
1answer
790 views

Appending text to each column in a Google Sheets

I have a Google Sheets that has about 200 columns. Each one is a name, and to each one I want to append -banner.jpg. For example: abcde fghij klmno will all be updated to: abcde-...
1
vote
2answers
475 views

How can I merge into one cell, all the non-zero values in a row each concatenated with the column header?

I hope this example is clear. I have played with join and filter but can't get the Summary field to display as below: Row 1 is a list of subjects. Row 2 is a teacher's subject allocation (calculated ...
8
votes
3answers
7k views

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

I imagine this is laughably simple but I just don't know what to do. I have a column (A) of cells with strings in them and I want to simply prepend each string in every cell with a web address: A ---...
7
votes
2answers
15k views

CONCATENATE a string with an ArrayFormula

I have the following string in column H and I. H2="http://" I2="aaa" H3="http://" I3="bbb" I want to have a auto-generated J column, which results like: J2="http://aaa" J3="http://bbb" So I put ...
5
votes
6answers
6k views

Split, reorder, concat inline

I want to split a cell, reorder the pieces and recombine. For example: | name | reordered name | | Page,Larry | Larry Page | | Brin,Sergey | Sergey Brin | I know that =SPLIT(A2, ",") ...
2
votes
1answer
479 views

Is it possible to use the importHTML function with a concatenated URL?

What I'm trying to do is use a URL previously made with the CONCATENATE function in an IMPORTHTML function. When I try: importHTML (H1, "table", 0) Where H1 is the cell with the concatenated URL, I ...
14
votes
4answers
27k views

How Do I Combine Columns In Google Spreadsheets? [duplicate]

I need to combine multiple columns into a single column, so: one four seven two five eight three six nine Becomes: one two three four five six seven eight nine
16
votes
4answers
22k views

Concatenating only filled cells

I currently have a spreadsheet to keep track of scores in a card game. There can be between two and five players. I have the following: | Players | |----------| | Dave | | Paul | | John ...
3
votes
2answers
3k views

Which of the concatenation options are the best to use?

I want to accomplish the following: string = TEST; cell reference = B2, value = ING; RESULT = TESTING Which of the following options are the best to use?: CONCAT: =CONCAT("TEST",B2) & ...
13
votes
2answers
23k views

Format a date fetched from another cell for display using CONCATENATE

Given say 2 different dates in A1 and B1, what I want to do is just show the range of dates as a label in A2 by using =concatenate(A1, " - ", A2) However, A1 is in the default format (MM/DD/YYYY) and ...
9
votes
2answers
20k views

Adding one column to a string to dynamically create a URL in the next column

I have a spreadsheet of product ID codes that I'd like to turn into links, and from everything I've read, the concat or concatenate functions in Google Sheets should work for that, but so far ...