The 2 Most Important

Excel Functions AND
Formulas

SUMPRODUCT

AND

INDEX/MATCH

Lesson 10: Excel Spreadsheets Text Functions (24)

In annex 10 you have found a description of all 24 Excel functions in the "Text" category. Below is the list of the 9 most useful ones.

Tips

To Concatenate: To assemble strings of text. When you concatenate the result is always in text format even if your are concatenating numbers.

For example: if you have 1 in cell A1 and 2 in cell A2 the formula =A1+A2 will return 3. If instead of the plus sign (+) you use the ampersand (& ) the formula =A1 & A2 will return 13 because concatenating is not adding it is creating a chain of characters with the content of many cells. The result 13 is not even a number with which you could execute calculations it is a text just like Peter.

The TEXT functions in Excel are great "Time Saving" tools. When you have data that you receive from colleagues, clients or  suppliers, when you download data from a database or the Internet and the format is not right for you, you need to RE-ENTER the data manually and this task is time consuming, error prone and very frustrating. The TEXT functions will allow you to do the reformatting automatically.

I have developed hundreds of spreadsheets to convert data and make them usable within Excel. I have also developed spreadsheets to convert large quantities of Excel data into a format uploadable in large databases (Oracle, Sybase, SQL Server...) or ERP systems (JDEdwards, SAP, PeopleSoft, SmartStream...) as batch files.

Excel is a great translator to move data from one system to the other. You download data from system A, convert and either use it in Excel or  upload in system B.

Basic Excel Formulas using TEXT Functions

CONCATENATE and the ampersand (& )
If you have "Peter" in cell A1 and "Clark" in cell B1 the following formula in cell C1 will return "Peter Clark" :
=CONCATENATE(A1," " ,B1)
With this formula you are telling Excel to assemble the content of cell A1, a space (between quotes) and the content of cell C1.
a simpler way to get the same result:
=A1 & " " & B1
The ampersand (& ) is the sign used to tell Excel to concatenate strings of text. Most users prefer the ampersand to the CONCATENATE function.

FIND or SEARCH
With " Peter Clark" in cell A1 the formula =FIND(" " ,A1) will return 6 because the space is the sixth character from the left. This function is very useful to remove parts of a string of characters when there is a constant within it. FIND and SEARCH perform the same task but FIND is case sensitive and  SEARCH is not.

LEFT, RIGHT, MID
If you have Peter Clark in cell A1 these formulas in cell B1 to B3:
=RIGHT(A1,2) will return "rk"
=RIGHT(A1,5) will return " Clark"
=LEFT(A1,2) will return "Pe"
=LEFT(A1,5) will return "Peter"
=MID(A1,7,3) will return "Cla" because you are asking Excel to extract 3 characters starting with the seventh from the left.

LEN
The function LEN returns the number of characters in a string. Like many functions of the TEXT category LEN is a function that is rarely used by itself The basic LEN formula looks like this:
=LEN(A1)
If cell A1 contains "Peter" the answer will be 5, with "Peter Clark" the answer is 11 because the space is a character

REPT
The REPT function is indispensable when you want to upload a series a values that are in different columns in Excel to an old database or to an A/S400 database. These databases and certain other accounting programs have fixed width fields. For example the " amount" field can be 10 characters wide so even if the amount that you have is 3.35 (In cell A1) you need to upload 0000000335
=REPT(0,8) & A1 will return 0000000335

TEXT
I use this function to make sure that Excel sees a string of characters and not a number. If you have 3567 in cell A4,
=TEXT(A4,"@") will return 3567 and you know that it works because the string although looking like a number is aligned to the left of the cell. This function is particularly important when working with numerical part numbers or account numbers  specially with SUMPRODUCT  and INDEX/MATCH.

TRIM
Sometimes when you download data from certain databases you have in cell A1 either "Peter     Clark" with five spaces between Peter and Clark or "Peter Clark     " with 5 spaces at the end of the name or "     Peter Clark" with 5 spaces at the beginning, =TRIM(A1) will return the same result "Peter Clark" with no space at the beginning or the end and a single space in between. The TRIM function only removes what Excel considers as useless spaces.

VALUE
Sometimes when you download data from certain databases the numbers are in text format and you cannot use them in calculations. You will use the following formula to resolve this problem:
=VALUE(A1)

Formulas Using Many Functions

1 - The surname is in cell A1, the first name is in cell B1 and in cell C1 you want both of them separated by a comma and a space. The formula in cell C1 is:
=A1 & "," & B1

2 - You download data from a database and what you have in cell A1 "Peter     " with five spaces  at the end and in B1 "Clark     " with five spaces at the end. What you want in C1 is "Peter Clark" . The formula in C1 is:
=TRIM(A1) & " " & TRIM(B1)

3 - In cell A1 you have a serial number (SKU). The SKU is built like this: a letter then 3 digits for the style, three digits for the color and three digits for the print. For example A305888765 means product "A" with style number "305" , color "888" and print "765" . In cell B1 you just want the color. The formula in B1 will look like this:
=RIGHT(LEFT(A1, 7) 3)