Determining the placement of a delimiter when concatenating cells with occasional blank values

[mweirath]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Concat Column[/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD]Value1[/TD]
[TD]Value1[/TD]
[TD]AA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value1.Value2[/TD]
[TD]Value1[/TD]
[TD]Value2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value2[/TD]
[TD]AA[/TD]
[TD]Value2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value1Value3[/TD]
[TD]Value1[/TD]
[TD][/TD]
[TD]Value3[/TD]
[/TR]
[TR]
[TD]Value1Value3[/TD]
[TD]Value1[/TD]
[TD]AA[/TD]
[TD]Value3[/TD]
[/TR]
</tbody>[/TABLE]

 

[jayesh25]

Maybe something like this? Considering 5 columns will always have values.

=IF(SUM(LEN(D4&E4&F4&G4&H4))>10,RIGHT(IF(LEN(D4)>2,"."&D4,"")&IF(LEN(E4)>2,"."&E4,"")&IF(LEN(F4)>2,"."&F4,"")&IF(LEN(G4)>2,"."&G4,"")&IF(LEN(H4)>2,"."&H4,""),LEN(IF(LEN(D4)>2,"."&D4,"")&IF(LEN(E4)>2,"."&E4,"")&IF(LEN(F4)>2,"."&F4,"")&IF(LEN(G4)>2,"."&G4,"")&IF(LEN(H4)>2,"."&H4,"")) - 1),"")


