Excel - TEXTJOIN Tutorial

In this video, you will learn about how to search and find what you need using Microsoft 365. The video covers the usage of OneDrive, Yammer, and Delve in the Office 365 subscription to help you access and organize your documents, collaborate with colleagues, and discover relevant information.
This tutorial will enhance your understanding of these tools and enable you to efficiently search for and find the latest updates and publications.
By mastering these techniques, you will be able to work more effectively and save time in your daily tasks.

0:47
3738 views

Available in Microsoft365

Objectifs :

Understand how to use the TEXTJOIN function in Excel to combine text strings with specified delimiters, while ignoring empty cells.

Chapitres :

Introduction to TEXTJOIN Function
The TEXTJOIN function in Excel is a powerful tool that allows users to combine multiple text strings into a single string. This function is particularly useful when you want to create a list of items, such as email addresses, with a specific delimiter separating each item.
Key Features of TEXTJOIN
The TEXTJOIN function has several key features that enhance its usability: - **Combines Text Strings**: It allows you to merge a list or range of text strings into one. - **Custom Delimiters**: You can specify a delimiter (such as a comma, space, or any other character) that will be placed between each text value. - **Ignore Empty Cells**: The function can be set to ignore any empty cells in the range, ensuring that the final output is clean and free of unnecessary delimiters.
Using TEXTJOIN to Highlight Email Addresses
To illustrate the use of the TEXTJOIN function, consider a scenario where you want to compile a list of email addresses from a range of cells. Here’s how you can do it: 1. **Select the Delimiter**: Choose the character you want to use to separate the email addresses (e.g., a comma). 2. **Ignore Empty Cells**: Ensure that the function is set to ignore any empty cells in your selected range. 3. **Define the Text Range**: Specify the range of cells that contain the email addresses you want to combine. By following these steps, you can easily create a single string of email addresses, formatted neatly with your chosen delimiter.
Practical Example
For example, if you have the following email addresses in cells A1 to A5: - john@example.com - jane@example.com - (empty cell) - doe@example.com - (empty cell) Using the TEXTJOIN function with a comma as the delimiter and the option to ignore empty cells, the result would be: "john@example.com, jane@example.com, doe@example.com" This output is concise and free of any unnecessary commas from the empty cells.
Conclusion
The TEXTJOIN function is an essential tool for anyone looking to efficiently combine text strings in Excel. By understanding its features and how to apply it, users can streamline their data management tasks, particularly when dealing with lists of information such as email addresses. This function not only saves time but also enhances the clarity of the data presented.

FAQ :

What is the TEXTJOIN function used for in Excel?

The TEXTJOIN function is used to combine multiple text strings from a list or range into a single string, with a specified delimiter separating each value.

How do I specify a delimiter in the TEXTJOIN function?

You can specify a delimiter by including it as the second argument in the TEXTJOIN function. For example, using a comma as a delimiter would look like TEXTJOIN(",", TRUE, range).

Can TEXTJOIN ignore empty cells?

Yes, the TEXTJOIN function can be set to ignore empty cells by using the second argument as TRUE. This way, only non-empty cells will be included in the combined string.

What happens if I use a space as a delimiter in TEXTJOIN?

If you use a space as a delimiter in the TEXTJOIN function, the combined text will have spaces between each text value. For example, TEXTJOIN(" ", TRUE, range) will separate each value with a space.

Quelques cas d'usages :

Combining Email Addresses

In a corporate setting, you can use the TEXTJOIN function to combine a list of employee email addresses into a single cell for easy sharing. This is particularly useful when sending mass emails or creating contact lists.

Creating a Custom Report

When generating reports, you can use TEXTJOIN to compile comments or feedback from multiple sources into one cell, making it easier to review and analyze the information.

Data Cleanup

TEXTJOIN can be used to clean up data by combining related text entries from different cells into one, while ignoring any empty cells. This can streamline data presentation and improve readability.

Glossaire :

TEXTJOIN

A function in Excel that combines a list or range of text strings into a single string, using a specified delimiter between each text value.

delimiter

A character or string that separates text values in a combined string, such as a comma, space, or semicolon.

empty cells

Cells in a spreadsheet that do not contain any data or text. The TEXTJOIN function can be set to ignore these cells when combining text.

00:00:05

Die TEXTJOIN-Funktion kombiniert eine Liste oder einen Bereich von Textzeichenfolgen mit einem Trennzeichen zwischen jedem zu kombinierenden Textwert.

00:00:15

Hier möchten wir eine Liste der E-Mail-Adresse jeder Person hervorheben.

00:00:20

