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 &.

Filter by
Sorted by
Tagged with
0
votes
0answers
16 views

Concatenate multiple columns automatically [duplicate]

I have three columns A B C D E F G H I I want to write a formula such that it will combine the three columns, one on top of the other such that my fourth column is: A ...
1
vote
2answers
40 views

Find and place in a column, the first non-blank cell in each row from a range of columns

I'm attempting to create an ARRAYFORMULA that can extract the first non-blank cell value on each row in a range of columns. My latest attempt is this: =TRANSPOSE(QUERY(TRANSPOSE( A2:D6),"select * ...
1
vote
2answers
57 views

Remove Count column by repeating rows in Google Sheets

I have a worksheet, 'wi', in a Google Sheet similar to the following example but with an undetermined number of rows: Count | Entry | Description -------|-------|----------------- 2 | ...
0
votes
2answers
230 views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
2answers
32 views

Showing top 5 most frequent values and counts of each from a data range

I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
0
votes
2answers
23 views

How to filter after parsing CSV values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
0
votes
2answers
58 views

IF and IFS not working properly

I am trying to locate data present in different sheets. I have tried using IF and IFS. Below are the formulas I have used. For IF, I get NOT IN USE even when I know there should be one of the sheets ...
0
votes
1answer
39 views

If cell y contains any word from range x:x, mark cell y green

In Google Sheets, I have three columns (with different lengths!) Column1 contains firm names (50) Column2 containts firm names (100) I would like to mark a cell in Column1 green (or with a number in ...
0
votes
1answer
24 views

I want to get values from multiple URL's in Google Spreadsheet. IMPORTDATA doesnt work

I have 9000 lines with URL's in my Google Sheet. Example: https://steamcommunity.com/auction/ajaxgetgoovalueforitemtype/?appid=233860&item_type=10). Every URL returns JSON like this: { "success": ...
1
vote
1answer
11 views

How to create new cell from keyword of another cell?

I have a column that has job listings with extra info, so I only want to grab the job position and place it into a new cell. A1: Seeking Assistant to the marketing division... B1: Assistant (...
1
vote
1answer
34 views

How to Iterate between colunns and rows?

I am running the following forumula: =IF(INDEX(C$2:$2, 1, ROW(1:1)),CONCATENATE(B$2,"-",INDEX(C$1:$1, 1, ROW(1:1)))) On the following table Basically, If a value is present in column C2, I am ...
0
votes
1answer
112 views

Partial cell formatting with CONCATENATE in Google Sheets

Is there a way to format partially using CONCATENATE in Google Sheets? For example, =CONCATENATE("*", Sheet1!A1, Sheet1!B1), and I want to make the * bolded or blue in color or something else. I ...
1
vote
1answer
415 views

How to generate all combinations of letters in a word?

Using Google Sheets, how would I show all possible ways to type a word, for example: word Word WOrd WORd WORD wOrd woRd worD without any length word? The given word would go on Row A and the ...
1
vote
1answer
51 views

Automatic merging of duplicate cells under common ID

I have data in column A with IDs and a column B with data for those IDs. Right now I have multiple (usually 2-3) rows with the same ID but different data. What formula to use to merge those into one ...
1
vote
1answer
54 views

Filter and display duplicates in a table

Here's the link to my Google sheet: https://docs.google.com/spreadsheets/d/1SBD0_Ca0r_p3810u1ZNlbG6MXsvhFlLcwEJndVQJRP0/edit?usp=sharing The master data will show: And I would like to have a table ...
1
vote
1answer
62 views

Concatenate - Show blank cell result if a cell is empty

Could someone help me? I am trying to create a social media keyphrase strategy using keywords. I would like to know if Google Sheets could return a blank cell if one of the cells in the concatenate ...
0
votes
1answer
21 views

How to convert an array of unique records into an array where records are repeated N times?

I have a Google sheet that records items and their quantities. In order to input them into our SalesForce system, I need to split each record into repeated rows, according to the quantity of the item. ...
2
votes
1answer
37 views

Comparing Unique Values to a Range

I have the following formula =CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 )) The intention of this formula is to take a range and compare it to a list of unique identifiers on ...
1
vote
1answer
21 views

Сopy cells depending on values in another cell

In Google Sheets I try to copy cells B2:B4 depending on values in other cells C2:C4. For example - example.xlsx made in Excel. But stuck and can't find a solution on how to do it in Google Sheets. ...
1
vote
1answer
19 views

Rotate and duplicate row values in Google Sheets

I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example: a | b | c | d would become: a | b ----- a | c ----- a | d Is there any way to do this ...
1
vote
1answer
35 views

Is there a formula to list all possible 2 card combinations of 4 playing cards in Google Sheets?

Example: Is there a formula to list all possible 2 card combinations of 4 playing cards? 4 playing cards: A♥ A♦ A♣ A♠ The result should be 6 combinations only (Not 4 x 4 = 16 Combinations): 1: A♥ ...
-1
votes
1answer
339 views

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 ...
0
votes
2answers
25 views

Add columns based on rows

I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet. I have no idea if this is even possible, I've tried googling it. Might be ...
1
vote
1answer
15 views

COUNTIFS issue, output is always zero

I have no idea what I'm doing, considering there's plenty of topics on COUNTIFS. However, each solution I've tried, as well as my own solutions, which I shall list, end up in the same thing... ZERO. ...
0
votes
1answer
333 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
46 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
16 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?
-1
votes
2answers
25 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
44 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
467 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 ...
-1
votes
1answer
9 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
-1
votes
1answer
15 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
35 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
77 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
105 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
451 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
320 views

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

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
134 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
411 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
36 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
38 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
296 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
487 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
153 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
2answers
1k 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
647 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
1k 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
27 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
226 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
38 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 ...