Extract Text from a Text String separated by Semicolon using formulas

[angel34]

I have a text string in say, A6 separated by semicolon, comprising of 8 words which looks something like this

dog;cat;mouse;tiger;squirrel,moose,panther,lion

I would like to extract the 4th, 5th and 8th word i.e. tiger, squirrel and lion in D6, E6 and F6 respectively. After extraction, D6 should contain tiger, E6 squirrel and F6 lion. All the other words need not be extracted.

I need to accomplish the task by using standard excel formulas and VBA cannot be used in this instance. I have tried using LEFT, MID RIGHT, LEN and FIND functions but could not pull through the correct formula. I am just not able to figure out the error I am making.

I shall appreciate very much any help in this regard.

Thanks and Regards,

 

[Special-K99]

in D6
=TRIM(MID(SUBSTITUTE($A6,";",REPT(" ",LEN($A6))),(4-1)*(LEN($A6))+1,LEN($A6)))
in E6
=TRIM(MID(SUBSTITUTE($A6,";",REPT(" ",LEN($A6))),(5-1)*(LEN($A6))+1,LEN($A6)))
in F6
=TRIM(MID(SUBSTITUTE($A6,";",REPT(" ",LEN($A6))),(8-1)*(LEN($A6))+1,LEN($A6)))

The standard formula is

=TRIM(MID(SUBSTITUTE($A6,";",REPT(" ",LEN($A6))),(X-1)*(LEN($A6))+1,LEN($A6)))

where X is the nth word you want to extract and ; is the word delimiter

 

[Rick Rothstein]

Using a single formula, put this in cell D6 and copy across for 3 columns (the same number of columns as for the number positions to pull data from)...

=TRIM(MID(SUBSTITUTE(";"&$A6,";",REPT(" ",300)),INDEX({4,5,8},,COLUMNS($D:D))*300,300))

The comma delimited set of positions to pull from are highlighted in red. Note that the 300's used above means the formula assumes your text in cell A6 will never be longer than 300 characters (adjust as needed).

 

[angel34]

Thanks to both Special K-99 and Rick for sparing the time to reply to my query. I shall try the formulae and revert back with the feedback soon. Thanks once again for your valuable guidance.

 

[angel34]

Both the suggestions worked like a dream. Thanks again to both of you for helping me out.