Capturing a set quantity based on delimiter

[jbodell]

Good morning,

I have a concatenation of unique identifiers, separated by commas, in column A. I have a formula in Column B which is as follows: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1&",",",",CHAR(1),2))-1) which identifies the first two unique identifiers in A:


A: 01,02,03,04,05
B: 01, 02

What I'm having trouble with is creating a =MID in C to identify the third and fourth identifier concatenated in A. I've got:

=MID(A1,LEN(B1)+2,???) where ??? is the num_chars that I'm having trouble defining. I thought that by repeating the exercise in column B but using the length of B (+2) as a starting point, the formula would count the next two identifiers- but thus far I've only been able to get that to literally count the length of B (+2) as the number of characters. This is an issue because the unique identifiers will not all be a consistent number of characters.

I'm hoping someone smarter than myself can point me in the right direction. Any thoughts/comments would be appreciated.

Thank you,

Jonathan

 

[mjbeam]

Third identifier:

=LEFT(MID(MID(A1,FIND(",",A1)+1,LEN(A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)))+1,LEN(A1)),FIND(",",MID(MID(A1,FIND(",",A1)+1,LEN(A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)))+1,LEN(A1)))-1)


Fourth identifier:

=LEFT(MID(MID(MID(A1,FIND(",",A1)+1,LEN(A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)))+1,LEN(A1)),FIND(",",MID(MID(A1,FIND(",",A1)+1,LEN(A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)))+1,LEN(A1)))+1,LEN(A1)),FIND(",",MID(MID(MID(A1,FIND(",",A1)+1,LEN(A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)))+1,LEN(A1)),FIND(",",MID(MID(A1,FIND(",",A1)+1,LEN(A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)))+1,LEN(A1)))+1,LEN(A1)))-1)



Not pretty, but it should work.

 

[jbodell]

mjbeam- thanks for the reply. I may not have been clear enough in my original description- I'm looking for a concatenation of the second and third value. Obviously I could accomplish that by concatenating the two formulas you provided, but I had in mind scalability- being able to capture a MID range by identifying (in concept) =MID(A1,LEN(B1)+2,???) where ??? is a count of the LEN of the third and fourth identifier...so that I could adjust that formula to return a segment of the concatenation in A1 based on the quantity of identifiers to return following the number returned in B1 as a starting point. In the end I need:

A: 01,02,03,04,05
B: 01,02
C: 03,04 (based on a quantity of 2 identifiers, in continuation of the number of identifiers in B1 within A1)

I don't mind that the formulas you provided are lengthy- they both work exactly how they should. Thank you again for your help.

 

[jbodell]

In tinkering with this and discussing it with a colleague I solved this for the convoluted purpose of the exercise I defined:

=LEFT(MID(A1,LEN(B1)+2,9000),FIND(CHAR(1),SUBSTITUTE(MID(A1,LEN(B1)+2,9000)&",",",",CHAR(1),2))-1)

Where the 2 towards the end will return the number of identifiers. Entering this as is will return 03,04 where replacing 2 with 3 will return 03,04,05.

Thanks again for your help mjbeam.