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.

Concat Column	Column1	Column2	Column3
Value1	Value1	AA
Value1.Value2	Value1	Value2
Value2	AA	Value2
Value1Value3	Value1		Value3
Value1Value3	Value1	AA	Value3

<tbody>
</tbody>

 

[mweirath]

I couldn't figure out how to edit my original post, so here is a better sample of what my data looks like, although this is only 3 columns of values, and I will have 5 in my final data set. And just to clarify I am trying to do the following.

• Concatenate all values in SpecialityCode 1-5 with a period in the Speciality Concat column
• Ignore all blank cells
• Ignore all cells with only 2 characters
• Only place a period when there are values that are being concatenated i.e. I don't want a leading period if there aren't values or double periods for blanks

Speciality Concat	SpecialtyCode1	SpecialtyCode2	SpecialtyCode3
	SC
Internal Medicine	EF		Internal Medical
Pediatrics.Internal Medicine.Family Medicine	Pediatrics	Internal Medicine	Family Medicine
Family Medicine	Family Medicine	EE
Geriatric Primary Care	AG	Geriatric Primary Care
Pediatrics.Family Medicine	Pediatrics		Family Medicine
Family Medicine	Family Medicine
Family Medicine			Family Medicine
Pediatrics	Pediatrics

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

Thanks again for the responses so far.

 

[mweirath]

Morning Jayesh and I apologize for not trying out your response. I thought it required all the columns to be populated, which is a problem for my data set. I just tried it and it almost works perfectly although I don't fully understand the code. I applied it to my data and I am seeing one issue. If the first value (that I want to keep) doesn't have a space in it, then it is ignored. You can see how it ignored the 9th value down in my set.

Speciality Concat	SpecialtyCode1	SpecialtyCode2	SpecialtyCode3
	SC
Internal Medical	EF		Internal Medical
Pediatrics.Internal Medicine.Family Medicine	Pediatrics	Internal Medicine	Family Medicine
Family Medicine	Family Medicine	EE
Geriatric Primary Care	AG	Geriatric Primary Care
Pediatrics.Family Medicine	Pediatrics		Family Medicine
Family Medicine	Family Medicine
Family Medicine			Family Medicine
	Pediatrics
Family Medicine	Family Medicine

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[mikerickson]

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 you try that formula, it is cocatentating and, at these same time, determining if whether a particular delimiter should be "_" or ""

 

[mweirath]

Did you try that formula, it is cocatentating and, at these same time, determining if whether a particular delimiter should be "_" or ""

I did, but it is not ignoring my codes that are 2 characters in length. For example I wouldn't want to see the following:

Family Medicine_EE

Family Medicine

EE

<tbody>
</tbody>

The any two character values should be ignored.

I am almost wondering if I should just figure something out to clear out the two character values from my table prior to trying to do the concatenation since that is causing a ton of issues. I would be okay with adding some VBA code to do that, since I am using VBA to convert my existing codes into string literals already. The problem is that I have a huge number of potential 2 character codes so I didn't want to have to try and write that many change replace statements and I am at risk of it breaking if we add a specialty code in the future.

 

[jayesh25]

No issues.. you're right it isn't working cause i'd kept a condition considering all 5 columns will have values. I've modified that formula to this.

The length is long because the formula is repeated for the purpose of error handling. You can reduce the formula by removing the error handling part but then if there are all values less than len of 2 in a column then it will produce an error.

