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.
The key to writing a long, complex formula is to break the formula into bite-size formulas with helper columns. This article illustrates the process using an example of converting poorly written text into a consistent format.