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
-1 votes
0 answers
13 views

Automatically Format & Combine Cells [duplicate]

I have a pretty specific need and I’ve had a lot of trouble figuring how even to get started. I have several columns of text. My goal is to combine them into one cell (per row), such that: The first ...
Paul's user avatar
  • 1
0 votes
0 answers
72 views

Array formula & concatenate

i want to make 6 number id code with array formula by combining the job code and the number list... 2 first number is the job code and 4 last number is the number list.. I tried to divide the job code ...
Febriana Angelina Suhendra's user avatar
1 vote
1 answer
15 views

Need help coming up with formula to count and concatenate specific things

Though I'm not the best at Excel/Google Sheets (this is Sheets specifically), I'm usually able to figure out how to do the things I need. However, this one is stumping me. Here is a screenshot of my ...
ghostxshark's user avatar
1 vote
1 answer
115 views

Splitting hierarchical codes into parent items (Google Sheets)

source cell: CCA, BF result cell: C, CC, CCA, B, BF CCA is a complex code relating to a hierarchy. It means, CCA is child of CC, which is child of C. So, the formula should split into all parents (C ...
Chris's user avatar
  • 11
0 votes
1 answer
78 views

"Multiply" (Concatenate) Text Columns in Google Sheets

Simple question that I can't seem to find a solution for. I have text data in two columns like Name Num a 1 b 2 and I would like to produce a single formula if possible to output a 3rd column ...
Yofi's user avatar
  • 1
0 votes
1 answer
491 views

Concatenate multiple cells in one cell based on ID

I'm trying to figure out a formula for concatenating data in cells based on a condition. Specifically, I need to output rows for each unique "Conversation ID" (found in column C) and ...
Martins Kruklis's user avatar
1 vote
1 answer
135 views

Apply Concatenate formula to entire column

How do I apply the formula as described here.. https://webapps.stackexchange.com/a/76395/279173 which is this part: =CONCATENATE(""&LEFT(A1,10)&" ...") to an entire ...
LazerBlazer's user avatar
1 vote
1 answer
513 views

Query, Import Range and Concat

I'm wanting to import whole rows of data from 'SPREADSHEET A' to 'SPREADSHEET B' but only if Column 11 in 'SPREADSHEET A' contains the text 'EXPIRED'. The data in 'SPREADSHEET A' contains about 12 ...
April's user avatar
  • 11
0 votes
1 answer
2k views

How do I combine multiple results of two filter formulas in Sheets?

Share Link: https://docs.google.com/spreadsheets/d/1-SoQS5Y4EvExbYTOjBGdx7EAq4OYlS0sPENFsblrTr4/edit?usp=sharing Formula Currently: =CONCATENATE("[",FILTER('BLS - Occupational Employment ...
Kevin Handy's user avatar
0 votes
2 answers
108 views

concatenation truncating numberic value

in a cell J21 I have entered a numeric value 1.10000. The cell is set to numeric value with 5 decimal places. When using concatenate =CONCATENATE("=CONCATENATE("@ £/€ rate: ",(J21)) in ...
Low Rep Mods are bad's user avatar
3 votes
2 answers
1k views

Preserve leading zeros in a Google Sheets CONCATENATE function

I am trying to create about 50 cells that have number combinations which look like variations of this: 01:05:39 I am using a RANDBETWEEN function to generate three pairs of numbers (e.g. =RANDBETWEEN(...
Peter Bentley's user avatar
1 vote
2 answers
176 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 * ...
fsteff's user avatar
  • 147
1 vote
2 answers
440 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 | ...
Tom's user avatar
  • 163
1 vote
2 answers
3k 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 ...
Tom's user avatar
  • 163
0 votes
2 answers
2k 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 ...
Charles's user avatar
  • 11
0 votes
2 answers
250 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 ...
Shadow43375's user avatar
-1 votes
1 answer
198 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 ...
Bibek's user avatar
  • 9
0 votes
1 answer
141 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 ...
Peter the lol's user avatar
0 votes
1 answer
228 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": ...
Finindie's user avatar
1 vote
1 answer
23 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 (...
nr1's user avatar
  • 13
1 vote
1 answer
56 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 ...
user avatar
2 votes
1 answer
7k 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 ...
user650654's user avatar
2 votes
1 answer
2k 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 ...
Wiiliam Bonney's user avatar
0 votes
1 answer
537 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 ...
Marek Marhoul's user avatar
1 vote
1 answer
81 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 ...
D-Ly's user avatar
  • 61
0 votes
1 answer
208 views

Show blank cell result if a cell is empty

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 formula is blank?
user avatar
0 votes
1 answer
68 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. ...
Lou's user avatar
  • 225
2 votes
1 answer
68 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 ...
littlecoder's user avatar
1 vote
1 answer
25 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. ...
Chain EvroSmak's user avatar
1 vote
1 answer
241 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 ...
Ethan Chapman's user avatar
1 vote
1 answer
257 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♥ ...
where_theres_a's user avatar
4 votes
3 answers
4k 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 ...
skytreader's user avatar
1 vote
2 answers
50 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 ...
Ronald McDonald's user avatar
1 vote
1 answer
32 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. ...
Nate.M-C's user avatar
0 votes
1 answer
2k 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 ...
pos18's user avatar
  • 3
1 vote
2 answers
1k 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 ...
John Henckel's user avatar
0 votes
2 answers
21 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?
Nathan Johnson's user avatar
-1 votes
2 answers
288 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 | ...
Dave Stein's user avatar
0 votes
5 answers
72 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 ...
Joel Reid's user avatar
  • 690
1 vote
1 answer
1k 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 ...
Conor's user avatar
  • 11
-1 votes
1 answer
24 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
user2680792's user avatar
-1 votes
1 answer
25 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 ...
MainelyTom's user avatar
-1 votes
1 answer
132 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?
BntMrx's user avatar
  • 99
1 vote
2 answers
569 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 ...
Marek Marhoul's user avatar
4 votes
4 answers
890 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 ...
Steven D.'s user avatar
0 votes
1 answer
4k 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,...
Anil Banatwala's user avatar
1 vote
1 answer
6k 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= ...
Ester Barias-Wolf's user avatar
1 vote
1 answer
531 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 ... ...
Joe's user avatar
  • 113
0 votes
5 answers
1k 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 ...
sancho.s ReinstateMonicaCellio's user avatar
1 vote
1 answer
125 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,...
Lutfi Creativesys's user avatar