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

M

mweirath

New Member
Joined
Aug 5, 2016
Messages
28
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]
 
Sort by date Sort by votes
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

[TABLE="width: 1158"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Speciality Concat[/TD]
[TD]SpecialtyCode1[/TD]
[TD]SpecialtyCode2[/TD]
[TD]SpecialtyCode3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Internal Medicine[/TD]
[TD]EF[/TD]
[TD][/TD]
[TD]Internal Medical[/TD]
[/TR]
[TR]
[TD]Pediatrics.Internal Medicine.Family Medicine[/TD]
[TD]Pediatrics[/TD]
[TD]Internal Medicine[/TD]
[TD]Family Medicine[/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD]Family Medicine[/TD]
[TD]EE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Geriatric Primary Care[/TD]
[TD]AG[/TD]
[TD]Geriatric Primary Care[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pediatrics.Family Medicine[/TD]
[TD]Pediatrics[/TD]
[TD][/TD]
[TD]Family Medicine[/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[TD]Family Medicine[/TD]
[/TR]
[TR]
[TD]Pediatrics[/TD]
[TD]Pediatrics[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks again for the responses so far.
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.

[TABLE="width: 1158"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Speciality Concat[/TD]
[TD]SpecialtyCode1[/TD]
[TD]SpecialtyCode2[/TD]
[TD]SpecialtyCode3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Internal Medical[/TD]
[TD]EF[/TD]
[TD][/TD]
[TD]Internal Medical[/TD]
[/TR]
[TR]
[TD]Pediatrics.Internal Medicine.Family Medicine[/TD]
[TD]Pediatrics[/TD]
[TD]Internal Medicine[/TD]
[TD]Family Medicine[/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD]Family Medicine[/TD]
[TD]EE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Geriatric Primary Care[/TD]
[TD]AG[/TD]
[TD]Geriatric Primary Care[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pediatrics.Family Medicine[/TD]
[TD]Pediatrics[/TD]
[TD][/TD]
[TD]Family Medicine[/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[TD]Family Medicine[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pediatrics[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
mweirath said:
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.
Click to expand...

Did you try that formula, it is cocatentating and, at these same time, determining if whether a particular delimiter should be "_" or ""
 
Upvote 0
mikerickson said:
Did you try that formula, it is cocatentating and, at these same time, determining if whether a particular delimiter should be "_" or ""
Click to expand...

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:

[TABLE="width: 1048"]
<tbody>[TR]
[TD="class: xl65, width: 428"]Family Medicine_EE[/TD]
[TD="width: 310"]Family Medicine[/TD]
[TD="width: 310"]EE[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
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))
 
Upvote 0
mweirath said:
Rick Rothstein said:
Can you make use of a UDF (user defined function) which will work no matter how many columns of data there are? If so...
Code: 
[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.
Click to expand...
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...
Code: 
[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]
Click to expand...
 
Upvote 0
mweirath said:
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.

[TABLE="width: 1158"]
<tbody>[TR]
[TD]Speciality Concat[/TD]
[TD]SpecialtyCode1[/TD]
[TD]SpecialtyCode2[/TD]
[TD]SpecialtyCode3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Internal Medical[/TD]
[TD]EF[/TD]
[TD][/TD]
[TD]Internal Medical[/TD]
[/TR]
[TR]
[TD]Pediatrics.Internal Medicine.Family Medicine[/TD]
[TD]Pediatrics[/TD]
[TD]Internal Medicine[/TD]
[TD]Family Medicine[/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD]Family Medicine[/TD]
[TD]EE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Geriatric Primary Care[/TD]
[TD]AG[/TD]
[TD]Geriatric Primary Care[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pediatrics.Family Medicine[/TD]
[TD]Pediatrics[/TD]
[TD][/TD]
[TD]Family Medicine[/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[TD]Family Medicine[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pediatrics[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Family Medicine[/TD]
[TD]Family Medicine[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


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

Again...


[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]
Speciality Concat​
[/td][td]
SpecialtyCode1​
[/td][td]
SpecialtyCode2​
[/td][td]
SpecialtyCode3​
[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td][/td][td]
SC​
[/td][td][/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Internal Medicine​
[/td][td]
EF​
[/td][td][/td][td]
Internal Medical​
[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
Pediatrics.Internal Medicine.Family Medicine​
[/td][td]
Pediatrics​
[/td][td]
Internal Medicine​
[/td][td]
Family Medicine​
[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
Family Medicine​
[/td][td]
Family Medicine​
[/td][td]
EE​
[/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
Geriatric Primary Care​
[/td][td]
AG​
[/td][td]
Geriatric Primary Care​
[/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
Pediatrics.Family Medicine​
[/td][td]
Pediatrics​
[/td][td][/td][td]
Family Medicine​
[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
Family Medicine​
[/td][td]
Family Medicine​
[/td][td][/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
Family Medicine​
[/td][td][/td][td][/td][td]
Family Medicine​
[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
Pediatrics​
[/td][td]
Pediatrics​
[/td][td][/td][td][/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.
 
Upvote 0
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!
 
Upvote 0
mweirath said:
Rick, I love the solution and I am going to see if I can figure out how to rewrite to execute solely in VBA.
Click to expand...
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"))
 
Upvote 0
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!
Click to expand...

You are welcome. Thanks for the feedback.

M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
124
sijpie
sijpie
D
  • Question Question
find populated cells in separate region
Replies
1
Views
341
dappy
D
V
  • Question Question
VBA help - find string between 2nd and 3rd occurrence of a character.
Replies
3
Views
267
DanteAmor
DanteAmor
S
  • Question Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
552
myall_blues
myall_blues
T
  • Solved
Normalizing a Worksheet Output
Replies
2
Views
272
Thomas Scott
T

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top