How many characters in a cell?

A single cell can hold up to 32,000 characters. To put things into perspective, this chapter contains about 30,000 characters. We certainly don’t recommend using a cell in lieu of a word processor, but you really don’t have to lose much sleep worrying about filling up a cell with text.

Numbers as text

As we mentioned, Excel distinguishes between numbers and text. In some cases, such as part numbers and credit card numbers, you don’t need a number to be numerical. If you want to “force” a number to be considered as text, you can do one of the following:

• Apply the Text number format to the cell before you enter the number. Select Text from the Number Format drop-down list, which can be found in the Home ➜ Number group. If you haven’t applied other horizontal alignment formatting, the value will appear left aligned in the cell (like normal text), and functions like SUM will not treat it as a value. Note, however, that it doesn’t work in the opposite direction. If you enter a number and then format it as text, the number will be left aligned, but functions will continue to treat the entry as a value.
• Precede the number with an apostrophe. The apostrophe isn’t displayed, but the cell entry will be treated as if it were text. Functions like SUM will not treat the cell as a number.

Even though a cell is formatted as text (or uses an apostrophe), you can still perform some mathematical operations on the cell if the entry looks like a number. For example, assume cell A1 contains a numeric value preceded by an apostrophe. This formula displays the value in A1, incremented by 1:

=A1+1

This formula, however, treats the contents of cell A1 as 0:

=SUM(A1:A10)

To confuse things even more, if you format cell A1 as text, the preceding SUM formula treats it as 0.

In some cases, treating text as a number can be useful. In other cases, it can cause problems. Bottom line? Just be aware of Excel’s inconsistency in how it treats a number formatted as text.

Determining whether a cell contains text

In some situations, you may need a formula that determines the type of data contained in a particular cell. For example, you can use an IF function to return a result only if a cell contains text. The easiest way to make this determination is to use the ISTEXT function.

The ISTEXT function takes a single argument, returning TRUE if the argument contains text and FALSE if it doesn’t contain text. The formula that follows returns TRUE if A1 contains a string:

=ISTEXT(A1)

You can also use the TYPE function. The TYPE function takes a single argument and returns a value that indicates the type of data in a cell. If cell A1 contains a text string, the formula that follows returns 2 (the code number for text):

=TYPE(A1)

Both the ISTEXT function and the TYPE function consider a numeric value that’s preceded by an apostrophe to be text. However, these functions do not consider a number formatted as Text to be text unless the Text formatting is applied before you enter the number in the cell.

This sounds very confusing (and it is), but in actual practice, it’s rare to need to identify the contents of a cell as numeric or text.

Working with character codes

Every character that you see on your screen has an associated code number. For Windows systems, Excel uses the standard American National Standards Institute (ANSI) character set. The ANSI character set consists of 255 characters, numbered from 1 to 255. An ANSI character requires one byte of storage. Excel also supports an extended character set known as Unicode, in which each character requires two bytes of storage.

Figure 5.2 shows an Excel worksheet that displays all 255 ANSI characters. This example uses the Calibri font. (Other fonts may have different characters.)

Two functions come into play when dealing with character codes: CODE and CHAR. These functions aren’t very useful by themselves. However, they can prove quite useful in conjunction with other functions. We discuss these functions in the following sections.

=CODE("A")

=CODE("Abbey Road")

=CHAR(65)

=CHAR(CODE("A"))

=CHAR(CODE(A1)+32)

Determining whether two strings are identical

You can enter a simple logical formula to determine whether two cells contain the same entry. For example, use this formula to determine whether cell A1 has the same contents as cell A2:

=A1=A2

Excel acts a bit lax in its comparisons when text is involved. Consider the case in which A1 contains the word January (initial capitalization), and A2 contains JANUARY (all uppercase). You’ll find that the previous formula returns TRUE even though the contents of the two cells are not really the same. In other words, the comparison is not case sensitive.

In many cases, you don’t need to worry about the case of the text. However, if you need to make an exact, case-sensitive comparison, you can use Excel’s EXACT function. The formula that follows returns TRUE only if cells A1 and A2 contain exactly the same entry:

