Concatenate with a Line Break BUT....

calinini

New Member
Joined
Jul 13, 2015
Messages
9
Hello!

I have a exit interview survey that individuals are able to select multiple reasons as to why they are leaving the company. When I pull the raw data it puts all the reasons they selected into a new column. I would like to put all the reasons into one cell, which I can do using the CHAR(10) function, however if the person did not select a reason, it will still put a line break there.

Example Data:

ABCDEFGH
CHOICES (Column)/EE Name (Row)Better payPromotional position with more pay and/or responsibilityBetter benefits package to include tuition reimbursement, medical, 401k and vacationBetter schedulesLess responsibility for equal pay or more payOther (please specify)<B>RESULTS</B>
EE ABetter schedulesOther (please specify)


Better schedules

Other (please specify)
EE BBetter payPromotional position with more pay and/or responsibilityLess responsibility for equal pay or more pay
Better pay
Promotional position with more pay and/or responsibility


Less responsibility for equal pay or more pay

EE CBetter payPromotional position with more pay and/or responsibilityBetter benefits package to include tuition reimbursement, medical, 401k and vacation
Better pay
Promotional position with more pay and/or responsibility
Better benefits package to include tuition reimbursement, medical, 401k and vacation




[/tr]

My Formula in Col H is:
=CONCATENATE(a1,CHAR(10)&b1,CHAR(10)&c1,CHAR(10)&d1,CHAR(10)&e1,CHAR(10)&f1,CHAR(10)&g1)

As you can see it puts line breaks regardless to if that cell has value.

I would like the results of EE A to be:

Better schedules
Other (please specify)

So if that person didn't select that reason, it will not give it a line break. I have a feeling this is a macro or a VERY long If statement, which I'm not opposed to but I have to manually do this report monthly and I'm tired of copying and pasting. LOL

OR, if someone can tell me how to do a REPLACE function for a line break, that would work too!

Your help is greatly appreciated!

TIA!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
725
hope this helps.

Code:
Sub sample1()
    Dim i As Long, str As String
    Dim rng As Range, C
    
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = Range(Cells(i, 2), Cells(i, 7))
        For Each C In rng
            If C <> "" Then
                str = str & vbCrLf & C
            End If
         Next
            Cells(i, 8).Value = Right(str, Len(str) - 2)
        str = ""
    Next
    
End Sub
 

calinini

New Member
Joined
Jul 13, 2015
Messages
9
Thank you Takae for your response.

I think I might be in a little over my head though. I've created your Macro in excel. I'm unsure of what I need to update in it though to make it work.

In my example, in column H, I want to put all the information in columns B:G for each row respectively.

How do I apply your macro so it will do that? It doesn't ask me which columns I would like to concatenate or anything of that sort.

Your help is appreciated.
 

calinini

New Member
Joined
Jul 13, 2015
Messages
9
Hi Takae, I just realized that your code does exactly what I need. THANK YOU!

As you can imagine my spreadsheet contains more information than my example. In reality, I'm trying to concatenate columns l:V into Column W. If you could show your code with those changes, I can do a comparison and see what you updated to indicate which columns to pull.

Thank you again!! I appreciate your help.
 

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
725
Column A is column H in reality?
Code:
Sub sample1()
Dim i As Long, str As String
Dim rng As Range, C

For i = 2 To Cells(Rows.Count, 8).End(xlUp).Row
Set rng = Range(Cells(i, 9), Cells(i, 22))
For Each C In rng
If C <> "" Then
str = str & vbCrLf & C
End If
Next
Cells(i, 23).Value = Right(str, Len(str) - 2)
str = ""
End Sub
 

Forum statistics

Threads
1,136,434
Messages
5,675,832
Members
419,586
Latest member
RoteichA

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
Top