# OzGrid

How to convert split formula in vba in their respective columns

Category: [Excel]  Demo Available

### How to convert split formula in VBA in their respective columns

Requirement:

The user has a sheet where the user needs to split columns A into 2 distinct columns in B and C that is there reference will be in columns B and the serial no in column C ,.

How to convert the actual formula in VB to last data row as it consists of 15000 rows.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148882-convert-split-formula-in-vba-in-their-respective-columns

Solution:

Code:
```Option Explicit

Sub Macro1()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & lr).TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub```

OR

An alternative method

Code:
```Sub SplitReference()
Dim x, y(), i As Long

With Sheets("INVOICE SPLIT")
x = .Cells(1).CurrentRegion
For i = 2 To UBound(x, 1)
ReDim Preserve y(1 To 2, 1 To i - 1)
y(1, i - 1) = Split(x(i, 1), "/")(0): y(2, i - 1) = Split(x(i, 1), "/")(1)
Next
.[b2].Resize(UBound(y, 2), UBound(y, 1)) = Application.Transpose(y)
End With

End Sub```

Obtained from the OzGrid Help Forum.

Solution provided by AlanSidman and KjBox.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

### Gallery

stars (0 Reviews)