Variable data in cell

jfarrackand

New Member
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>

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

NeonRedSharpie

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

jfarrackand

New Member
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

Replies
3
Views
181
Replies
12
Views
257
Replies
11
Views
114
Replies
4
Views
84
Replies
1
Views
43