Nested Formula in Excel

There could be simpler methods to accomplish this, but we’re going to take a different route since we want to try and make the wackiest Nested Excel formula we can (that really works).

You must nest several RIGHT functions to take the text up to the first “;” sign, then use the LEFT function once more to extract the portions on the right. Here is how that appears when the street number portion of the address is extracted.                    “=LEFT((RIGHT(A2,LEN(A2)-FIND(“;”,A2))),FIND(“;”,(RIGHT(A2,LEN(A2)-FIND(“;”,A2))),1)-1)

“Although it appears crazy, putting it together is not difficult. I did nothing more than use the method RIGHT(A2,LEN(A2)-FIND(“;”,A2)).

And placed it into each and every “A2”-filled spot in the LEFT function above.
The second part of the string is successfully extracted in this way.

Another nest needs to be built for each additional segment of the string. The only thing left to do is to copy the “RIGHT” equation you created in the previous step and paste it into a new “RIGHT” formula where “A2” is written. This is how that seems.
(RIGHT((RIGHT(A2,LEN(A2)-FIND(“;”,A2))),LEN((RIGHT(A2,LEN(A2)-FIND(“;”,A2))))-FIND(“;”,(RIGHT(A2,LEN(A2)-FIND(“;”,A2))))))).

Then, whenever there is a “A2,” you must take THAT formula and insert it into the original LEFT formula.

The final, perplexing equation is as follows:

The formula “=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(“;”,A2))”,LEN((RIGHT(A2,LEN(A2)-FIND(“;”,A2))))-FIND(“;”,(RIGHT(A2,LEN(A2)-FIND(“;”,A2)))))),FIND(“;”,(RIGHT((RIGHT(A2,LEN(A2)-FIND(“;”,

Repeat the procedure above to extract the following portion.

Excel formulae can become really complicated, but all you’re actually doing is copying and pasting lengthy formulas together to create lengthy nests that still function.
Yes, this does fit the definition of “insane.” But let’s be honest, there is a far easier way to use only one function to accomplish the same thing.

Simply choose Text to Columns from the Data menu item after selecting the column containing the delimited data.

This will provide a window where you can divide the string using any desired delimiter. Simply type “;,” and you’ll notice how your selected data’s preview updates accordingly.
You can accomplish the same task as that bizarre formula above with a few clicks, but where’s the fun in that?

To Read Similar Article Click on This Link  https://nextepiclife.com/nested-formula-to-parse-strings/