Excel has several functions that let you tear apart text and put it together again. Individually, these functions may seem like "play" functions, but when combined with other functions, they can provide a very useful tool to the developer.

Length Function

The Length function provides you the ability to determine how many characters are in a cell containing data. For example, the cell containing the word TROY has 4 characters. The function looks like this:

=LEN(Cell Address)

Some examples might be: =Len(B4) or = LEN(m15)

The formulas displayed in Column D were entered in Column C.

 

The results are displayed for your viewing.

 

Numbers in cells are treated slightly different. Look at this table and see what characters are NOT counted.

It would appear that all of the keystrokes used in numbers are counted, but not some of the formatting features you can use to display the number.

The comma separator and the $ used to denote money are not counted with the LEN function. What would you suspect about the % sign?

Once data is entered in a cell, you have the ability to determine the number of characters in the text with the LEN function.

Left and Right Functions

You can "strip off" characters from the left and right ends of a string of text. For example, =Left("Troy",2) would give you "Tr" and =Right("Troy",1) would give you "y".

Left Function	Right function

Mid Function

If there is a left and right function, you know in your heart there has to be a middle!

The format of the MID function is:

=(MID,Cell Address,starting with,how many)

In this example, =mid("University",4,3) you would begin with the 4th character from the left (the "v") and capture 3 characters (the "ver").

Using the MID function on Text	Using the MID function on numbers

It is relatively easy to see that you have the power to look at the contents of a cell and disect it with some combination of the Len, Left, Right, or Mid functions. There's more you can do. You can add two cells together to make a new cell.

Concatenate Text

Concatenate is a long name that basically you can take two strings of text characters and join them end to end. (Troy + State = TroyState) The process is fairly simple. You just have to remember to use the & key.

Comments:

• In the first example, tombill is accomplished by joining A2 and B2
• In the second example, A2 is joined with a blank space and that is joined with B2
• The third example is like the first, only the order of the joining is different.
• The last example used the concatenate function. This lets you join the elements in order if you separate them with commas.

A good question to ask is when might you ever use the concatenate or & in real life? The most common place I have used the & to join two cells has been when someone has carefully planned ahead and split names into two parts to facilitate creating an alphabetized listing. But later they still needed the full names in separate cells.

If there was a need for a column of full names, you certainly wouldn't want to copy all of the names again! You simply concatenate the top two cells with =a2 & b2 and copy this formula down with a fill-down. Certainly saves typing and having to correct typing erors (see!)

Repeat

The repeat function is a quick way to request Excel to repeat a string of text a number of times. The format for the function is:

=REPT(Text, # of times to repeat)

The examples above pretty much tell the story of the repeat function. The more interesting part is in deciding how to take advantage of the features in any meaningful way. Let's take a look at some examples of how you might use all of the Text Functions we have explored.

Suppose you wanted to build your own Bar Graph rather than use the graphic capabilities of Excel.

The Repeat Function added to the Concatenate process lets you create a nice horizontal graph. If you add text color, you can have an interesting graph and never use the power of Excel's graphing wizard.

This next example requires a little setup story. In preparation for some other work, you have to know if the data entry person has accidentally added a space bar character at the end of any of the names in a long list. Since the spacebar character is not visible, you will have some difficulty checking without some help from Excel.

There are always more than one way to solve a problem. Take a look at these three methods...some we think are better than others.

The Length function is OK. You basically have to scroll down the list and see if the number of letters in each name in Column B matches the value in Column C. Not too bad, but counting will slow you down.

The combination of the Left and Len functions will probably be easier to "eye-ball" the column. The process basically will display all but the last character entered. We think it would be easier to see where two cells match than counting characters in a name.

The third solution seems (to us) to be the best. We use the Right function to display the "right-most" character. It is pretty easy to spot a blank cell as you look down the column. By any of the methods, you can monitor the typing and quickly edit the cells having the unwanted blank space.

Return