Formula Excel : Adding a Line Break within a Formula

When creating charts in Excel, it’s sometimes useful to force line breaks for the purpose of composing better visualizations. Take the chart shown in Image 3-9, for example. Here, the X-axis labels in the chart include the data value in addition to the sales rep. This setup works well when you don’t want to inundate your chart with data labels. Image 3-9: The X-axis labels...

Read More!

Formula Excel : Counting Specific Characters in a Cell

A useful trick is to be able to count the number of times a specific character exists in a text string. The technique for doing this in Excel is a bit clever. To figure out, for example, how many times the letter s appears in the word Mississippi, you can count them by hand, of course, but systematically, you can follow these general steps: Measure the character length of...

Read More!

Formula Excel : Substituting Text Strings

In some situations, it’s helpful to substitute some text with other text. One such case is when you encounter the annoying apostrophe S (’S) quirk that you get with the PROPER function. To see what we mean, enter this formula into Excel: =PROPER("STAR'S COFFEE") This formula is meant to convert the given text into title case (where the first letter of every word is capitalized)....

Read More!

Formula Excel : Finding a Particular Character in a Text String

Excel’s LEFT, RIGHT, and MID functions work great for extracting text, but only if you know the exact position of the characters you are targeting. What do you do when you don’t know exactly where to start the extraction? For example, if you had the following list of Product codes, how would you go about extracting all the text after the hyphen? PRT-432 COPR-6758 SVCCALL-58574 The...

Read More!

Formula Excel : Extract Parts of a Text String

One of the most important techniques for manipulating text in Excel is the capability to extract specific portions of text. Using Excel’s LEFT, RIGHT, and MID functions, you can perform tasks such as: Convert nine-digit postal codes into five-digit postal codes Extract phone numbers without the area code Extract parts of employee or job codes for use somewhere else Image...

Read More!

Formula Excel : Removing Spaces from a Text String

If you pull data in from external databases and legacy systems, you will no doubt encounter text that contains extra spaces. Sometimes these extra spaces are found at the beginning of the text, whereas at other times, they show up at the end. Extra spaces are generally evil because they can cause problems in lookup formulas, charting, column sizing, and printing. Image 3-3...

Read More!

Formula Excel : Setting Text to Sentence Case

Excel provides three useful functions to change the text to upper-, lower-, or proper case. As you can see in rows 6, 7, and 8 illustrated in Figure 3-2, these functions require nothing more than a pointer to the text you want converted. As you might guess, the UPPER function converts text to all uppercase, the LOWER function converts text to all lowercase, and the PROPER...

Read More!

Formula Excel : Joining Text Strings

One of the more basic text manipulation actions you can perform is joining text strings together. In the example shown in Image 3-1, you create a full-name column by joining together first and last names. Image 3-1: Joining first and last names. How it works This example illustrates the use of the ampersand (&) operator. The ampersand operator tells Excel to concatenate...

Read More!

Formula Excel : Creating a Conversion Table

You may work at a company where you need to know how many cubic yards can be covered by a gallon of material, or how many cups are needed to fill an Imperial Gallon. You can use Excel’s CONVERT function to produce a conversion table containing every possible type of conversion that you need for a set of measures. Image 12-14 illustrates a conversion table created using nothing...

Read More!

Formula Excel : Counting Values in a Range

Excel provides several functions to count the values in a range: COUNT, COUNTA, and COUNTBLANK. Each of these functions provides a different method of counting based on whether the values in your range are numbers, numbers and text, or blank. Image 2-13 illustrates the different kinds of counting you can perform. In row 12, the COUNT function is counting only exams where students...

Read More!