splitting an array

newbie777

New Member
Joined
Oct 17, 2018
Messages
24
Hello everyone,

I have an array that has 100 values. The values are text1 & "|" & text2.

I need to split this array into two columns. Text 1 in column X and text2 in column Y.

I failed to do it. Can you please help?

Thanks!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
My code is as below:

On Error Resume Next


'adds


For I = 1 To UBound(Arr1)
For J = 1 To UBound(arr2)
If Arr1(I, 1) = arr2(J, 1) Then
Exit For

End If
Next J
If J >= UBound(arr2) Then
ReDim Preserve MS(0 To K)
MS(K) = Arr1(I, 1)
K = K + 1
End If
Next I
LastR3 = sht5.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
sht5.Range("B" & LastR3 + 1).Resize((UBound(MS) - LBound(MS) + 1), 1).Value = Application.Transpose(MS)


For I = 1 To UBound(Arr1)
For J = 1 To UBound(arr2)


'drops
If arr2(J, 1) = Arr1(I, 1) Then
Exit For

End If
Next J
If I >= UBound(Arr1) Then
ReDim Preserve MV(0 To K)
MS(v) = arr2(J, 1)
K = K + 1
End If
Next I
LastR3 = sht5.Cells(Cells.Rows.Count, "A").End(xlUp).Row
sht5.Range("B" & LastR3 + 1).Resize((UBound(MS) - LBound(MS) + 1), 1).Value = Application.Transpose(MS)
On Error GoTo 0




The data inside the array is a text&|& number. For example GSIS|89652

I want to put the number in column Y and the text in Column Z instead of copying MS array including the delimiter.

Any help please? Am stuck for 2 days in this.

THanks,
 
Last edited:
Upvote 0
Here is one way. Run in a test workbook - values are split and placed in A1:B4
Code:
Sub SplitArray()
    Dim MS(3, 1) As String, X(3), Y(3), a As Long
'populate array
    MS(0, 1) = "abc|def"
    MS(1, 1) = "ghi|jkl"
    MS(2, 1) = "mno|pqr"
    MS(3, 1) = "stu|vwx"
'split
    For a = 0 To UBound(MS)
        X(a) = Split(MS(a, 1), "|")(0)
        Y(a) = Split(MS(a, 1), "|")(1)
    Next
'write to sheet
    With Range("A1").Resize(UBound(X) + 1)
        .Value = Application.Transpose(X)
        .Offset(, 1).Value = Application.Transpose(Y)
    End With
End Sub
 
Upvote 0
Many thanks for your reply.

I have two questions if you may,

1. Why a is long and not integer? Ps. The amount of data shouldnt be more than 2000
2. Can we make it dynamic, instead of 0 1 2 3. As i mentioned above it can be a lot of data.


Appreciating your help on this. My project is pending this &&^%$ step that I failed to do.

Thanks again!
 
Upvote 0
Why a is long and not integer? Ps. The amount of data shouldnt be more than 2000
Long and Integer are the same except that Long can deal with bigger numbers

from the horse's mouth Three data types in VBA can represent integers, or whole numbers: the Integer, Long, and Byte data types. Of these, the Integer and Long types are the ones you're most likely to use regularly.
The Integer and Long data types can both hold positive or negative values. The difference between them is their size: Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647. Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. In recent versions, however, VBA converts all integer values to type Long, even if they're declared as type Integer. So there's no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them.

The Byte data type can hold positive values from 0 to 255. A Byte variable requires only a single byte of memory, so it's very efficient. You can use a Byte variable to hold an Integer value if you know that value will never be greater than 255. However, the Byte data type is typically used for working with strings. For some string operations, converting the string to an array of bytes can significantly enhance performance.
 
Last edited:
Upvote 0
Can we make it dynamic, instead of 0 1 2 3. As i mentioned above it can be a lot of data

Code amended to make X and Y dynamic in size

Code:
Sub SplitArray()
    Dim MS(3, 1) As String
'populate array
    MS(0, 1) = "abc|def"
    MS(1, 1) = "ghi|jkl"
    MS(2, 1) = "mno|pqr"
    MS(3, 1) = "stu|vwx"

'split
   [COLOR=#008080] Dim X() As String, Y() As String[/COLOR], a As Long
    
[COLOR=#008080]    ReDim X(UBound(MS))
    ReDim Y(UBound(MS))[/COLOR]
    For a = 0 To UBound(MS)
        X(a) = Split(MS(a, 1), "|")(0)
        Y(a) = Split(MS(a, 1), "|")(1)
    Next
'write to sheet
    With Range("A1").Resize(UBound(X) + 1)
        .Value = Application.Transpose(X)
        .Offset(, 1).Value = Application.Transpose(Y)
    End With
End Sub
 
Upvote 0
Avoid repeated calculation of Ubound with variable uBnd

Code:
Sub SplitArray()
    Dim MS(3, 1) As String
'populate array
    MS(0, 1) = "abc|def"
    MS(1, 1) = "ghi|jkl"
    MS(2, 1) = "mno|pqr"
    MS(3, 1) = "stu|vwx"

'split
    Dim X() As String, Y() As String, a As Long, [COLOR=#008080]uBnd[/COLOR] As Long
    [COLOR=#008080]uBnd[/COLOR] = UBound(MS)
    ReDim X([COLOR=#008080]uBnd[/COLOR])
    ReDim Y([COLOR=#008080]uBnd[/COLOR])
    For a = 0 To [COLOR=#008080]uBnd[/COLOR]
        X(a) = Split(MS(a, 1), "|")(0)
        Y(a) = Split(MS(a, 1), "|")(1)
    Next
'write to sheet
    With Range("A1").Resize([COLOR=#008080]uBnd[/COLOR] + 1)
        .Value = Application.Transpose(X)
        .Offset(, 1).Value = Application.Transpose(Y)
    End With
End Sub
 
Last edited:
Upvote 0
I will try this out once i get back to the office!

Many thanks for your help. I really appreciate it.

I hope i will be able to help others in VBA one day :)
 
Upvote 0
Hi Yongle,

It didnt work. I get subscript out of range error.

I think because int he declaration, you declared MS(3,1) while I have it MS() to make it expandable.

Any idea to overcome this issue?

Many thanks.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,637
Latest member
Ezio2866

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