Excel - Text before & after Function Tutorial

In this video, you will learn about Microsoft Search.
Microsoft Search allows you to quickly access features that can be hard to find, such as email signatures and comments.
It also helps you find new useful tools and work faster by directly accessing the features you want to use.
For example, you can check and remove duplicates from your documents in just a few clicks.
Additionally, you can find and open documents installed in your OneDrive or SharePoint and share them with your colleagues directly from the search bar.
This tutorial provides a concise and fluent overview of Microsoft Search, ensuring the best SEO visibility for your Microsoft 365 e-learning context.

4:57
2007 views

Available in Microsoft365

Objectifs :

This tutorial aims to introduce new string manipulation functions in Excel version 2208 and above, specifically focusing on the 'TEXTBEFORE' and 'TEXTAFTER' functions. It will guide users on how to extract specific parts of text based on delimiters, enhancing their data handling capabilities in Excel.

Chapitres :

Introduction to New Functions in Excel
Excel has introduced new functions for string manipulation in version 2208 and later. Users can verify their current version by navigating to 'File' > 'Account' and checking if they are using a Microsoft 365 app for business. The tutorial will focus on the 'TEXTBEFORE' and 'TEXTAFTER' functions, which allow users to extract portions of text based on specified delimiters.
Accessing the Functions
To access the 'TEXTBEFORE' and 'TEXTAFTER' functions, users can either use the function assistant or directly type the function into a cell. The function assistant can be found under the 'Insert Function' option, categorized under 'Text'.
Using TEXTBEFORE Function
The 'TEXTBEFORE' function is used to extract text that appears before a specified delimiter. For example, to extract the seller's name from a full string, select the cell containing the full string and specify a space as the delimiter. This will yield the first name of the seller.
Extracting Text Before the Second Delimiter
To extract the name before the second space, users can manually enter the formula for 'TEXTBEFORE'. By specifying the instance number as 2, Excel will extract the text before the second space. This method can be useful for separating names and surnames.
Using TEXTAFTER Function
The 'TEXTAFTER' function is useful for extracting text that appears after a specified delimiter. For instance, to extract the country from a full address, users can specify the space as the delimiter. To simplify the process, a negative instance number can be used to count spaces from the end of the string.
Combining Functions
Users can combine both 'TEXTBEFORE' and 'TEXTAFTER' functions in a single cell to extract multiple pieces of information. For example, to extract the forename, serial number, and country, the 'TEXTAFTER' function can be used first, followed by 'TEXTBEFORE' to gather the full name.
Extracting Serial Numbers
To extract serial numbers from a string, the 'TEXTAFTER' function can be employed to capture all numbers following a specific delimiter. The 'TEXTBEFORE' function can then be used to stop extraction at a designated space separator.
Optional Parameters of the Functions
Both 'TEXTBEFORE' and 'TEXTAFTER' functions come with optional parameters. The 'match mode' parameter allows users to specify case sensitivity. By default, the delimiter match is case sensitive, but this can be changed by entering 0 (case insensitive) or 1 (case sensitive). Additionally, the 'if not found' argument lets users define what should be displayed if the formula does not find the specified text.
Conclusion
The new 'TEXTBEFORE' and 'TEXTAFTER' functions in Excel provide powerful tools for text manipulation. By understanding how to use these functions effectively, users can enhance their data processing skills and streamline their workflows. For further assistance, users are encouraged to explore the help option within Excel for more detailed explanations.

FAQ :

What are the new functions available in Excel version 2208?

The new functions available in Excel version 2208 include Text Before and Text After, which help extract parts of a string based on specified delimiters.

How can I check my current version of Excel?

To check your current version of Excel, go to the File menu, select Account, and look for the version information under the About section.

What is a delimiter in Excel?

A delimiter in Excel is a character that separates data elements in a string, such as a space, comma, or other specified character.

How do I use the Text Before function?

To use the Text Before function, specify the cell containing the full string and the delimiter. The function will return all text before the specified delimiter.

