Concatenate formula

[stevewood1]

Hello,

I have a list of information in a column that I want to concatenate into a single cell. I need a line break between each line of information but the problem that I have is that as I don't know how much information will be entered into the column each time. I have allowed for possible entries in cells A1 to A50 and so I am ending up with a large amount of blank lines in my concatenated cell result as sometimes there will only be information in cells A1 to A10.

I know that I could use a nested IF formula to check if each cell was not blank but due to the number of cells that could possibly have data in them this would seem to be too large and unworkable.

Is there any other way that I can get a line break only if the original cell was not blank?

Many thanks,

Steve

 

[steve the fish]

Do you have TEXTJOIN function?

=TEXTJOIN(CHAR(10),TRUE,A1:A50)

 

[stevewood1]

Sadly not

 

[steve the fish]

Then as far i can think its your first option or a user defined function in VBA. Depends if you can use coding or not.

 

[stevewood1]

I can do little bits in VBA but wouldn't know how to do this.

 

[steve the fish]

This UDF will act in a similar way to TEXTJOIN:

Function TEXTJOIN2(delimiter As String, ignore_empty As Boolean, text_range As Range) As String

Dim c as Range

For Each c In text_range
    If ignore_empty = True Then
        If Len(c) > 0 Then TEXTJOIN2 = TEXTJOIN2 & delimiter & c.Value
    Else
        TEXTJOIN2 = TEXTJOIN2 & delimiter & c.Value
    End If
Next

TEXTJOIN2 = Replace(TEXTJOIN2, delimiter, vbNullString, , 1)

End Function

So add it to a module then in the worksheet you can use what i typed above but i called it TEXTJOIN2.

 

[stevewood1]

That's great thanks

 

[MichaelFerguson]

Would this work for my problem, posted here: https://www.mrexcel.com/forum/excel...lude-cells-no-data.html?highlight=concatenate ???

If so, I'm not sure how to do it. I have some VBA experience, but not sure on this one how I would combine with my existing setup.

Thanks!