Find a string in cell with multiple values based on number in another cell

[ccantara]

I have a column with multiple choices downloaded from Sharepoint. Each value is separated by a semi-colon. I found a wonderful VBA solution to duplicate rows based on the # of values in this cell on your site. Now, I want to select the specific string based on the # in that column. Example: Cell A1 contained: a; c; d; f.

The macro duplicated this so that now I have 4 rows (2-5) with the exact same information in column A and numbering starting over at 1 for each new item in Column B. I am attaching a spreadsheet to illustrate.

What I need is for Column C to display the value from Column A based on the number in Column B.
I have indicated what the final result in Column C would be. Just don't know the formula or VBA code for accomplishing this.

Whatever help you can provide would be appreciated.

[bebo021999]

How about:

=MID(SUBSTITUTE(A2,";",REPT(" ",100)),(B2-1)*100+1,100)

[samba_ravi]

C2=TRIM(MID(SUBSTITUTE(A2,";",REPT(" ",LEN(A2))),(B2-1)*LEN(A2)+1,LEN(A2)))
Try this and copy towards down

[ccantara]

Correction on earlier reply: This worked great for most of my list. I have some cells that contain more items than others. I'm not sure if there is a limit to this formula or if it's something else. One of my entries has 36 items (1208 characters and spaces) and the formula works fine. Another on my list has 47 (1575 characters and spaces) and I'm getting "#VALUE!" for a result. Any idea why this is happening?

[samba_ravi]

Try
=MID(A2&";",FIND("^",SUBSTITUTE(";"&A2&";",";","^",B2)),FIND("^^",SUBSTITUTE(";"&A2&";",";","^^",B2+1))-fIND("^",SUBSTITUTE(";"&A2&";",";","^",B2))-1)
or
=LEFT(RIGHT(A2&";",LEN(A2)+2-FIND("^",SUBSTITUTE(";"&A2&";",";","^",B2))),FIND(";",RIGHT(A2&";",LEN(A2)+2-FIND("^",SUBSTITUTE(";"&A2&";",";","^",B2))))-1)

[ccantara]

Both worked perfectly! I can't thank you enough!!!