Play with Words in Excel

In Excel you can play with Words. That is quite funny actually.

You can add words, cut them mix them. Here some basics:

The main function are called:

CONCATENATE: which puts words together. Here you type CONCATENATE(cell1, cell2, cell3, "another word", cell4, ....)

LEFT: takes n characters from the Left of the word or sentence. LEFT(cell including the string, a number)

RIGHT: takes n characters from the Right of the word or sentence. RIGHT(cell including the string, a number)

MID: returns a specific number of characters from a word, sentence or text string, starting at the position you specify, based on the number of characters you specify. MID(cell including the string, number representing the first letter you want, and then the lastone).

Here an Exemple:

concatenate

 

 

Number of word, Counting word or line or non/empty cells

This is a so important function that I had to put it here in the basics although it is not so basic.

Sometimes you have a long list of items or name and you want to know how many are non empty or repeat themselves.

Here how to:

Take this list of names. We want to know how many are in total inside and how many ROGER there are.

The function COUNTIF is used here. This COUNTIF function allows you to enter a condition to the counting.

Here we look for the word "roger". So it become COUNTIF(B2:B15, "roger").

The COUNTA function counts the number of NON-EMPTY cells in an area.

range counta

REMARK:

Look at the formulas. The first one shows the range B:B which means the full B column.

The second one show a specific range B2:B15.

You can use both ways here but not always. If you have a header, using B:B will include the header so you should use B2:Bxxx

 

Let's do some magic trick, sorry Excel Trick.

Lets count how many unique people are in a list

Here we use the function COUNTIF 3 times because we know that one name will not appear more than 3 times.

We could use a pivot table too to count the number of occurence of a word or number.

unique people count

 

 

These were some basics. Now you will learn by doing.