Can I use multiple delimiters with the Text Before and Text After functions?

Yes, you can specify which occurrence of the delimiter to use by providing the Instance NUM argument in the functions.

What does the Match Mode parameter do?

The Match Mode parameter determines whether the delimiter matching is case sensitive. You can set it to 0 for case insensitive or 1 for case sensitive.

What happens if the delimiter is not found?

If the delimiter is not found, the If Not Found argument allows you to specify a word or value to display in the cell instead.

Quelques cas d'usages :

Extracting Seller Names from Full Strings

In a sales database, you can use the Text Before function to extract seller names from full strings that include last names and countries, improving data organization and reporting.

Parsing Addresses for Data Analysis

When analyzing customer addresses, the Text After function can be used to extract specific components, such as the country or city, from a full address string, facilitating better data segmentation.

Generating Reports with Serial Numbers

In inventory management, you can use the Text After function to extract serial numbers from product descriptions, streamlining the reporting process and ensuring accurate tracking.

Cleaning Up Data for Import

When preparing data for import into another system, you can use the Text Before and Text After functions to clean up and format strings, ensuring compatibility and reducing errors during the import process.

Creating Dynamic Dashboards

In business intelligence, using the Text Before and Text After functions can help create dynamic dashboards that display relevant metrics by extracting specific data points from larger datasets.

Glossaire :

Excel

A spreadsheet program developed by Microsoft that allows users to organize, format, and calculate data with formulas.

Version 2208

A specific release of Microsoft Excel that includes new functions for string manipulation.

Delimiter

A character or sequence of characters that separates data elements in a string, such as a space, comma, or semicolon.

Text Before

An Excel function that extracts all text from a string that appears before a specified delimiter.

Text After

An Excel function that extracts all text from a string that appears after a specified delimiter.

Instance NUM

An argument in the Text Before and Text After functions that specifies which occurrence of the delimiter to consider.

Match Mode

An optional parameter in the Text Before and Text After functions that determines whether the delimiter matching is case sensitive.

If Not Found Argument

An optional parameter that specifies what to display in the cell if the specified delimiter is not found in the text.

00:00:00

New functions are now available

00:00:01

in Excel if you have the

00:00:04

2208 version to treat strings.

00:00:06

To verify your current version of Excel,

00:00:08

you need to go to the option file.

00:00:12

Account. And check if you are on

00:00:17

a Microsoft 365 app for business

00:00:19

and check on the about option

00:00:21

the version of your Excel.

00:00:26

Check if the version is at least 2208.

00:00:28

In my case I got the 2210.

00:00:35

The function we will talk about in this

00:00:38

tutorial is the text after and before.

00:00:40

Two functions that can help you

00:00:42

extract a part of any text before,

00:00:45

between and after a delimiter.

00:00:47

To access these function

00:00:50

you can go to the assistant here or

00:00:52

you can directly write the function.

00:00:55

In the cell.

00:00:57

For my first example I will go to the

00:01:00

insert function or assistant option.

00:01:03

Then go to the category

00:01:05

called text to find it.

00:01:07

To use the text before,

00:01:09

I want to extract the name of the

00:01:12

seller before the space separator.

00:01:14

Knowing the name also has the

00:01:15

last name and the country.

00:01:19

So I will click on the cell that contains

00:01:22

the full string and specify the delimiter.

00:01:25

In my case I will put a

00:01:29

space between quotes and that's all.

00:01:30

I have extracted the name of the cell.

00:01:32

For the second example I will enter the

00:01:37

formula manually for text before.

00:01:40

And I will extract the

00:01:42

name before the 2nd space.

00:01:44

I have to click on the cell

00:01:46

and specify the delimiter.

00:01:51

I will also specify that I

00:01:53

want to extract all the text

00:01:55

before the 2nd delimiter.

00:01:57

This is called instance NUM

00:01:59

or second delimiter space.

00:02:03

But I can't extract the name and last

00:02:06

name directly to extract the country.