=EXACT(A1,A2)

The following formula returns FALSE because the two strings do not match exactly with respect to case:

=EXACT("California","california")

Joining two or more cells

Excel uses an ampersand (&) as its concatenation operator. Concatenation is simply a fancy term that describes what happens when you join the contents of two or more cells. For example, if cell A1 contains the text Tucson, and cell A2 contains the text Arizona, the following formula then returns TucsonArizona:

=A1&A2

Notice that the two strings are joined without an intervening space. To add a space between the two entries (to get Tucson Arizona), use a formula like this one:

=A1&" "&A2

Or, even better, use a comma and a space to produce Tucson, Arizona:

=A1&", "&A2

Another option is to eliminate the quote characters and use the CHAR function with an appropriate argument. Note this example of using the CHAR function to represent a comma (44) and a space (32):

=A1&CHAR(44)&CHAR(32)&A2

If you’d like to force a line break between strings, concatenate the strings by using CHAR(10), which inserts a line break character. Also, make sure that you apply the wrap text format to the cell. (Choose Home ➜ Alignment ➜ Wrap Text.) The following example joins the text in cell A1 and the text in cell B1 with a line break in between:

=A1&CHAR(10)&B1

The following formula returns the string Stop by concatenating four characters returned by the CHAR function:

=CHAR(83)&CHAR(116)&CHAR(111)&CHAR(112)

Here’s a final example of using the & operator. In this case, the formula combines text with the result of an expression that returns the maximum value in column C:

="The largest value in Column C is " &MAX(C:C)

Displaying formatted values as text

The Excel TEXT function enables you to display a value in a specific number format. Although this function may appear to have dubious value, it does serve some useful purposes, as the examples in this section demonstrate. Figure 5.3 shows a simple worksheet. The formula in cell D2 is

="The net profit is " & B2

This formula essentially combines a text string with the contents of cell B2 and displays the result. Note, however, that the value from cell B2 is not formatted in any way. You might want to display the contents in cell B2 using a currency number format.

We can use the TEXT function to simulate the number formatting shown in cell D2:

="The net profit is " & TEXT(B2,"$#,##0.00")

This formula displays the text along with a nicely formatted value: The net profit is $171,653.

The second argument for the TEXT function consists of a standard Excel number format string. You can enter any valid number format string for this argument. Note, however, that color codes in number format strings are ignored.

The preceding example uses a simple cell reference (B2). You can, of course, use an expression instead. Here’s an example that combines text with a number resulting from a computation:

="Average Expenditure: "& TEXT(AVERAGE(A:A),"$#,##0.00")

This formula might return a string such as Average Expenditure: $7,794.57.

Here’s another example that uses the NOW function (which returns the current date and time). The TEXT function displays the date and time, nicely formatted:

="Report printed on "&TEXT(NOW(),"mmmm d, yyyy, at h:mm AM/PM")

The formula might display the following: Report printed on July 22, 2015 at 3:23 PM.

Displaying formatted currency values as text

Excel’s DOLLAR function converts a number to text using the currency format. It takes two arguments: the number to convert, and the number of decimal places to display. The DOLLAR function uses the regional currency symbol (for example, $).

You can sometimes use the DOLLAR function in place of the TEXT function. The TEXT function, however, is much more flexible because it doesn’t limit you to a specific number format. The second argument for the DOLLAR function specifies the number of decimal places.

The following formula returns Total: $1,287.37:

="Total: " & DOLLAR(1287.367, 2)

Removing excess spaces and nonprinting characters

Often, data imported into an Excel worksheet contains excess spaces or strange (often unprintable) characters. Excel provides you with two functions to help whip your data into shape: TRIM and CLEAN:

• TRIM removes all leading and trailing spaces, and it replaces internal strings of multiple spaces with a single space.
• CLEAN removes all nonprinting characters from a string. These “garbage” characters often appear when you import certain types of data.