=IF(ISERROR(RIGHT(IF(LEN(L13)>2,"."&L13,"")&IF(LEN(M13)>2,"."&M13,"")&IF(LEN(N13)>2,"."&N13,"")&IF(LEN(O13)>2,"."&O13,"")&IF(LEN(P13)>2,"."&P13,""),LEN(IF(LEN(L13)>2,"."&L13,"")&IF(LEN(M13)>2,"."&M13,"")&IF(LEN(N13)>2,"."&N13,"")&IF(LEN(O13)>2,"."&O13,"")&IF(LEN(P13)>2,"."&P13,""))-1)),"",RIGHT(IF(LEN(L13)>2,"."&L13,"")&IF(LEN(M13)>2,"."&M13,"")&IF(LEN(N13)>2,"."&N13,"")&IF(LEN(O13)>2,"."&O13,"")&IF(LEN(P13)>2,"."&P13,""),LEN(IF(LEN(L13)>2,"."&L13,"")&IF(LEN(M13)>2,"."&M13,"")&IF(LEN(N13)>2,"."&N13,"")&IF(LEN(O13)>2,"."&O13,"")&IF(LEN(P13)>2,"."&P13,""))-1))

 

[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"]
<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.

The problem was I assume when you wrote Value1, Value2, etc. that you meant numeric values. Try this replacement UDF in place of what I posted earlier...

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

 

[Marcelo Branco]

Morning Jayesh and I apologize for not trying out your response. I thought it required all the columns to be populated, which is a problem for my data set. I just tried it and it almost works perfectly although I don't fully understand the code. I applied it to my data and I am seeing one issue. If the first value (that I want to keep) doesn't have a space in it, then it is ignored. You can see how it ignored the 9th value down in my set.

Speciality Concat	SpecialtyCode1	SpecialtyCode2	SpecialtyCode3
	SC
Internal Medical	EF		Internal Medical
Pediatrics.Internal Medicine.Family Medicine	Pediatrics	Internal Medicine	Family Medicine
Family Medicine	Family Medicine	EE
Geriatric Primary Care	AG	Geriatric Primary Care
Pediatrics.Family Medicine	Pediatrics		Family Medicine
Family Medicine	Family Medicine
Family Medicine			Family Medicine
	Pediatrics
Family Medicine	Family Medicine

<tbody>
</tbody>

Have you not seen the formula i suggested in post #4?

Again...

	A​	B​	C​	D​
1​	Speciality Concat​	SpecialtyCode1​	SpecialtyCode2​	SpecialtyCode3​
2​		SC​
3​	Internal Medicine​	EF​		Internal Medical​
4​	Pediatrics.Internal Medicine.Family Medicine​	Pediatrics​	Internal Medicine​	Family Medicine​
5​	Family Medicine​	Family Medicine​	EE​
6​	Geriatric Primary Care​	AG​	Geriatric Primary Care​
7​	Pediatrics.Family Medicine​	Pediatrics​		Family Medicine​
8​	Family Medicine​	Family Medicine​
9​	Family Medicine​			Family Medicine​
10​	Pediatrics​	Pediatrics​

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]

Thank you Jayesh, Rick, and Marcelo.

Rick, I love the solution and I am going to see if I can figure out how to rewrite to execute solely in VBA. I couldn't figure out how to do it in VBA, so my approach was going to be use VBA to apply a formula to the entire column and then remove the formula from the column, again via VBA. I think with this framework I should be able to rewrite this to execute without having to do the other steps.

Marcelo, thanks for reposting that solution. I did see it, but I missed the simplicity of the formula initially and when I tried to extend it to 5 columns I messed it up. I just redid the formula and it worked perfectly. Thanks for the repost!

 

[Rick Rothstein]

Rick, I love the solution and I am going to see if I can figure out how to rewrite to execute solely in VBA.

The function I posted will work solely in VBA, you just have to pass the range as a VBA range. For example, if the range is cells B2:D2, you would write the function this way (where you can assign it to the variable of your choice)...

SomeVariable = ConcatValues(Range("B2:D2"))

 

[Marcelo Branco]

Marcelo, thanks for reposting that solution. I did see it, but I missed the simplicity of the formula initially and when I tried to extend it to 5 columns I messed it up. I just redid the formula and it worked perfectly. Thanks for the repost!

You are welcome. Thanks for the feedback.

M.