Variable data in cell

jfarrackand

New Member
Joined
Oct 9, 2014
Messages
5
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>
 

Some videos you may like

Excel Facts

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

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Would it make more sense to split them out into separate columns? You could use text>columns to split it.
 

jfarrackand

New Member
Joined
Oct 9, 2014
Messages
5
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

 

Watch MrExcel Video

Forum statistics

Threads
1,109,460
Messages
5,528,909
Members
409,847
Latest member
Foster034
Top