## 6 Essential TEXT functions in Excel with 6 Everyday Examples

Excel text functions are useful for cleaning up text / alphanumeric values, extracting parts of cell and presenting combined results in output pages. In this article, learn the most important TEXT Functions in Excel with 6 everyday examples.

## The 6 Important Text Functions

### LEFT Function

Use LEFT function to extract portion of text from left.

Examples:

=LEFT(“Chandoo”, 2) will be Ch
=LEFT(A1, 4) will be first 4 letters of A1 value

### RIGHT Function

Use RIGHT function to extract portion of text from right.

Examples:

=RIGHT(“Chandoo”, 2) will be oo
=RIGHT(LEFT(A1, 4),2) will be two letters from the middle, starting from 3rd letter of A1.

### MID Function

Use MID function to extract portion of text from middle, from a specified starting point.

Examples:

=MID(“Chandoo”, 5, 2) will be do
=MID(“Chandoo”, 4, 99) will be doo

### LEN Function

LEN function measures the length of a text in number of characters.

Examples:

=LEN(“Chandoo”) will be 7
=LEN(A1) will be the length of contents in A1. If A1 is empty, this will be 0.

### FIND Function

Find the starting position of a text in another text using FIND function.

Examples:

=FIND(“do”, “Chandoo”) will be 5

=FIND(“DO”, “Chandoo”) will be error as find is a case-sensitive function

=SEARCH(“DO”, “Chandoo”) will be 5.

### TEXTJOIN Function

Combine (concatenate) a bunch of values with a specified delimiter.

Examples:

=TEXTJOIN(“,”,FALSE, “Chandoo”,”Jon”,”Mike”) will be Chandoo,Jon,Mike

=TEXTJOIN(” “, TRUE, A1:A10) will combine all non-empty values  in range A1:A10 with space as delimiter.

## The 6 Everyday Examples

Now that you know the 6 important functions, let’s see them applied in 6 everyday situations.

For the purpose of these examples, we will use below sample tabular data & structural references.

### 1) Gender code (M for male, F for female)

Use the formula =LEFT([@Gender], 1) to get make the gender letter code.

### 2) Extract first name from name

Use the formula =LEFT([@Name],FIND(” “,[@Name])-1) to get the first name.

FIND gets the position of space, left gets everything before that.

### 3) Extract last name from name

Try the formula =MID([@Name],FIND(” “,[@Name])+1,99) to get the last name.

FIND gets the position of space, mid  gets everything after that.

### 4) Print name in Last name, First name format

The formula =MID([@Name],
FIND(” “,[@Name])+1,99)
&”, “&LEFT([@Name],FIND(” “,[@Name])-1)
will convert value in Name column to last name, first name format.

It is a combination of the formulas shown in 2 & 3.

### 5) Combine all male staff names in to one cell

The formula =TEXTJOIN(“, “,TRUE, IF(staff[Gender]=”Male”, staff[Name],””))
will return all male staff names in the table staff.

The IF formula generates a list of all male names or blanks. TEXTJOIN ignores the blanks (second parameter is TRUE) and combines the values with a comma as separator.

### 6) Word count of a sentence

Assuming you have sentence in cell D6, the formula

=LEN(D6)-LEN(SUBSTITUTE(D6,” “,””))+1

will tell you its word count.

The SUBSTITUTE formula removes any spaces (by subbing them with nothing) and LEN is used to measure the length.