00:02:08

So I will use the text after formula.

00:02:11

I will give you a tip because here it

00:02:13

becomes more difficult as I have to

00:02:15

count how many spaces there are between

00:02:17

the country and the rest of the street.

00:02:20

I have to specify the delimiter

00:02:22

and the space. To avoid all this

00:02:24

I will put a negative number.

00:02:27

This option allows you to tell

00:02:29

itself that you want the exact

00:02:31

space before the last word,

00:02:32

and what Excel does is to count the

00:02:35

space from the end to the start

00:02:37

depending on the chosen formula.

00:02:39

So now you can extract the

00:02:41

country to extract the forename.

00:02:44

I will use 2 formulas in one cell.

00:02:50

I will begin with text after.

00:02:54

So I specify the delimiter

00:02:56

space and I also tell its cell

00:02:58

that I want to extract it all

00:03:00

after the first delimiter

00:03:02

I extract the forename,

00:03:05

the serial number and the country.

00:03:08

Now I will use

00:03:12

the text before formula to extract it all.

00:03:21

And now I've extracted the full

00:03:24

name for the serial number.

00:03:26

It is nearly

00:03:27

the same thing

00:03:30

but with another delimiter.

00:03:32

I will extract all the numbers after the

00:03:35

delimiter of my cell with a text after.

00:03:38

So in this case the name ID to stop

00:03:42

the extraction and keep only the

00:03:44

serial number . So I use the text before

00:03:47

as usual and specify to stop the

00:03:50

extraction at the space separator.

00:03:52

So if I extract the serial

00:03:54

number of the seller.

00:03:56

These two formulas have optional parameters.

00:03:58

Let's see what they are.

00:04:06

First of all, the match mode.

00:04:09

By default the delimiter

00:04:11

match is case sensitive.

00:04:13

If you don't want your formula

00:04:14

to be case sensitive, you have to

00:04:16

disactivate the case sensitive option.

00:04:18

By typing zero or one in the match mode.

00:04:22

If you want more information,

00:04:24

I invite you to click on the help option

00:04:26

area to get a deeper explanation.

00:04:36

The match argument allows you to

00:04:38

specify if the end of the text will

00:04:40

be considered or not as a delimiter.

00:04:45

The if not found argument allows

00:04:49

you to specify which word will be

00:04:52

shown in your cell if the formula.

00:04:54

Or if there is an error produced.

00:00:00

Novas funções já estão disponíveis

00:00:01

no Excel, se tiver a seringa

00:00:04

Versão 2208 para tratar cordas.

00:00:06

Para verificar a sua versão atual do Excel,

00:00:08

você precisa ir para o arquivo de opção.

00:00:12

Conta. E verifique se você está ligado

00:00:17

um aplicativo Microsoft 365 para empresas

00:00:19

e marque a opção sobre

00:00:21

a versão do seu Excel.

00:00:26

Verifique se a versão é pelo menos 2208.

00:00:28

No meu caso eu tenho o 2210.

00:00:35

A função sobre a qual falaremos neste

00:00:38

tutorial é o texto depois e antes.

00:00:40

Duas funções que podem ajudá-lo

00:00:42

extrair uma parte de qualquer texto antes,

00:00:45

entre e depois de um delimitador.

00:00:47

Para aceder a estas funções

00:00:50

Pode dirigir-se ao assistente aqui ou

00:00:52

Você pode escrever diretamente a função.

00:00:55

Na célula.

00:00:57

Para o meu primeiro exemplo, irei ao

00:01:00

Inserir função ou opção de assistente.

00:01:03

Em seguida, vá para a categoria

00:01:05

chamou texto para encontrá-lo.

00:01:07

Para usar o texto antes,

00:01:09

Quero extrair o nome do

00:01:12

vendedor antes do separador de espaço.

00:01:14

Conhecer o nome também tem o

00:01:15

apelido e país.

00:01:19

Então vou clicar na célula que contém

00:01:22

