shorten this formula

[lex_m]

can the formula be shortened

=AQ51&$AP$49&AQ52&$AP$49&AQ53&$AP$49&AQ54&$AP$49&AQ55&$AP$49&AQ56&$AP$49&AQ57&$AP$49&AQ58&$AP$49&AQ59&$AP$49&AQ60&$AP$49&AQ61&$AP$49&AQ62&$AP$49&AQ63&$AP$49&AQ64&$AP$49&AQ65&$AP$49&AQ66

Thanks this is for excel 2003

 

[jasonb75]

Not with native excel functions, but you could use

Function AConcat(a As Variant, Optional Sep As String = "") As String
' Harlan Grove, Mar 2002

    Dim Y As Variant

    If TypeOf a Is Range Then
    
        For Each Y In a.Cells
            AConcat = AConcat & Y.Value & Sep        Next Y
    
    ElseIf IsArray(a) Then
    
        For Each Y In a
            AConcat = AConcat & Y & Sep
        Next Y
        
    Else
    
        AConcat = AConcat & a & Sep
    
    End If
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
End Function

Formula

=AConcat(AQ51:AQ66,$AP$49)

 

[lex_m]

Thanks that will make it alot easier