[TABLE="width: 441"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Val.Val1.VBB.CCC[/TD]
[TD]Val[/TD]
[TD]Val1[/TD]
[TD]AA[/TD]
[TD]VBB[/TD]
[TD]CCC[/TD]
[/TR]
[TR]
[TD]Val.Val.Val[/TD]
[TD]AA[/TD]
[TD]AA[/TD]
[TD]Val[/TD]
[TD]Val[/TD]
[TD]Val[/TD]
[/TR]
[TR]
[TD]Val.Val.Val[/TD]
[TD]AA[/TD]
[TD]Val[/TD]
[TD]AA[/TD]
[TD]Val[/TD]
[TD]Val[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

If your values have no spaces in them and you want a "." delimiter, you could so something like

=SUBSTITUTE(TRIM(D4&" "&E4&" "&F4&" "&G4&" "&H4), " ", ".")

 

[Marcelo Branco]

Maybe this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Concat Column​

[/td][td]

Column1​

[/td][td]

Column2​

[/td][td]

Column3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Value1​

[/td][td]

Value1​

[/td][td]

AA​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Value1.Value2​

[/td][td]

Value1​

[/td][td]

Value2​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Value2​

[/td][td]

AA​

[/td][td]

Value2​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Value1.Value3​

[/td][td]

Value1​

[/td][td][/td][td]

Value3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Value1.Value3​

[/td][td]

Value1​

[/td][td]

AA​

[/td][td]

Value3​

[/td][/tr]
[/table]

Formula in A2 copied down
=TRIM(SUBSTITUTE(" "&CONCATENATE(IF(LEN(B2)>2,B2,""),IF(LEN(C2)>2,"."&C2,""),IF(LEN(D2)>2,"."&D2,""))," .",""))

Hope this helps

M.

 

[mweirath]

Thanks for the replies, I am away from my computer for right now so I can't test out the solutions. There are spaces in the data so I don't think the substitute solution would work out. Also it is very possible to have a column or multiple columns without data.

 

[mikerickson]

This is a particularly ugly version that works with spaced values
=D1&REPT("_",COUNTA(E1)*SIGN(COUNTA($D1:D1))) & E1&REPT("_",COUNTA(F1)*SIGN(COUNTA($D1:E1))) & F1&REPT("_",COUNTA(G1)*SIGN(COUNTA($D1:F1))) & G1&REPT("_",COUNTA(H1)*SIGN(COUNTA($D1:G1))) & H1

 

[Rick Rothstein]

Can you make use of a UDF (user defined function) which will work no matter how many columns of data there are? If so...

[table="width: 500"]
[tr]
	[td]Function ConcatValues(HorzRng As Range) As String
  ConcatValues = Replace(Application.Trim(Join(Evaluate(Replace("IF(ISNUMBER(@),@,"""")", "@", HorzRng.Address(0, 0))))), " ", ".")
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ConcatValues just like it was a built-in Excel function. For example, pass in a horizontal range of cells (say, B2:F2) and only the numbers in the range will be concatenated together with a dot delimiter

=ConcatValues(B2:F2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[mweirath]

Can you make use of a UDF (user defined function) which will work no matter how many columns of data there are? If so...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ConcatValues(HorzRng As Range) As String
  ConcatValues = Replace(Application.Trim(Join(Evaluate(Replace("IF(ISNUMBER(@),@,"""")", "@", HorzRng.Address(0, 0))))), " ", ".")
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Rick,
Thank you for your reply. I attempted to use the code you supplied, but it generated an error. I don't fully understand what the code was trying to do so I tweaked it slight, but didn't get the appropriate answer.

I have a few things going on with my data:
- Everything is either a text field or blank
- Every non-blank field has either a code (2 char long) or a literal name
- I am trying to ignore the codes that were left

So I tweaked the code slightly to try and ignore values that weren't greater than 2 characters, although now it is just getting everything, adding Zero's for any blank fields, and putting spaces in between all my values.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ConcatValues(HorzRng As Range) As String
   ConcatValues = Replace(Application.Trim(Join(Evaluate(Replace("IF(ISNUMBER(@),@,"""")", "@", HorzRng.Address(0, 0))))), " ", ".")
End Function[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1158"]
<tbody>[TR]
[TD]Speciality Concat[/TD]
[TD]SpecialtyCode1[/TD]
[TD]SpecialtyCode2[/TD]
[TD]SpecialtyCode3[/TD]
[/TR]
[TR]
[TD]SC.0.0.0.0[/TD]
[TD]SC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Family.Medicine.0.0.0.0[/TD]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pediatrics.0.0.0.0[/TD]
[TD]Pediatrics[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Family.Medicine.EE.0.0.0[/TD]
[TD]Family Medicine[/TD]
[TD]EE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pediatrics.0.0.0.0[/TD]
[TD]Pediatrics[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pediatrics.0.0.0.0[/TD]
[TD]Pediatrics[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Family.Medicine.0.0.0.0[/TD]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.0.Family.Medicine.0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD]Family Medicine[/TD]
[/TR]
</tbody>[/TABLE]

 

[mweirath]

This is a particularly ugly version that works with spaced values
=D1&REPT("_",COUNTA(E1)*SIGN(COUNTA($D1:D1))) & E1&REPT("_",COUNTA(F1)*SIGN(COUNTA($D1:E1))) & F1&REPT("_",COUNTA(G1)*SIGN(COUNTA($D1:F1))) & G1&REPT("_",COUNTA(H1)*SIGN(COUNTA($D1:G1))) & H1

Mike,

I am looking at this code and it looks like this is just concatenating the values. One of my challenges is I am trying to add a delimiter between the values that I want to keep. I am going to mock up some better data and add it to my initial question. Right now I could have a field that contains a string literal, contains a code (2 characters), or is blank. I am wanting to ignore all the blanks and code fields and add a delimiter (.) only when I have two string literal values that I am concatenating together.

 

[jayesh25]

Mike,

I am looking at this code and it looks like this is just concatenating the values. One of my challenges is I am trying to add a delimiter between the values that I want to keep. I am going to mock up some better data and add it to my initial question. Right now I could have a field that contains a string literal, contains a code (2 characters), or is blank. I am wanting to ignore all the blanks and code fields and add a delimiter (.) only when I have two string literal values that I am concatenating together.

Did u try this


=IF(SUM(LEN(D4&E4&F4&G4&H4))>10,RIGHT(IF(LEN(D4)>2,"."&D4,"")&IF(LEN(E4)>2,"."&E4,"")&IF(LEN(F4)>2,"."&F4,"")&IF(LEN(G4)>2,"."&G4,"")&IF(LEN(H4)>2,"."&H4,""),LEN(IF(LEN(D4)>2,"."&D4,"")&IF(LEN(E4)>2,"."&E4,"")&IF(LEN(F4)>2,"."&F4,"")&IF(LEN(G4)>2,"."&G4,"")&IF(LEN(H4)>2,"."&H4,"")) - 1),"")


Val.Val1.VBB.CCC Val Val1 AA VBB CCC
Val.Val.Val AA AA Val Val Val
Val.Val.Val AA Val AA Val Val