Something we have to divide some text value for two separated elements. VBA code contained to this post show how to do this. Of course shown code is quite simple – it has to be rebuilt for yours needs.
First of all following values have to be added to sheet – started from A1 cells:
Thanks to this code text value from column A will be divided for two next columns such as B and C.
Solution 1: function InStr has to be used to return space number position in text value. Next with other text functions separated values are added to next columns:
Sub SpaceSearching() ' Source: https://www.techonthenet.com/excel/formulas/instr.php Dim SpaceNo, i As Long Dim Value1, Value2 As String Dim CellValueDividing As Long With ThisWorkbook.ActiveSheet For i = 2 To 4 SpaceNo = InStr(1, .Cells(i, 1), " ") Value1 = Mid(.Cells(i, 1), SpaceNo, Len(.Cells(i, 1)) - SpaceNo + 1) Value2 = Left(.Cells(i, 1), SpaceNo - 1) .Cells(i, 2) = Value2 .Cells(i, 3) = Value1 Next i End With End Sub
Solution 2: an array as a storage for result of SPLIT function:
Sub ArraySpaceSearching() Dim TempArray() As String Dim i As Long With ThisWorkbook.ActiveSheet For i = 2 To 4 TempArray() = Split(.Cells(i, 1), " ") .Cells(i, 2) = TempArray(0) .Cells(i, 3) = TempArray(1) Erase TempArray Next i End With End Sub