Return nth term from comma delimited cell base on value of adjacent comma delimited cell.

tds019

New Member
Joined
Feb 16, 2017
Messages
3
Hoping someone can help me with a function. I want to return the number in the left column that corresponds to “99292” in the middle column. I know I can use the “find” function to find the position of 99292 in the middle column, but that doesn’t correspond to anything meaningful in the left column because the number of digits in the middle column will always vary.

I know I could also turn commas into columns and it should be easy from there but I want to do this in only a few columns since the number of values could get very large making comma to columns unwieldy.

In the right column below I have explained what value the function should return.

1, 1, -1, 1, 1, 1, 4, 1, -1, 1, -1, 12500003, 2500003, 2500003, 27100000, 99143, 99291, 99292, 2500003, 2500003, 99143, 99143, 96372Here I would want to return “4” since that is the number in the left column that corresponds to 99292 in the right column, they’re both the seventh number
1, 1, -1, 1, 1, 1, 1, 1, 1, 1, 1, -1, 1, -1, 1, 1, 1, 1, 1, 1, 7, 1, -173130, 73110, 73110, 36415, 80048, 85730, 85027, 85610, 86900, 86901, 86850, 86900, 86900, 36415, 36415, 27200000, 99285, 36592, 94761, 99291, 99292, 12001, 99285Here I would want to return 7. 99292 is the third to last number in the right column so I would want to return the third to last number in the left column.

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi, welcome to the forum!

Here is one option you can try, albeit a little unwieldy:


Excel 2013
ABC
11, 1, -1, 1, 1, 1, 4, 1, -1, 1, -1, 12500003, 2500003, 2500003, 27100000, 99143, 99291, 99292, 2500003, 2500003, 99143, 99143, 963724
21, 1, -1, 1, 1, 1, 1, 1, 1, 1, 1, -1, 1, -1, 1, 1, 1, 1, 1, 1, 7, 1, -173130, 73110, 73110, 36415, 80048, 85730, 85027, 85610, 86900, 86901, 86850, 86900, 86900, 36415, 36415, 27200000, 99285, 36592, 94761, 99291, 99292, 12001, 992857
Sheet1
Cell Formulas
RangeFormula
C1=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(1+LEN(LEFT(B1,FIND(", 99292,",", "&B1&",")))-LEN(SUBSTITUTE(LEFT(B1,FIND(", 99292,",", "&B1&",")),",","")))*99-98,99))
 
Upvote 0
Wow! That seems to work! Thank you so much. A few follow-up questions/comments.

1. Some do not contain 99292 at all in which case I want the column to return 0. I was able to do this with a modification to your function(my data is actually in H and I):
=IF(ISNUMBER(SEARCH(99292,I1227)),(TRIM(MID(SUBSTITUTE(H1227,",",REPT(" ",99)),(1+LEN(LEFT(I1227,FIND(", 99292,",", "&I1227&",")))-LEN(SUBSTITUTE(LEFT(I1227,FIND(", 99292,",", "&I1227&",")),",","")))*99-98,99))),0)

2. Is there a limit to how many terms this will work for? Some are returning #VALUE!. It seems to be the very long ones. Unfortunately some have around 5,000 terms, I just pasted some of the simpler ones to this thread. Is there any way to modify the formula to make it work for cells that have 5 to 6 thousand terms?
 
Upvote 0
Wow! That seems to work! Thank you so much. A few follow-up questions/comments.

1. Some do not contain 99292 at all in which case I want the column to return 0. I was able to do this with a modification to your function(my data is actually in H and I):
=IF(ISNUMBER(SEARCH(99292,I1227)),(TRIM(MID(SUBSTITUTE(H1227,",",REPT(" ",99)),(1+LEN(LEFT(I1227,FIND(", 99292,",", "&I1227&",")))-LEN(SUBSTITUTE(LEFT(I1227,FIND(", 99292,",", "&I1227&",")),",","")))*99-98,99))),0)

2. Is there a limit to how many terms this will work for? Some are returning #VALUE!. It seems to be the very long ones. Unfortunately some have around 5,000 terms, I just pasted some of the simpler ones to this thread. Is there any way to modify the formula to make it work for cells that have 5 to 6 thousand terms?
You try this formula:
PHP:
=IFERROR(LOOKUP(100,--MID(A1,FIND("X",SUBSTITUTE(A1,",","X",LEN(LEFT(B1,FIND(99292,B1)))-LEN(SUBSTITUTE(LEFT(B1,FIND(99292,B1)),",",""))))+1,ROW($1:$4))),)
 
Last edited:
Upvote 0
That works! Perfectly! The only oddity is if there are more than one 99292 it returns the number associated with the first one, which is fine for my purposes.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top