I have a spreadsheet that I am trying to concatenate 5 columns into 1 column. I have already used some VBA code to change the existing two character codes into string literals. There are some codes that I want to ignore so I left them as two digit characters. I am using a concat formula to merge the cells together. I have a few things I do:
First I check to see if the length of the cell is greater than two, and if so I use the value in that cell.
Second I check the LEN of the two adjacent fields to see if they are greater than 2 and if so I add a . for a delimiter.
This works well unless I have a break in between the cells with values greater than two. I figure I could probably write a massive IF statement to handle this scenario, but I was hoping someone might be able to suggest another approach.
My Code:
=CONCATENATE(IF(LEN(H820)>2,H820,""),IF(AND(LEN(H820)>2,LEN(I820)>2), ".",""),IF(LEN(I820)>2,I820,""),IF(AND(LEN(I820)>2,LEN(J820)>2), ".",""),IF(LEN(J820)>2,J820,""),IF(AND(LEN(J820)>2,LEN(K820)>2), ".",""),IF(LEN(K820)>2,K820,""),IF(AND(LEN(K820)>2,LEN(L820)>2), ".",""),IF(LEN(L820)>2,L820,""))
My problem is with the items in the 5th and 6th row. I am trying to figure out the best way to address that situation. Also the value that shows up in the column could be a variety of things, I have simplified the entry here for demonstrative purposes.
<tbody>
</tbody>
First I check to see if the length of the cell is greater than two, and if so I use the value in that cell.
Second I check the LEN of the two adjacent fields to see if they are greater than 2 and if so I add a . for a delimiter.
This works well unless I have a break in between the cells with values greater than two. I figure I could probably write a massive IF statement to handle this scenario, but I was hoping someone might be able to suggest another approach.
My Code:
=CONCATENATE(IF(LEN(H820)>2,H820,""),IF(AND(LEN(H820)>2,LEN(I820)>2), ".",""),IF(LEN(I820)>2,I820,""),IF(AND(LEN(I820)>2,LEN(J820)>2), ".",""),IF(LEN(J820)>2,J820,""),IF(AND(LEN(J820)>2,LEN(K820)>2), ".",""),IF(LEN(K820)>2,K820,""),IF(AND(LEN(K820)>2,LEN(L820)>2), ".",""),IF(LEN(L820)>2,L820,""))
My problem is with the items in the 5th and 6th row. I am trying to figure out the best way to address that situation. Also the value that shows up in the column could be a variety of things, I have simplified the entry here for demonstrative purposes.
Concat Column | Column1 | Column2 | Column3 |
Value1 | Value1 | AA | |
Value1.Value2 | Value1 | Value2 | |
Value2 | AA | Value2 | |
Value1Value3 | Value1 | Value3 | |
Value1Value3 | Value1 | AA | Value3 |
<tbody>
</tbody>