This example uses the TRIM function. The formula returns Fourth Quarter Earnings (with no excess spaces):

=TRIM("   Fourth    Quarter     Earnings     ")

Counting characters in a string

The LEN function takes one argument and returns the number of characters in the argument. For example, assume that cell A1 contains the string September Sales. The following formula returns 15:

=LEN(A1)

Notice that space characters are included in the character count. The LEN function can be useful for identifying strings with extraneous spaces, which can cause problems in some situations, such as in lookup formulas. The following formula returns FALSE if cell A1 contains any leading spaces, trailing spaces, or multiple spaces.

=LEN(A1)=LEN(TRIM(A1))

The following formula shortens text that is too long. If the text in A1 is more than ten characters in length, this formula returns the first nine characters plus an ellipsis (133 on the ANSI chart) as a continuation character. If cell A1 contains ten or fewer characters, the entire string is returned:

=IF(LEN(A1)>10,LEFT(A1,9)&CHAR(133),A1)

Repeating a character or string

The REPT function repeats a text string (first argument) any number of times you specify (second argument). For example, this formula returns HoHoHo:

=REPT("Ho",3)

You can also use this function to create a crude horizontal divider between cells. This example displays a squiggly line, 20 characters in length:

=REPT("~",20)

Creating a text histogram

A clever use for the REPT function is to create a simple histogram (also known as a frequency distribution) directly in a worksheet (chart not required). Figure 5.4 shows an example of such a histogram. You’ll find this type of graphical display especially useful when you need to visually summarize many values. In such a case, a standard chart may be unwieldy.

The formulas in column D graphically depict the values in column B by displaying a series of characters in the Wingdings 2 font. This example uses character code 162, which displays as a solid rectangle in the Wingdings 2 font. A formula using the REPT function determines the number of characters displayed. Cell D2 contains this formula:

=REPT(CHAR(162),B2/100)

Assign the Wingdings 2 font to cell D2 and then copy the formulas down the column to accommodate all the data. Depending on the numerical range of your data, you may need to change the scaling. Experiment by replacing the 100 value in the formulas. You can substitute any character you like to produce a different character in the chart.

Padding a number

You’re probably familiar with a common security measure (frequently used on printed checks) in which numbers are padded with asterisks on the right. The following formula displays the value in cell A1, along with enough asterisks to make 24 characters total:

=(A1 & REPT("*",24-LEN(A1)))

Or if you’d prefer to pad the number with asterisks on the left, use this formula:

=REPT("*",24-LEN(A1))&A1

The following formula displays asterisk padding on both sides of the number. It returns 24 characters when the number in cell A1 contains an even number of characters; otherwise, it returns 23 characters:

=REPT("*",12-LEN(A1)/2)&A1&REPT("*",12-LEN(A1)/2)

The preceding formulas are a bit deficient because they don’t show any number formatting. Note this revised version that displays the value in A1 (formatted), along with the asterisk padding on the left:

=REPT("*",24-LEN(TEXT(A1,"$#,##0.00")))&TEXT(A1,"$#,##0.00")

Figure 5.5 shows these formulas in action.

You can also pad a number by using a custom number format. To repeat the next character in the format to fill the column width, include an asterisk (*) in the custom number format code. For example, use this number format to pad the number with dashes:

$#,##0.00*-

To pad the number with asterisks, use two asterisks, like this:

$#,##0.00**

Changing the case of text

Excel provides three handy functions to change the case of text:

• UPPER: Converts the text to ALL UPPERCASE.
• LOWER: Converts the text to all lowercase.
• PROPER: Converts the text to Proper Case. (The First Letter In Each Word Is Capitalized.)

These functions are quite straightforward. The formula that follows, for example, converts the text in cell A1 to proper case. If cell A1 contained the text MR. JOHN Q. PUBLIC, the formula would return Mr. John Q. Public:

=PROPER(A1)

These functions operate only on alphabetic characters; they ignore all other characters and return them unchanged.

Extracting characters from a string

