Concatenate Values with UDF

[RINCONPAUL]

Hi,
I want to replace this formula with a User Defined Function, but it places a "." between values, not blanks?

=AY5&IF(AY6<>"","."&AY6,"")&IF(AY6<>"","."&AY6,"")&IF(AY7<>"","."&AY7,"")&I F(AY8<>"","."&AY8,"")&IF(AY9<>"","."&AY9,"")&IF(AY10<>"","."&AY10,"")&IF(AY 11<>"","."&AY11,"")&IF(AY12<>"","."&AY12,"")&IF(AY13<>"","."&AY13,"")&IF(AY 14<>"","."&AY14,"")&IF(AY15<>"","."&AY15,"")&IF(AY16<>"","."&AY16,"")&IF(AY 17<>"","."&AY17,"")&IF(AY18<>"","."&AY18,"")

Cheers

[ashleyuk1984]

Hi,
Would be easier to see what that formula is doing if we had some dummy data.
Can you upload a simple workbook with the formula working on some data.

Thanks

[RINCONPAUL]

numbers in column with blank cells between 6&7, 7&8 9&7 : 1 4 6 7 8 9 7

Result of concatenate will be: 1.4.6.7.8.9.7

[ashleyuk1984]

This makes it even more confusing. I'm trying to visualise this in my head, but can't. Just upload a worksheet.
Remember that your talking to people that have no idea of your logic or what your trying to achieve, so saying this to us:

6&7, 7&8 9&7 : 1 4 6 7 8 9 7

Makes no sense at all.

We need to visualise it, in a form of a worksheet.

[offthelip]

try this:

Function conc(inarr As Range)
Dim txtout As String


  colcnt = inarr.Columns.Count
   txtout = ""
   If colcnt > 1 Then
     For i = 1 To colcnt
      If inarr(1, i) <> "" Then
        txtout = txtout & inarr(1, i) & "."
      End If
     Next i
    End If
conc = txtout
 End Function

[SamT]

The only changes I would make to offthelip's fine code are

Function conc(inarr As Range) 
    Dim txtout As String   
    Dim colcnt as Long 
Dim i As Long
     
    colcnt = inarr.Columns.Count 
    If colcnt > 0 Then '<---
        For i = 1 To colcnt 
            If IsNumeric(inarr(1, i)) Then
If CLng(inarr(1, i)) > 0 Then txtout = txtout & inarr(1, i) & "." '<---
End If
         Next i 
    End If 
    conc = Left(txtout, Len(txtout) - 1) '<---
End Function

[mikerickson]

This ConcatIf function has served me well. Your formula would look like
=ConcatIf(1:1, "<>", 1:1, ".")
The "<>" means not equal to the empty string.
note that the Delimiter is an optional argument that defaults to a space.

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
     
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

[mdmackillop]

If you have Excel 2016 look at TEXTJOIN
=TEXTJOIN(" ",,A1:H1)

[snb]

Otherwise:

=F_snb(AY6:AY18)

Function F_snb(sn)
   F_snb=join(application.transpose(sn))
End Function

[shrivallabha]

Using standard CONCATENATE,TRIM and SUBSTITUTE you can get rid of big IF loop like below:
=SUBSTITUTE(TRIM(CONCATENATE(AY5," ",AY6," ",AY6," ",AY7," ",AY8," ",AY9," ",AY10," ",AY11," ",AY12," ",AY13," ",AY14," ",AY15," ",AY16," ",AY17," ",AY18))," ",".")

[RINCONPAUL]

WOW!! Overwhelmed with all the solutions offered. The prize goes to mdmackillop IMHO! Without using a UDF, he's nailed it with a very short function. Honourable mentions to all the others though, many thanks.

[mdmackillop]