Stack Overflow
Stack Overflow

Questions tagged [textjoin]

Use the TEXTJOIN tag for questions that specify it in the question or could benefit from its use in a proposed solution.

Similar to the CONCAT or CONCATENATE worksheet functions, TEXTJOIN allows several cell values and/or string constants to be 'strung together'. TEXTJOIN has an additional parameter for a single delimiter to be used between all concatenations and a boolean option to discard potentially blank cells within a range of text values.

The TEXTJOIN was introduced with Excel 2016 in the following versions:

  • Excel for Android phones
  • Excel Mobile
  • Excel 2016 with Office 365
  • Excel 2016 for Mac
  • Excel Online
  • Excel for iPad
  • Excel for iPhone
  • Excel for Android tablet

It is currently not available within the standard desktop version.

Official support.office.com documentation page at TEXTJOIN function.

337 questions
2
votes
1 answer

TEXTJOIN of INDEX MATCH unique values if value on another table is contained in a cell

On cell B5 I'm trying to get a TEXTJOIN with delimiter "," of INDEX MATCH to the price range you see on Table B. Because cell A5 contains "Apple" then "$$$" is one of the values I need, also A5 contains "Banana" then "$$" is the second value i need.…
Kobe2424
  • 147
  • 7
2
votes
1 answer

How to see if cells contain multiple substrings

In Google Sheets (or Excel), I need to see if a column contains both of 2 substrings within 2 columns. For example: Column 1 might have: matt@gmail.com matt@yahoo.com doug@gmail.com gmail@matt.com Column 2 is 1 list of substrings: Row 1: matt Row…
Kassidy Webb
  • 23
  • 2
2
votes
1 answer

How to collapse sheet by pivoting rows into csv data

I have a set of data where an account id can have multiple rows of country. I'm looking for an array function that will give me a unique list of accounts with the countries in the second column as csv values e.g. country1,country1,country3. If I…
Chris Rosendin
  • 305
  • 2
  • 14
2
votes
2 answers

Row-wise textjoin of dynamic array with lookup

This question is closely related to this answer from user mark fitzpatrick. My sheet image I have a variable list of country code headers in M3# and AB3# (same list) and a variable list of unique "mentions" as rows in A4#. The formulas in M4# and…
dholt
  • 33
  • 7
2
votes
2 answers

USING TEXTJOIN AND UNIQUE OVER NON-CONTINUOUS CELLS

OK, relatively simple, but frustrating for me. I think my issue isn't with the TEXTJOIN, but in defining a non-continuous series of cells for the UNIQUE function. In cell A1, I am using this formula: =TEXTJOIN(" ",UNIQUE(B1,E1,H1,K1,N1)) NOTE: I am…
Rogue Scientist Allen
  • 109
  • 7
2
votes
2 answers

Google Sheets - Formula to Concatenate Text in Range of Cells based on Numeric Value in Corresponding Range of Cells

I have this setup in Google Sheets: Row 1 is a list of ingredients. Rows 2-6 are specific plans that contain combinations of ingredients A-F - if there is a 1 in the column, it contains the ingredient in the column. In column H, I want to…
Anthony
  • 61
  • 1
  • 5
2
votes
1 answer

How Can I Countunique from a compring 1 column eith 3 columns?

In this example I have tried to find matches between 1 column and 3 columns. It works. Now I want to count the hits BUT if for some reason there is an odd value it still counts. I have…
Allan Bech
  • 391
  • 1
  • 6
2
votes
1 answer

How could I form a string of items grouped by a matching value? (e.g. "Red: Apples, Cherries")

I'm wondering if there is a decent way to do this (without scripts) - if not, I can attempt creating a script for it but some users of this sheet will be using Excel on their computers so I'm trying to keep it scriptless as much as possible. I have…
Ali
  • 173
  • 1
  • 3
  • 13
2
votes
2 answers

Conditional formatting with named ranges

I'm trying to format table cells when the first row value and header column matches values from a named range in Google Sheets. I have solved it as a formula with TRUE and FALSE, but I can't adopt it for the conditional formatting. Example…
Andii
  • 484
  • 1
  • 4
  • 19
2
votes
4 answers

Detect new text blocks to generate functions in Google Sheets

I have imported a text file containing 3 blocks of text. I want to be able to recognize a new block of text, which is always separated by two lines. Then join the text in the rows for that text block and add some text between each line. Here is the…
Gracie
  • 896
  • 5
  • 14
  • 34
2
votes
1 answer

Nesting formulas not working as expected Google Sheets

I am trying to nest the following two formulas, both work. cell AO2 = Bob, cell AP2 = Judy =TEXTJOIN(\"|\", True, AO2, AP2) = Bob|Judy =SUBSTITUTE(ADDRESS(1,MATCH("*sumAppTags",Elements!A1:BB1,0),4),1,"") = Column Index AO If I try: =TEXTJOIN("|",…
xyz333
  • 679
  • 6
  • 14
2
votes
1 answer

Google sheets array formula to split and transpose cells with commas into another column

I'm looking for an arrayformula to split and transpose all comma-separated values into a single column. See my sheet here: https://docs.google.com/spreadsheets/d/1RYOlt2HmE1oC31nGhRtpU5mu3mg0gz7UK9-exMVKQlA/edit?usp=sharing
chappers
  • 466
  • 1
  • 6
  • 17
2
votes
2 answers

Is there any formula length limitation in Google Sheets?

I'm trying to create a data array from multiple cells in one cell and fail: My data is placed in cells from A3 to A3000. I try to paste into A1 the formula like =A3&"|"&A4&"|"&A...&"|"&A3000. The length of this formula is 29.345 characters - fewer…
Evgeniy
  • 2,337
  • 2
  • 28
  • 68
2
votes
1 answer

Highlight duplicate values between two or more ranges

I have a range of cells that I want to highlight duplicates in the same column. using =match(C$21,C$7:C$10,0) applied to the range C7:H10. Expected outcome: If there is a duplicate in the range it highlights the whole range of that column ie…
Allister
  • 23
  • 2
2
votes
1 answer

Google Sheets Query sum indeterminate number of columns

select sum(Col1),sum(Col2),sum(Col3) ... up to 500 columns but the number might increase or decrease... what is the best way to go about this with one formula? it would be really nice if you could just do select sum(*) ... but sadly that does not…
CodeCamper
  • 6,609
  • 6
  • 44
  • 94
1 2
3
…
22 23