Function that returns an array?

MyHanhCB

New Member
Joined
Feb 20, 2023
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
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."
VBA Code:
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
 

Attachments

  • 2.jpg
    2.jpg
    77.2 KB · Views: 18

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
In cell D1 I use =txtJoin(A1:A6,",") and combine Ctrl + Shift + Enter
 
Upvote 0
Does this do what you want?

VBA Code:
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
ABCDE
1123bxcv123bxcv
2112312,vcbcva123112312
3123123123bn,123asdadas,ad123vcbcva123
43123123123bn
5123123123asdadas
6123ad123
73
8123123
9123
10
Sheet1
Cell Formulas
RangeFormula
D1:D9D1=txtJoin(A1:A6,",")
Dynamic array formulas.
 
Upvote 0
Solution
Does this do what you want?

VBA Code:
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
ABCDE
1123bxcv123bxcv
2112312,vcbcva123112312
3123123123bn,123asdadas,ad123vcbcva123
43123123123bn
5123123123asdadas
6123ad123
73
8123123
9123
10
Sheet1
Cell Formulas
RangeFormula
D1:D9D1=txtJoin(A1:A6,",")
Dynamic array formulas.
Why is there still 1 value? Did I do something wrong? Thank you for your support.
 

Attachments

  • 1680065142507.png
    1680065142507.png
    35.8 KB · Views: 20
Upvote 0
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
ABCDE
11231212312
2asdasd12312,sdfsd325264asdasd12312
3123132,fgdgd234523sdfsd325264
4asdasd12312,sdfsd325264123132
5asdasd12312,sdfsd325264fgdgd234523
6asdasd12312
7sdfsd325264
8asdasd12312
9sdfsd325264
10
Sheet2
Cell Formulas
RangeFormula
D1:D9D1=txtJoin(A1:A5,",")
Dynamic array formulas.
 
Upvote 0
Upvote 0
Are you sure you're using the correct code?

Book1
ABCDE
11231212312
2asdasd12312,sdfsd325264asdasd12312
3123132,fgdgd234523sdfsd325264
4asdasd12312,sdfsd325264123132
5asdasd12312,sdfsd325264fgdgd234523
6asdasd12312
7sdfsd325264
8asdasd12312
9sdfsd325264
10
Sheet2
Cell Formulas
RangeFormula
D1:D9D1=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.
 

Attachments

  • 1680065685517.png
    1680065685517.png
    64 KB · Views: 6
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top