O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #78. Construct Mega-Formulas

Mega-formulas—a formula within a formula within a formula—are enough to send even the most seasoned Excel veteran running for the hills. With a little forethought and by working step by step toward the formula you need, however, you can tame those complex mega-formulas without fear.

Does the very thought of having to make sense of, let alone construct, nested formulas fill you with dread? Some of those cells, so chock-full of complex functional gobbledygook, make us feel a little faint too. But with a little forethought and a step-by-step approach, you'll be creating mega-formulas without fear. And maybe, just maybe, you'll even be able to read and understand them again later.

The trick is to build up your formulas, bit by bit, using Excel's standard functions. Use one function per cell, obtaining individually manageable results, and then nest them together to yield the result you need. Here's an example of such a process in action.

Say you've been given a long list of people's names, each consisting of first, middle, and last names—one full name per cell. Your job is to write a formula in the adjacent column to extract only the person's last name.

What you're after, then, is the start of the last name—the third word—in the cell. Actually, what you'll be looking for is the position of the second space character in the cell. Excel has no standard built-in function to automatically locate the second space character in a cell, but you can bring ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required