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>
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
Hi, welcome to the forum!

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

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">1, 1, -1, 1, 1, 1, 4, 1, -1, 1, -1, 1</td><td style=";">2500003, 2500003, 2500003, 27100000, 99143, 99291, 99292, 2500003, 2500003, 99143, 99143, 96372</td><td style="font-weight: bold;text-align: right;color: #333333;background-color: #FFFF00;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">1, 1, -1, 1, 1, 1, 1, 1, 1, 1, 1, -1, 1, -1, 1, 1, 1, 1, 1, 1, 7, 1, -1</td><td style=";">73130, 73110, 73110, 36415, 80048, 85730, 85027, 85610, 86900, 86901, 86850, 86900, 86900, 36415, 36415, 27200000, 99285, 36592, 94761, 99291, 99292, 12001, 99285</td><td style="font-weight: bold;text-align: right;color: #333333;;">7</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A1,",",REPT(<font color="Purple">" ",99</font>)</font>),(<font color="Green">1+LEN(<font color="Purple">LEFT(<font color="Teal">B1,FIND(<font color="#FF00FF">", 99292,",", "&B1&","</font>)</font>)</font>)-LEN(<font color="Purple">SUBSTITUTE(<font color="Teal">LEFT(<font color="#FF00FF">B1,FIND(<font color="Navy">", 99292,",", "&B1&","</font>)</font>),",",""</font>)</font>)</font>)*99-98,99</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

tds019

New Member
Joined
Feb 16, 2017
Messages
3
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?
 

eke_rula

New Member
Joined
Feb 7, 2017
Messages
45
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:
Code:
=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:

tds019

New Member
Joined
Feb 16, 2017
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

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
Top