Variable data in cell

[jfarrackand]

Hello, I have a doozy of a question.
I have multiple cells with this type of data

aaaaa;bbbbb;ccccc;

ddddd;eeeee

bbbbb;nnnnn;hhhhh;qqqqq I have to perform a lookup on each 5 character code and the cells can hold up to 14 five character codes Here is what I have so far =IF(LEN(A1>4),INDEX($C:$C,MATCH(MID(A1,1,5),$D:$D,0)),"")&","&IF(LEN(A1>10),INDEX($C:$C,MATCH(MID(A1,7,5),$D:$D,0)),"")&","&IF(LEN(A1>16),INDEX($C:$C,MATCH(MID(A1,13,5),$D:$D,0)),"") This formula will only work if the cell has 3 or more codes I was trying to setup the formula to give a blank if the length was greater len(A1) - 2 Any help?

<colgroup><col></colgroup><tbody>
</tbody>

 

[NeonRedSharpie]

Would it make more sense to split them out into separate columns? You could use text>columns to split it.

 

[jfarrackand]

It would make sense to split them into separate columns but the actual data source is a dynamic 100 rows by 100 columns.
Each cell can be filled or not filled (and of variable length)

I found the culprit
=IF(LEN(A1>4)
should be
=IF(LEN(A1)>4