Web Applications Stack Exchange
Web Applications 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 &.

92 questions
34
votes
4 answers

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 | | | | | At the moment I…
Djave
  • 746
  • 2
  • 8
  • 14
29
votes
2 answers

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 take a string literal such as "Paper " and…
hello_there_andy
  • 411
  • 1
  • 4
  • 7
23
votes
3 answers

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 I want the label to have dates in the format DD…
Alok
  • 467
  • 2
  • 4
  • 15
20
votes
4 answers

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 could I put into D1 to fill the column as…
Paulb
  • 323
  • 1
  • 2
  • 5
19
votes
4 answers

How Do I Combine Columns In Google Spreadsheets?

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
Undistraction
  • 347
  • 2
  • 3
  • 7
18
votes
4 answers

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 the following formula in J1: = ARRAYFORMULA…
srjohnhuang
  • 281
  • 1
  • 2
  • 4
14
votes
4 answers

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 -------- 1| /foo/bar/ 2| /foo/foo/ 3|…
Timmy O'Mahony
  • 243
  • 1
  • 2
  • 6
10
votes
6 answers

Split content of one row into multiple rows?

I have a table where each row contains data like this: Team name | First member | Second member | Third member | Team description Would it be possible to transform/break this data so that each row contains only one member? Like this: Team name |…
Dom Delimar
  • 1,864
  • 3
  • 14
  • 12
10
votes
2 answers

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 everything I try results in an error. What I want to…
Kzqai
  • 248
  • 1
  • 3
  • 13
7
votes
1 answer

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", "A1:A3"), " ", IMPORTRANGE("URL", "B1:B3")) What…
Jon
  • 207
  • 3
  • 6
7
votes
2 answers

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. Formula: =QUERY({A2:C2,D2:F2,G2:I2,J2:L2,M2:O2},…
Bardo
  • 317
  • 1
  • 4
  • 13
5
votes
6 answers

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, ",") will split the values into the next two columns…
Andrew Hubbs
  • 171
  • 1
  • 2
  • 7
4
votes
2 answers

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) & operator: ="TEST" & B2 CONCATENATE: =CONCATENATE("TEST",…
Jacob Jan
  • 23,322
  • 16
  • 106
  • 195
4
votes
3 answers

How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I created a simple sheet that translates words from…
skytreader
  • 151
  • 1
  • 6
4
votes
4 answers

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 active row contains a value, I want to pull the…
Steven D.
  • 51
  • 4
1
2 3 4 5 6 7