Concatenate() - modified

[Suril]

The concatenate function or the & operator is pretty straightforward.. However, one of the things which comes out as a limitation to me was this: all the cells have to be referred by sepecting with , and not a range of cells.
eg: cell A1 to cell Z1 have to be concatenated.. I have to refer cell a1, b1, c1....... Instead, I wrote a very simple code to simply refer: A1:Z1

Function concat(rng As range) As String
For Each c In rng
concat = concat & c.Value
Next
End Function

 

[Suril]

I am sure there would be many modifications required to this.. Please make me happy with your criticisms.

 

[Aurelie]

Hi Suril,

You might be interested by a tip Chandoo posted some time ago: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

Is this what you are looking for?

BR

 

[Suril]

Hi @Aurelie,

Thanks! didnt know that chandoo had written a post on this.. that helps a lot.. havent intvestigated it but i just thot wat was the need to have such an elaborate code when this simple code does it all... I guess i may b missing something

 

[Aurelie]

To be honest I don't need this kind of formula so I did not check the code in details, but I would start there because I'm far from being an expert in VBA (not yet ). However, it would be interesting to compare with your code and check if it would work the same way, and if not, why.

Cheers,

 

[Hui]

Suril

The link posted above contains many examples in the Comments section of Concatif with many, many options

 

[Suril]

thanks a lot hui.. concatif is superb

 

[Jon von der Heyden]

Sorry for resurrecting an old thread but I thought I would share my own version of conditional concatenation.

Like many others I agree that Excel doesn't include enough functions in its' library for string manipulation. Generally I'm not a big fan of UDF's but I think this is where UDF's become important.

I had a requirement to perform conditional concatenation and when writing the UDF I thought I'd introduce two things into my code:

1. I want to perform any type of comparison (not just "equals"). Much like the way we utilise the operators with SUMIF(S)/COUNTIF(S)...
2. I want to avoid looping through ranges and/or arrays so as not to write a slow-calculating function; and rather evaluate an array formula that generates the desired array for concatenation (and use the Join function to actually string the values together).

I ended up with this:

Public Function ConcatenateIf(ByVal rngCriteriaRange As Excel.Range, _
                              ByVal varCriteria As Variant, _
                              ByVal rngValues As Excel.Range, _
                              Optional ByVal strDelimiter As String = " ") As Variant
    Dim lngRows As Long, lngCols As Long
    Dim blnErr As Boolean, lngErr As XlCVError
    Dim strCritAddress As String
    Dim strValAddress As String
    Dim varOperators As Variant: varOperators = VBA.Array("=", "<>", ">", "<", ">=", "<=")
    Dim strOperator As String
    Dim varResults As Variant

    With rngCriteriaRange
        lngRows = .Rows.Count
        lngCols = .Columns.Count
    End With

    '#REF! if 2D criteria range is passed
   blnErr = CBool(lngRows > 1 And lngCols > 1)
    If blnErr Then
        lngErr = xlErrRef
        GoTo err_exit
    End If

    '#VALUE! if values range dimension is not the same size and orientation as criteria range
   With rngValues
        blnErr = CBool(lngRows <> .Rows.Count)
        blnErr = CBool(blnErr Or lngCols <> .Columns.Count)
        If blnErr Then
            lngErr = xlErrValue
            GoTo err_exit
        End If
    End With

    '#N/A if the criteria is an array (or more than one cell)
   blnErr = IsArray(varCriteria)
    If blnErr Then
        lngErr = xlErrNA
        GoTo err_exit
    End If

    'Split the operator from the criteria, if an operator has been included
   strOperator = Left$(varCriteria, 2)
    If IsNumeric(Application.Match(strOperator, varOperators, 0)) Then
        varCriteria = Mid$(varCriteria, 3)
    Else
        strOperator = Left$(varCriteria, 1)
        If IsNumeric(Application.Match(strOperator, varOperators, 0)) Then
            varCriteria = Mid$(varCriteria, 2)
        Else
            strOperator = "="
        End If
    End If

    'Make sure the criteria type is correct, and concatenate the operator with the criteria
   If IsDate(varCriteria) Then
        varCriteria = strOperator & CDbl(varCriteria)
    Else
        If IsNumeric(varCriteria) Then
            varCriteria = strOperator & varCriteria
        Else
            varCriteria = strOperator & Chr$(34) & varCriteria & Chr$(34)
        End If
    End If

    'Get the addresses of the criteria and values ranges
   strCritAddress = rngCriteriaRange.Address(external:=True)
    strValAddress = rngValues.Address(external:=True)

    'Construct an array of the results
   If lngRows > 1 Then
        varResults = Evaluate("transpose(if(" & strCritAddress & varCriteria & "," & strValAddress & "))")
    Else
        varResults = Evaluate("if(" & strCritAddress & varCriteria & "," & strValAddress & ")")
    End If

    'Remove non-matching items from the array, and concatenate the remaining items
   varResults = Filter(varResults, False, False)
    ConcatenateIf = Join$(varResults, strDelimiter)

    Exit Function

err_exit:
    ConcatenateIf = CVErr(lngErr)
End Function

Syntax: =ConcatenateIf(criteria_range, criteria, values_range, delimiter)

This function has since served me well which is why I thought this might be a good place to share it. I include this link to my now very-out-of-date blog where the syntax is explained and where samples (and a sample file) can be studied.

 

[bobhc]

Good day Jon

A nice link to your site with a brilliant and clear explanation, will be looking at other parts when time permits.