Excel Downloads

Excel Online Consulting

Tips on Excel

Tips on VBA for Excel

Excel Index and Search Tool (Click on the buttons below)

Excel Lesson 29 de 30: Microsoft Excel Text Functions

Microsoft offers you 24 Excel functions in the Text category. Of them 10 are interesting and here they are. Below you will find tips on text functions, examples of basic formulas using a single function, examples of formulas using more than one function and exercises.

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 manyt 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 end,
=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 fromat 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)

Exercises

1- You receive data with both surname and first name separated by a comma and a space in a single cell. You want them separated in two cells.

Clues:
in column B you will need: LEFT and FIND
in column C you will need: RIGHT, LEN and FIND

2- You receive data where the complete name appears in a single cell. You want the initial, a period, a space and the surname.

Clues:
you will need: LEFT , RIGHT, LEN, & and FIND

3- My client's SKU's are built like this: a letter then 3 digits for the style, three digits for the color and three digits for the print. For example A305230765 means product "A" with style number "305", color "230" and print "765". They bought another company producing the same kind of goods but their SKU's are built like this: a letter then 2 digits for the color, three digits for the print and three digits for the style. For example A23486235 means product "A" with color number "23", print "486" and style "235". They need to get both databases unified and want to change the other company's SKU's to be the same as theirs style/color/print. We will add a "0" to the color code.

Clues:
you will need: LEFT , RIGHT and  &

4- You have three pieces of information in columns A, B and C. You want to upload them in the central database but the database administrator tells you that the system will only accept a single string of characters where the first field is 10 characters long justified to the left with spaces as filler, the second field is 20 characters long justified to the left with spaces as filler and the third field is 8 characters long justified to the right with zeros as filler with NO decimal point.

Clues:
you will need: LEN , REPT and  &

Excel Index and Search Tool (Click on the buttons below)

Comments: Click Here

Excel VBA Online Consulting

Tips on Excel

Tips on VBA for Excel