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.

Learn more about TEXTJOIN function.

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

## Download the sample file

**Click here to download the sample file** and practice these functions.

## Important Text Functions in Excel Video

If you want a video guide that explains these functions in detail, check it out below or *find* it on my YouTube channel.

## Need a text function? Tell me in comments

Are you trying to make formula to get something done with text in Excel? Let me know in comments what you need and I will try to help.

Also, if you have a favorite Excel text formula trick or patterns, share it so we all learn from each other.

The post 6 Essential TEXT functions in Excel with 6 Everyday Examples appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: http://feedproxy.google.com/~r/PointyHairedDilbert/~3/RU4Q-1aWOc8/