Excel users often need to extract characters from a string. For example, you may have a list of employee names (first and last names) and need to extract the last name from each cell. Excel provides several useful functions for extracting characters:

• LEFT: Returns a specified number of characters from the beginning of a string
• RIGHT: Returns a specified number of characters from the end of a string
• MID: Returns a specified number of characters beginning at any specified position within a string

The formula that follows returns the last 10 characters from cell A1. If A1 contains fewer than 10 characters, the formula returns all the text in the cell:

=RIGHT(A1,10)

This next formula uses the MID function to return five characters from cell A1, beginning at character position 2. In other words, it returns characters 2 through 6:

=MID(A1,2,5)

The following example returns the text in cell A1, with only the first letter in uppercase (sometimes referred to as sentence case). It uses the LEFT function to extract the first character and convert it to uppercase. This character then concatenates to another string that uses the RIGHT function to extract all but the first character (converted to lowercase):

=UPPER(LEFT(A1))&LOWER(RIGHT(A1,LEN(A1)-1))

If cell A1 contained the text FIRST QUARTER, the formula would return First quarter.

Replacing text with other text

In some situations, you may need a formula to replace a part of a text string with some other text. For example, you may import data that contains asterisks, and you may need to convert the asterisks to some other character. You could use Excel’s Home ➜ Editing ➜ Find & Select ➜ Replace command to make the replacement. If you prefer a formula-based solution, you can take advantage of either of two functions:

• SUBSTITUTE replaces specific text in a string. Use this function when you know the character(s) that you want to replace but not the position.
• REPLACE replaces text that occurs in a specific location within a string. Use this function when you know the position of the text that you want to replace but not the actual text.

The following formula uses the SUBSTITUTE function to replace 2012 with 2013 in the string 2012 Budget. The formula returns 2013 Budget:

=SUBSTITUTE("2012 Budget","2012","2013")

The following formula uses the SUBSTITUTE function to remove all spaces from a string. In other words, it replaces all space characters with an empty string. The formula returns 2013OperatingBudget:

=SUBSTITUTE("2013 Operating Budget"," ","")

The following formula uses the REPLACE function to replace one character beginning at position 5 with nothing. In other words, it removes the fifth character (a hyphen) and returns Part544:

=REPLACE("Part-544",5,1,"")

You can, of course, nest these functions to perform multiple replacements in a single formula. The formula that follows demonstrates the power of nested SUBSTITUTE functions. The formula essentially strips out any of the following seven characters in cell A1: space, hyphen, colon, asterisk, underscore, left parenthesis, and right parenthesis:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((A1," ",""),"-",""),":",""),"*",""),"_",""),"(",""),")","")

Therefore, if cell A1 contains the string Part-2A - Z(4M1)_A*, the formula returns Part2AZ4M1A.

Finding and searching within a string

The Excel FIND and SEARCH functions enable you to locate the starting position of a particular substring within a string:

• FIND: Finds a substring within another text string and returns the starting position of the substring. You can specify the character position at which to begin searching. Use this function for case-sensitive text comparisons. Wildcard comparisons are not supported.
• SEARCH: Finds a substring within another text string and returns the starting position of the substring. You can specify the character position at which to begin searching. Use this function for non–case-sensitive text or when you need to use wildcard characters.

The following formula uses the FIND function and returns 7, the position of the first m in the string. Notice that this formula is case sensitive:

=FIND("m","Big Mamma Thornton",1)

The formula that follows, which uses the SEARCH function, returns 5, the position of the first m (either uppercase or lowercase):

=SEARCH("m","Big Mamma Thornton",1)

You can use the following wildcard characters within the first argument for the SEARCH function:

• Question mark (?): Matches any single character
• Asterisk (*): Matches any sequence of characters

The next formula examines the text in cell A1 and returns the position of the first three-character sequence that has a hyphen in the middle of it. In other words, it looks for any character followed by a hyphen and any other character. If cell A1 contains the text Part-A90, the formula returns 4:

=SEARCH("?-?",A1,1)

Searching and replacing within a string

