Function that returns an array?

[MyHanhCB]

Hello everyone in the forum.
I want in cell "D1" to enter the function, the output will be all values in the array (kq). But when I use the function, the returned value is only 1 value of the array (kq).
Hope you can help. Thanks you.
The result I want to achieve is in column "E"
"I don't know how to turn the sample excel file upside down. So I use temporary images."

Function txtJoin(rng As Range, chs As String)
Dim arr, str, kq(), key As Variant, i&, k&
arr = rng.Value
k = 0
For i = 1 To UBound(arr, 1)
   str = Split(arr(i, 1), chs)
   For Each key In str
        ReDim Preserve kq(k)
    kq(k) = Trim(key)
    k = k + 1
        Next key
            Next i
txtJoin = kq
Erase arr, str, kq
End Function

 

[MyHanhCB]

In cell D1 I use =txtJoin(A1:A6,",") and combine Ctrl + Shift + Enter

 

[kevin9999]

Does this do what you want?

Function txtJoin(rng As Range, chs As String)
    Dim c As Range, s As String, a, b
    For Each c In rng
        s = s & c.Text & chs
    Next c
    a = Split(s, chs)
    ReDim b(1 To UBound(a), 1 To 1)
    For i = LBound(a) To UBound(a) - 1
        b(i + 1, 1) = a(i)
    Next i
    txtJoin = b
End Function

Book1
	A	B	C	D	E
1	123bxcv			123bxcv
2	112312,vcbcva123			112312
3	123123123bn,123asdadas,ad123			vcbcva123
4	3			123123123bn
5	123123			123asdadas
6	123			ad123
7				3
8				123123
9				123
10
Sheet1

Cell Formulas
Range	Formula
D1:D9	D1	=txtJoin(A1:A6,",")
Dynamic array formulas.

 

[MyHanhCB]

Does this do what you want?

Function txtJoin(rng As Range, chs As String)
    Dim c As Range, s As String, a, b
    For Each c In rng
        s = s & c.Text & chs
    Next c
    a = Split(s, chs)
    ReDim b(1 To UBound(a), 1 To 1)
    For i = LBound(a) To UBound(a) - 1
        b(i + 1, 1) = a(i)
    Next i
    txtJoin = b
End Function

Book1
	A	B	C	D	E
1	123bxcv			123bxcv
2	112312,vcbcva123			112312
3	123123123bn,123asdadas,ad123			vcbcva123
4	3			123123123bn
5	123123			123asdadas
6	123			ad123
7				3
8				123123
9				123
10
Sheet1

Cell Formulas
Range	Formula
D1:D9	D1	=txtJoin(A1:A6,",")
Dynamic array formulas.

Why is there still 1 value? Did I do something wrong? Thank you for your support.

 

[kevin9999]

Why is there still 1 value? Did I do something wrong? Thank you for your support.

Are you sure you're using the correct code?

Book1
	A	B	C	D	E
1	12312			12312
2	asdasd12312,sdfsd325264			asdasd12312
3	123132,fgdgd234523			sdfsd325264
4	asdasd12312,sdfsd325264			123132
5	asdasd12312,sdfsd325264			fgdgd234523
6				asdasd12312
7				sdfsd325264
8				asdasd12312
9				sdfsd325264
10
Sheet2

Cell Formulas
Range	Formula
D1:D9	D1	=txtJoin(A1:A5,",")
Dynamic array formulas.

 

[kevin9999]

Try entering the formula with Ctrl + Shift + Enter

Book1
	A	B	C	D	E
1	12312			12312
2	asdasd12312,sdfsd325264			asdasd12312
3	123132,fgdgd234523			sdfsd325264
4	asdasd12312,sdfsd325264			123132
5	asdasd12312,sdfsd325264			fgdgd234523
6				asdasd12312
7				sdfsd325264
8				asdasd12312
9				sdfsd325264
10
Sheet2

Cell Formulas
Range	Formula
D1:D9	D1	=txtJoin(A1:A5,",")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MyHanhCB]

Are you sure you're using the correct code?

Book1
	A	B	C	D	E
1	12312			12312
2	asdasd12312,sdfsd325264			asdasd12312
3	123132,fgdgd234523			sdfsd325264
4	asdasd12312,sdfsd325264			123132
5	asdasd12312,sdfsd325264			fgdgd234523
6				asdasd12312
7				sdfsd325264
8				asdasd12312
9				sdfsd325264
10
Sheet2

Cell Formulas
Range	Formula
D1:D9	D1	=txtJoin(A1:A5,",")
Dynamic array formulas.

ah sorry, I haven't selected the range. Thank you. But is this place required to choose a range?, because I don't know in advance what the total value of the range is? If you select the range including column D, it can slow down the process.

 

[kevin9999]

But is this place required to choose a range?

Yes. That's how the function works, a range is required.

 

[MyHanhCB]

Yes. That's how the function works, a range is required.

Yes, Thank you verymuch

 

[kevin9999]

Yes, Thank you verymuch

You're welcome and thanks for the feedback