Using concatenate function to combine array of numbers

[Arundivakar]

How can i concatenate an array of numbers say A1:A100? I'm getting error as #value ! When i use =concatenate (A1:A100). As I'm not using a latest version of Office, i cannot use textjoin function.

 

[admiral100]

Hi,

Try a Vba code ....to use enter CONCATENATERANGE(A1:A100)

Function CONCATENATERANGE(Ref As Range) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
Result = Result & Cell.Value
Next Cell
CONCATENATERANGE = Left(Result, Len(Result) - 1)
End Function

 

[footoo]

admiral100
The function needs a separator (or change the last line).

 

[admiral100]

Thank's footoo

★ Arundivakar just remove the - 1 or try this (with separator)


Function CONCATENATERANGE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
Result = Result & Cell.Value & Separator
Next Cell
CONCATENATERANGE = Left(Result, Len(Result) - 1)
End Function

 

[footoo]

Here's another way :

Function CONCATENATERANGE(Ref As Range, sep$) As String
CONCATENATERANGE = Join(Application.Transpose(Ref), sep)
End Function

 

[Aladin Akyurek]

How can i concatenate an array of numbers say A1:A100? I'm getting error as #value ! When i use =concatenate (A1:A100). As I'm not using a latest version of Office, i cannot use textjoin function.

Do a search on ACONCAT in this forum as an option.