Writing a long formula is not easy, even for an advanced Excel user.  What I mean long is  a formula with many nested FUNCTIONS within a single formula.  The difficulty I am talking about is not related to whether you understand the functions.  It is more about the concentration and carefulness required for writing the formula.  Why is that?

If you have encountered the above message frequently, you know why! 🙂

You need to make sure you have corresponding pair of brackets () for each function; not to mention the comma (,) required for different arguments involved.  For instance, if you have 7 FUNCTIONS in your formula, you should have 7 pairs of brackets plus N commas spread around your formula.

The frustration usually comes from an attempt to write the long formula in one go.  When you are having the above error message popped up non-stop, maybe it’s time for you to break your long formula into bit-size formulas, with helper columns.  Let’s use the Text manipulation example discussed before to illustrate how a long formula is written through many small steps.

Here’s the task:

What we need to do is to convert the poorly written texts on A2:A10 into what we need, illustrated on B2:B10.

• Everything to the left of the hyphen “-” should be in upper cases
• Everything to the right of the hyphen “-” should be in proper cases
• Extra spaces should be removed

Here’s a possible solution:

=TRIM(UPPER(LEFT(A2,FIND("-",A2)-1))&" - " &PROPER(MID(A2,FIND("-",A2)+1,LEN(A2))))

Don’t think that is difficult.  It is just the combination of various small and simple steps.

There are 7 steps involved, as shown from Column B to H

Step 1 – Get the location of the first “-” by using FIND

In B2
=FIND("-",A2)

Step 2 – Get the texts on the right of the “-” by using LEFT

In C2 
=LEFT(A2,B2-1)

Step 3 – Capitalize the result from step 2 by using UPPER

In D2 
=UPPER(C2)

Step 4 – Get the texts on the right of the “-” by using MID and LEN

In E2 
=MID(A2,B2+1,LEN(A2))

Tip: LEN(A2) gives a number big enough to extract all characters after the first “-” 

Step 5 – Change the case of the result from Step 4 to Proper case by  using PROPER

In F2 
=PROPER(E2)

Step 6 – Join the texts resulted from Step 3 and 5 by using Ampersand (&)

In G2 
=D2 & " - " & F2

Step 7 – Remove any extra spaces by using TRIM

In H2 
=TRIM(G2)

Here we go with the answer.

See!? All steps involve short and simple formulation.  To get rid of all helper cells and to obtain a single long formula, we just need to work backward with manual substitution.

=TRIM(G2)

Substitute  G2 –>

=TRIM(D2 & ” – ” & F2)  ‘note: the “=” sign for the formula in G2 is not required

Then substitute F2 –>

=TRIM(D2 & ” – ” & PROPER(E2))

Then substitute E2 –>

=TRIM(D2 & ” – ” & PROPER(MID(A2,B2+1,LEN(A2))))

Then substitute D2 –>

=TRIM(UPPER(C2) & ” – ” & PROPER(MID(A2,B2+1,LEN(A2))))

Then substitute C2 –>

=TRIM(UPPER(LEFT(A2,B2-1)) & ” – ” & PROPER(MID(A2,B2+1,LEN(A2))))

Then substitute B2 (‘note: there are more than one B2) –>

=TRIM(UPPER(LEFT(A2,FIND("-",A2)-1)) & " - " & PROPER(MID(A2,FIND("-",A2)+1,LEN(A2))))

Make sure you have only one reference (i.e. A2 in our example) left in the final formula. Then you are ready to remove the helper columns.

As simple as that.

By the way, this formula is not long indeed.  I have seen a 5-line-long formula, with mainly IF function in it.  In many cases, the longer may not be the better.  🙂