Mit der FUNKTION TEXTJOIN...

00:00:28

Ich wähle die Trennzeichen aus, die ich hier angegeben habe....

00:00:33

Leere Zellen ignorieren....

00:00:36

Und schließlich mein Textbereich....

00:00:05

La función TEXTJOIN combina una lista o un intervalo de cadenas de texto mediante un delimitador entre cada valor de texto que se va a combinar.

00:00:15

Aquí nos gustaría resaltar una lista de la dirección de correo electrónico de cada persona.

00:00:20

Con la función TEXTJOIN...

00:00:28

Selecciono los delimitadores que he especificado aquí....

00:00:33

Ignorar celdas vacías....

00:00:36

Y finalmente, mi rango de texto....

00:00:05

A função TEXTJOIN combina uma lista ou intervalo de cadeias de texto usando um delimitador entre cada valor de texto a ser combinado.

00:00:15

Aqui, gostaríamos de destacar uma lista do endereço de e-mail de cada pessoa.

00:00:20

Com a função TEXTJOIN...

00:00:28

Eu seleciono os delimitadores que eu especifiquei aqui....

00:00:33

Ignore as células vazias....

00:00:36

E, finalmente, a minha gama de texto....

00:00:05

De functie TEXTJOIN combineert een lijst of een reeks tekstreeksen met een scheidingsteken tussen elke tekstwaarde die moet worden gecombineerd.

00:00:15

Hier willen we een lijst van het e-mailadres van elke persoon markeren.

00:00:20

Met de functie TEXTJOIN...

00:00:28

Ik selecteer de scheidingstekens die ik hier heb opgegeven....

00:00:33

Lege cellen negeren....

00:00:36

En tot slot, mijn tekstbereik....

00:00:05

La funzione TEXTJOIN combina un elenco o un intervallo di stringhe di testo utilizzando un delimitatore tra ogni valore di testo da combinare.

00:00:15

Qui vorremmo evidenziare un elenco dell'indirizzo email di ogni persona.

00:00:20

Con la funzione TESTO. . .

00:00:28

Seleziono i delimitatori che ho specificato qui....

00:00:33

Ignora celle vuote...

00:00:36

E infine, la mia gamma di testo....

00:00:05

TEXTJOIN 函數使用要合併的每個文本值之間的分隔符號組合文本字串的清單或範圍。

00:00:15

在這裡，我們想突出顯示每個人的電子郵件地址的清單。

00:00:20

使用 TEXTJOIN 函數...

00:00:28

我選擇我在這裡指定的分隔符號...

00:00:33

忽略空儲存格...

00:00:36

最後，我的文本範圍...

00:00:05

TEXTJOIN 関数は、結合する各テキスト値の間に区切り文字を使用して、テキスト文字列のリストまたは範囲を組み合わせます。

00:00:15

ここでは、各ユーザーの電子メールアドレスの一覧を強調表示します。

00:00:20

TEXTJOIN 関数を使用すると...

00:00:28

ここで指定した区切り文字を選択します。

00:00:33

空のセルを無視します。

00:00:36

そして最後に、私のテキスト範囲...

00:00:05

Hàm TEXTJOIN kết hợp một danh sách hoặc phạm vi của chuỗi văn bản bằng cách sử dụng dấu phân tách giữa mỗi giá trị văn bản được kết hợp.

00:00:15

Ở đây chúng tôi muốn làm nổi bật một danh sách các địa chỉ email của mỗi người.

00:00:20

Với chức năng TEXTJOIN...

00:00:28

Tôi chọn các dấu phân cách tôi đã chỉ định ở đây....

00:00:33

Bỏ qua các ô trống....

00:00:36

Và cuối cùng, phạm vi văn bản của tôi....

00:00:05

Fungsi TEXTJOIN menggabungkan daftar atau rentang string teks menggunakan pembatas antara setiap nilai teks yang akan digabungkan.

00:00:15

Di sini kami ingin menyoroti daftar alamat email setiap orang.

00:00:20

Dengan fungsi TEXTJOIN...

00:00:28

Saya pilih pembatas yang telah saya Tentukan di sini....

00:00:33

Abaikan sel kosong....

00:00:36

Dan akhirnya, rentang teks saya....

00:00:05

Функция TEXTJOIN сочетает в себе список или диапазон текстовых строк с использованием делимитр между каждым значением текста, которое должно быть объединено.

00:00:15

Здесь мы хотели бы выделить список адресов электронной почты каждого человека.

00:00:20

С функцией TEXTJOIN...

00:00:28

Я выбираю делимитеров я указал здесь ....