You can use the REPLACE function in conjunction with the SEARCH function to create a new string that replaces part of the original text string with another string. In effect, you use the SEARCH function to find the starting location used by the REPLACE function.

For example, assume cell A1 contains the text Annual Profit Figures. The following formula searches for the word Profit and replaces those six characters with the word Loss:

=REPLACE(A1,SEARCH("Profit",A1),6,"Loss")

This next formula uses the SUBSTITUTE function to accomplish the same effect in a more efficient manner:

=SUBSTITUTE(A1,"Profit","Loss")

Counting specific characters in a cell

This formula counts the number of Bs (uppercase only) in the string in cell A1:

=LEN(A1)-LEN(SUBSTITUTE(A1,"B",""))

This formula uses the SUBSTITUTE function to create a new string (in memory) that has all the Bs removed. Then the length of this string is subtracted from the length of the original string. The result reveals the number of Bs in the original string.

The following formula is a bit more versatile. It counts the number of Bs (both upper- and lowercase) in the string in cell A1:

=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"B",""),"b",""))

Counting the occurrences of a substring in a cell

The formulas in the preceding section count the number of occurrences of a particular character in a string. The following formula works with more than one character. It returns the number of occurrences of a particular substring (contained in cell B1) within a string (contained in cell A1). The substring can consist of any number of characters:

=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)

For example, if cell A1 contains the text Blonde On Blonde and B1 contains the text Blonde, the formula returns 2.

The comparison is case sensitive, so if B1 contains the text blonde, the formula returns 0. The following formula is a modified version that performs a case-insensitive comparison:

=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")))/LEN(B1)

Removing trailing minus signs

Some accounting systems use a trailing minus sign to indicate negative values. If you import such a report into Excel, the values with trailing minus signs are interpreted as text.

The formula that follows checks for a trailing minus sign. If found, it removes the minus sign and returns a negative number. If cell A1 contains 198.43–, the formula returns –198.43:

=IF(RIGHT(A1,1)="–",LEFT(A1,LEN(A1)–1)*–1,A1)

Expressing a number as an ordinal

You may need to express a value as an ordinal number: for example, Today is the 21st day of the month. In this case, the number 21 converts to an ordinal number by appending the characters st to the number. Keep in mind that the result of this formula is a string, not a value. Therefore, it can’t be used in numerical formulas.

The characters appended to a number depend on the number. There is no clear pattern, making the construction of a formula more difficult. Most numbers will use the th suffix. Exceptions occur for numbers that end with 1, 2, or 3 except if the preceding number is a 1: that is, numbers that end with 11, 12, or 13. These may seem like fairly complex rules, but you can translate them into an Excel formula.

The formula that follows converts the number in cell A1 (assumed to be an integer) to an ordinal number:

=A1&IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),"th",IF(OR(VALUE(RIGHT(A1))={1,2,3}),
CHOOSE(RIGHT(A1),"st","nd","rd"),"th"))

This is a rather complicated formula, so it may help to examine its components. Basically, the formula works as follows:

• Rule #1: If the last two digits of the number are 11, 12, or 13, use th.
• Rule #2: If Rule #1 does not apply, check the last digit.
  • If the last digit is 1, use st.
  • If the last digit is 2, use nd.
  • If the last digit is 3, use rd.
• Rule #3: If neither Rule #1 nor Rule #2 apply, use th.

Figure 5.6 shows the formula in use.

Determining a column letter for a column number

This next formula returns a worksheet column letter (ranging from A to XFD) for the value contained in cell A1. For example, if A1 contains 29, the formula returns AC (the 29th column letter in a worksheet):

=LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)

Note that the formula doesn’t check for a valid column number. In other words, if A1 contains a value less than 1 or greater than 16,384, the formula then returns an error. The following modification uses the IFERROR function to display text (Invalid Column) instead of an error value:

=IFERROR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1),"Invalid Column")

The IFERROR function was introduced in Excel 2007. For compatibility with versions prior to Excel 2007, use this formula:

