Extract Relevant data to relevant columns from unordered data in Excel
<p>I have data in the following manner in column A of excel (cells A2, A3, A4)</p>

<ol>
<li>Apple 223 12 Bags 32 Carrots 4231 2321 Doughnuts 5231 232 1321 Elephants 6232</li>
<li>Apple 3123213 Bags 412321 3213 Carrots 411 Doughnuts 621 3213 Elephants 71 2321</li>
<li>Apple 212324 Carrots 52312 Bags 42313 Elephants 512312 Doughnuts 621 32132</li>
</ol>

<p>I need to pull this data to relevant columns A, B, C, D, E.
Have been able to come up with the below solution till now.</p>

<ul>
<li>Insert column headers A, B, C, D, E, Dummy in first row (i.e. B1, C1, D1, E1, F1, G1)</li>
<li><p>Put the following formula into the respective cells on rows 2,3,4</p>

<pre><code> =MID($A2,FIND(E$1,$A2)+LEN(E$1),IFERROR(FIND(F$1,$A2),LEN($A2)+1)-FIND(E$1,$A2)-LEN(E$1))
</code></pre></li>
</ul>

<p>This works for data set 1,2 where the data is in ordered fashion i.e. A then B then C so on</p>

<p>But it doesnt work on data set 3 where data is undordered i.e. A then C then B then E then D..as shown below.  Any suggestions appreciated - Am open to VBA solutions too. Thanks.</p>

<p><a href="https://i.stack.imgur.com/eg7Ne.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eg7Ne.png" alt="TexttoCols"></a></p>