That formula correctly extracts "Portland, ME 04076" out of the original string. The final mind-bending formula looks like this: Then, you need to take THAT formula and place it into the original LEFT formula wherever there's an "A2". Now all you need to do is take the " RIGHT" equation that you created in the last section, and paste it into a new RIGHT formula with the previous RIGHT formula pasted into it where you see "A2". This correctly extracts the second section of the string.Įach subsequent section of the string needs another nest created. All I did is took this function:Īnd inserted it into every place in the LEFT function above where there's an "A2". It looks crazy, but it's not hard to piece together. Here's what that looks like for extracting the street number part of the address.
To extract the parts on the right, you need to nest multiple RIGHT functions to grab the section of text up until that first " " symbol, and perform the LEFT function on it again.
There may be easier ways to do this, but since we want to try and create the craziest Nested Excel formula possible (that actually works), we're going to use a unique approach. REI is a consumer cooperative and reports its financial results to members each year to coincide with the annual.
0 kommentar(er)