=IF(ISERR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)),
"Invalid Column",LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1))

Extracting a filename from a path specification

The following formula returns the filename from a full path specification. For example, if cell A1 contains c:filesexcelmyfile.xlsx, the formula returns myfile.xlsx:

=MID(A1,FIND("*",SUBSTITUTE(A1,"","*",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))))+1,LEN(A1))

The preceding formula assumes that the system path separator is a backslash (). It essentially returns all the text following the last backslash character. If cell A1 doesn’t contain a backslash character, the formula returns an error.

Extracting the first word of a string

To extract the first word of a string, a formula must locate the position of the first space character and then use this information as an argument for the LEFT function. The following formula does just that:

=LEFT(A1,FIND(" ",A1)-1)

This formula returns all the text prior to the first space in cell A1. However, the formula has a slight problem: it returns an error if cell A1 consists of a single word. A simple modification solves the problem by using an IFERROR function to check for the error:

=IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)

For compatibility with versions prior to Excel 2007, use this formula:

=IF(ISERR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))

Extracting the last word of a string

Extracting the last word of a string is more complicated because the FIND function works from left to right only. Therefore, the problem rests with locating the last space character. The formula that follows, however, solves this problem. It returns the last word of a string (all the text following the last space character):

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

This formula, however, has the same problem as the first formula in the preceding section: it fails if the string does not contain at least one space character. The following modified formula uses the IFERROR function to avoid the error value:

=IFERROR(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),A1)

For compatibility with versions prior to Excel 2007, use this formula:

=IF(ISERR(FIND(" ",A1)),A1,
RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Extracting all but the first word of a string

The following formula returns the contents of cell A1, except for the first word:

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

If cell A1 contains 2013 Operating Budget, the formula then returns Operating Budget.

This formula returns an error if the cell contains only one word. The following formula solves this problem and returns an empty string if the cell does not contain multiple words:

=IFERROR(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),"")

For compatibility with versions prior to Excel 2007, use this formula:

=IF(ISERR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))

Extracting first names, middle names, and last names

Suppose you have a list consisting of people’s names in a single column. You have to separate these names into three columns: one for the first name, one for the middle name or initial, and one for the last name. This task is more complicated than you may initially think because not every name in the column has a middle name or middle initial. However, you can still do it.

All the formulas that follow assume that the name appears in cell A1.

You can easily construct a formula to return the first name:

=IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)

Returning the middle name or initial is much more complicated because not all names have a middle initial. This formula returns the middle name or initial (if it exists); otherwise, it returns nothing:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,
MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND(" ",A1)+1)),"")

Finally, this formula returns the last name:

=IFERROR(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE (A1," ",""))))),"")

The formula that follows is a much shorter way to extract the middle name. This formula is useful if you use the other formulas to extract the first name and the last name. It assumes that the first name is in B1 and that the last name is in D1:

=IF(LEN(B1&D1)+2>=LEN(A1),"",MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1&D1)-2)

As you can see in Figure 5.7, the formulas work fairly well. There are a few problems, however: notably, names that contain four “words.” But, as we mentioned earlier, you can clean up these cases.

Removing titles from names

You can use the formula that follows to remove four common titles (Mr., Dr., Ms., and Mrs.) from a name. For example, if cell A1 contains Mr. Fred Munster, the following formula would return Fred Munster:

=IF(OR(LEFT(A1,2)={"Mr","Dr","Ms"}),RIGHT(A1,LEN(A1)-(FIND(".",A1)+1)),A1)

Counting the number of words in a cell

The following formula returns the number of words in cell A1:

=LEN(TRIM(A1))-LEN(SUBSTITUTE((A1)," ",""))+1

The formula uses the TRIM function to remove excess spaces. It then uses the SUBSTITUTE function to create a new string (in memory) that has all the space characters removed. The length of this string is subtracted from the length of the original (trimmed) string to get the number of spaces. This value is then incremented by 1 to get the number of words.

Note that this formula returns 1 if the cell is empty. The following modification solves that problem:

=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)