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 &.
91
questions
-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 ...
- 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 ...
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 ...
- 11
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 ...
- 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
...
- 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 ...
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 ...
- 13
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 ...
- 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 ...
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 ...
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(...
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 * ...
- 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 | ...
- 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 ...
- 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 ...
- 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 ...
- 425
-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 ...
- 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 ...
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": ...
- 1
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
(...
- 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 ...
Soulja Boy
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 ...
- 121
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 ...
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 ...
- 183
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 ...
- 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?
Michelle Gray
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. ...
- 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 ...
- 172
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.
...
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 ...
- 113
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♥ ...
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 ...
- 151
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 ...
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. ...
- 37
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 ...
- 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 ...
- 201
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?
-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 | ...
- 99
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 ...
- 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 ...
- 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
-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 ...
-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?
- 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 ...
- 183
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 ...
- 51
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,...
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=
...
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 ... ...
- 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 ...
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,...
