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,"")&IF(AY8<>"","."&AY8,"")&IF(AY9<>"","."&AY9,"")&IF(AY10<>"","."&AY10,"")&IF(AY11<>"","."&AY11,"")&IF(AY12<>"","."&AY12,"")&IF(AY13<>"","."&AY13,"")&IF(AY14<>"","."&AY14,"")&IF(AY15<>"","."&AY15,"")&IF(AY16<>"","."&AY16,"")&IF(AY17<>"","."&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]

:trophy: