Stack Exchange Network

Stack Exchange network consists of 175 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 &.

0
votes
1answer
19 views

Grabbing only non-empty cells from a row range for every row

I have a survey that sends responses into a Google sheet. The raw data that is inserted is messy, so I have another sheet that processes the incoming raw data. One of the things I need to do is: from ...
1
vote
1answer
20 views

How to convert multi-value answers into single column in Google Forms' response sheet?

I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this But that data is not very convenient. I want to have the data like this... Can I do that by ...
0
votes
2answers
14 views

How Do I Quickly Merge A Large Amount Of Cells?

So I'm trying to merge all cells in Col A and B so that A1 and B1 are one cell all the way for 150 rows, anybody knows how?
0
votes
2answers
12 views

How can I group by one column to join values in multiple rows?

Let's say I have a survey of people in teams, and what fruits they like: User | Team | Fruits ---------------------- Dave | Mine | Banana, Apple Steve | Mine | Apple, Orange Matt | Yours | ...
0
votes
5answers
26 views

Layperson's grid to normalized table

Introduction  (too long, don't read) I have a data-arrangement problem. I'm now up to three qualifying files from coworkers, and for that matter, I've run into a couple of questions here on SE ...
0
votes
1answer
10 views

How to TEXTJOIN in Google Sheets

I have ten columns in my rows which contain 5 sets of first name/last name. I want to combine each of these names in a final column using TEXTJOIN (or whatever function is appropriate if I'm missing ...
0
votes
1answer
5 views

How to split a cell into key/value rows?

Apple,3,Orange,4,Mango,10 How can I split this cell into key/value rows? Apple (a1), 3(a2) Orange (b1), 4(b2) If I just call split(), I get: Apple 3 Orange 4
0
votes
1answer
10 views

Outputting a cell x number of times in an array format

Using Google Sheets: Need to display a vertical list, where each occurrence in the A column of sheet1 is repeated based on the number in the B column of sheet1. I've created a dummy file to show what ...
0
votes
1answer
15 views

Convert string to URL in column

I just want to convert IDs as strings to URLs automatically. I tried this: =HYPERLINK(CONCATENATE("http://myjira.com/projects/BUWDI/issues/";A2);A2) I got an error... What do you think?
1
vote
2answers
18 views

How to combine a formula with text in one cell?

My spreadsheet contains data divided into weeks in a year and I would like to combine the following two pieces of information in one cell: =ISOWEEKNUM(TODAY()) Calendar week 'Calendar week' being ...
4
votes
4answers
59 views

Joining two rows of data with alternating delimiters

I'm working with two rows of values that are related to each other and trying to build a formula that will compile the data into one cell, with syntax that uses two delimiters. If a cell in the ...
0
votes
1answer
26 views

Concatenate using an array formula to result in last name.firstname

We have a form that asks for the students first name and last name. I am trying to using concatenate and an array formula to display "Lastname.Firstname". I used: =ARRAYFORMULA(CONCATENATE($B$52:$B,...
0
votes
1answer
22 views

Concatenating URL with a value to create link, but links only works partially

I have a column of cells in Google spreadsheet with values such as: b2397 b9504 b3333 b8655 bxxxx I would like to combine each value with a URL, for example: http://websitex.com?id= ...
1
vote
1answer
29 views

Generate custom sequence in Google Sheets

I would like to create a column with a custom sequence: A-Z,a-z,0-9. (Essentially the Base64 character set with the last two characters, + and /, left out) So, A1 would be AAA, A2 would be AAB ... ...
-1
votes
5answers
85 views

Combining ARRAYFORMULA with functions whose arguments can be rectangular ranges

I mean to concatenate several columns, so use of CONCATENATE is preferred over & (here I post an example with only 3 columns). For instance, D1 would contain =CONCATENATE(A1:C1). Then I would copy ...
1
vote
1answer
24 views

Formula for combining text if there are duplicate numbers

I have a table to combine text with conditions if in column A there is a duplicate number, then in the text in column B combined. The formula that I have made like this: =IF(COUNTIF($A:$A,A2)>1,...
0
votes
1answer
32 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
65 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
36 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
43 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
773 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
293 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
649 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
147 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
35 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
53 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
44 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 ...
4
votes
2answers
5k views

Query Multiple Ranges with Results in Row

I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return. ...
1
vote
1answer
110 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
1k 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
720 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
316 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
910 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
60 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 ...
11
votes
3answers
11k 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
165 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
297 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 ...
12
votes
1answer
22k 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
853 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
501 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
8k 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 ---...
8
votes
2answers
17k 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
7k 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, ",") ...