00:00:33

Игнорировать пустые ячейки ....

00:00:36

И, наконец, мой текстовый диапазон ....

00:00:05

Функцията TEXTJOIN комбинира списък или диапазон от текстови низове, като използва разделител между всяка текстова стойност, която да се комбинира.

00:00:15

Тук бихме искали да подчертаем списък с имейл адреса на всеки човек.

00:00:20

С функцията TEXTJOIN...

00:00:28

Избирам разделители, които съм посочил тук....

00:00:33

Игнорирай празните клетки....

00:00:36

И накрая, моя текст обхват....

00:00:05

Funcția TEXTJOIN combină o listă sau o zonă de șiruri de text utilizând un delimitator între fiecare valoare text de combinat.

00:00:15

Aici am dori să evidențiem o listă a adresei de e-mail a fiecărei persoane.

00:00:20

Cu funcția TEXTJOIN...

00:00:28

Am selectați delimitatori am specificat aici ....

00:00:33

Ignorare celule goale....

00:00:36

Și în cele din urmă, gama mea de text ....

00:00:05

Функція TEXTJOIN поєднує список або діапазон текстових рядків за допомогою роздільника між кожним текстовим значенням, яке потрібно об'єднати.

00:00:15

Тут ми хотіли б виділити список адрес електронної пошти кожної людини.

00:00:20

За допомогою функції TEXTJOIN...

00:00:28

Я вибив роздільники, які я вказав тут ....

00:00:33

Ігнорувати порожні клітинки....

00:00:36

І, нарешті, мій текстовий діапазон ....

00:00:05

Funkcja TEXTJOIN łączy listę lub zakres ciągów tekstowych przy użyciu ogranicznika między każdą wartością tekstową, która ma zostać połączona.

00:00:15

Tutaj chcielibyśmy wyróżnić listę adresu e-mail każdej osoby.

00:00:20

Z funkcją TEXTJOIN...

00:00:28

Wybieram ograniczniki, które tutaj określiłem....

00:00:33

Ignoruj puste komórki....

00:00:36

I wreszcie, mój zakres tekstu ....

00:00:05

TEXTJOIN işlevi, birleştirilmesi gereken her metin değeri arasında bir sınırlayıcı kullanarak bir liste veya metin dizeleri aralığını birleştirir.

00:00:15

Burada her kişinin e-posta adresinin bir listesini vurgulamak istiyoruz.

00:00:20

TEXTJOIN fonksiyonu ile...

00:00:28

Ben burada belirttiğim delimiters seçin ....

00:00:33

Boş hücreleri yoksay....

00:00:36

Ve son olarak, benim metin aralığı ....

00:00:05

A função TEXTJOIN combina uma lista ou gama de cadeias de texto utilizando um delimiter entre cada valor de texto a combinar.

00:00:15

Aqui gostaríamos de destacar uma lista do endereço de e-mail de cada pessoa.

00:00:20

Com a função TEXTJOIN...

00:00:28

Eu seleciono os delimiters que especifico aqui....

00:00:33

Ignore as células vazias...

00:00:36

E finalmente, o meu alcance de texto...

00:00:05

Funkcija TEXTJOIN kombinuje listu ili opseg tekstualnih niska pomoću razgraničavača između svake tekstualne vrednosti za kombinovanje.

00:00:15

Ovde želimo da istaknemo listu e-adrese svake osobe.

00:00:20

Sa funkcijom TEXTJOIN...

00:00:28

Biram znake razgraničenja koje sam ovde naveo...

00:00:33

Ignoriši prazne ćelije...

00:00:36

I na kraju, moj domet teksta...

00:00:15

ที่นี่เราต้องการเน้นรายการที่อยู่อีเมลของแต่ละคน

00:00:20

ด้วยฟังก์ชัน TEXTJOIN...

00:00:28

ฉันเลือกตัวคั่นที่ฉันระบุที่นี่...

00:00:33

ละเว้นเซลล์ว่าง......

00:00:36

และสุดท้าย ช่วงข้อความของฉัน...

00:00:05

A TEXTJOIN függvény egyesíti a szövegkarakterláncok listáját vagy tartományát az egyes kombinálandó szöveges értékek közötti elválasztóval.

00:00:15

Itt szeretnénk kiemelni az egyes személyek e-mail címének listáját.

00:00:20

A TEXTJOIN funkcióval...

00:00:28

Kiválasztom az itt megadott elhatárolókat...

00:00:33

Üres cellák figyelmen kívül hagyása...

00:00:36

És végül, a szövegtartományom...

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2435 Views

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Po...

ALL COURSES