a cadeia de caracteres completa e especifique o delimitador.

00:01:25

No meu caso vou colocar um

00:01:29

espaço entre aspas e isso é tudo.

00:01:30

Eu extraí o nome da célula.

00:01:32

Para o segundo exemplo, vou inserir o

00:01:37

fórmula manual para o texto anterior.

00:01:40

E vou extrair o

00:01:42

nome antes do 2º espaço.

00:01:44

Tenho que clicar no celular

00:01:46

e especificar o delimitador.

00:01:51

Especificarei também que

00:01:53

deseja extrair todo o texto

00:01:55

antes do 2º delimitador.

00:01:57

Isso é chamado de instância NUM

00:01:59

ou segundo espaço delimitador.

00:02:03

Mas não consigo extrair o nome e por último

00:02:06

nome diretamente para extrair o país.

00:02:08

Por isso, vou usar o texto após a fórmula.

00:02:11

Vou te dar uma dica porque aqui ele

00:02:13

torna-se mais difícil como eu tenho que

00:02:15

contar quantos espaços existem entre

00:02:17

o país e o resto da rua.

00:02:20

Tenho que especificar o delimitador

00:02:22

e o espaço. Para evitar tudo isso

00:02:24

Vou colocar um número negativo.

00:02:27

Esta opção permite-lhe dizer

00:02:29

que você quer o exato

00:02:31

espaço antes da última palavra,

00:02:32

e o que o Excel faz é contar o

00:02:35

espaço do fim ao início

00:02:37

dependendo da fórmula escolhida.

00:02:39

Então agora você pode extrair o

00:02:41

país para extrair o nome próprio.

00:02:44

Vou usar 2 fórmulas em uma célula.

00:02:50

Começarei com o texto depois.

00:02:54

Então eu especifico o delimitador

00:02:56

espaço e eu também digo a sua célula

00:02:58

que eu quero extrair tudo

00:03:00

após o primeiro delimitador

00:03:02

Eu extraio o nome próprio,

00:03:05

o número de série e o país.

00:03:08

Agora vou usar

00:03:12

o texto antes da fórmula para extrair tudo.

00:03:21

E agora eu extraí o completo

00:03:24

nome para o número de série.

00:03:26

É quase

00:03:27

a mesma coisa

00:03:30

mas com outro delimitador.

00:03:32

Vou extrair todos os números após o

00:03:35

delimitador da minha célula com um texto depois.

00:03:38

Então, neste caso, o ID do nome para parar

00:03:42

a extração e conservar apenas a seringa

00:03:44

número de série . Então eu uso o texto antes

00:03:47

como de costume e especificar para parar a

00:03:50

extração no separador de espaço.

00:03:52

Então, se eu extrair o serial

00:03:54

número do vendedor.

00:03:56

Estas duas fórmulas têm parâmetros opcionais.

00:03:58

Vamos ver quais são.

00:04:06

Em primeiro lugar, o modo de jogo.

00:04:09

Por padrão, o delimitador

00:04:11

A correspondência diferencia maiúsculas de minúsculas.

00:04:13

Se não quiser a sua fórmula

00:04:14

Para diferenciar maiúsculas de minúsculas, tem de

00:04:16

Desative a opção sensível a maiúsculas e minúsculas.

00:04:18

Digitando zero ou um no modo de correspondência.

00:04:22

Se você quiser mais informações,

00:04:24

Convido-o a clicar na opção de ajuda

00:04:26

para obter uma explicação mais profunda.

00:04:36

O argumento match permite-lhe:

00:04:38

especificar se o final do texto

00:04:40

ser considerado ou não como delimitador.

00:04:45

O argumento if not found permite

00:04:49

você para especificar qual palavra será

00:04:52

mostrada na sua célula se a fórmula.

00:04:54

Ou se houver um erro produzido.

Mandarine AI: CE QUI POURRAIT VOUS INTÉRESSER

RELATED COURSES This tutorial is part of the following courses

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

Cours E-learning

ALL COURSES