splitting a string text into separate rows in VBA

Question

I have 2 text boxes in a excel (or csv file) as below: text box 1 contains (#11111,#22222,#33333), text box 2 contains (#55555)

#11111,#22222,#33333  #55555

I want the text between , to be on 3 different rows and repeat the text in 2nd text box so that it looks like below:

#11111   #55555
#22222   #55555
#33333   #55555

I am new to VBA. I am reading about string functions but I can't come up with logic on how to do it.

Any help would be appreciated.

Hi @tim williams - Thanks for the advice. I did manage to write a short code which accomplishes the task but it overwrites the text if I have any in 2nd row and 3rd row.

 Sub splitcells()
 Dim txt As String
 Dim txt2 As String

 Dim i As Integer
 Dim cell1 As Variant

 txt = Range("a1", "A1").Value
 cell1 = Split(txt, ",")
 For i = 0 To UBound(cell1)
 Cells(i + 1, 1).Value = cell1(i)
 Next i

 txt2 = Range("b1", "b1")
 For i = 1 To UBound(cell1)
 Cells(i + 1, 2).Value = txt2
 Next i

 End Sub

Any advice on how to push the data on row 2 downwards .....

Solution

I do not know how to give you a hint that would help you adjust your macro so I have coded what I think you are after.

You talk about overwriting data in the 2nd or 3rd row so I assume you have several rows containing data in this format. I have therefore converted your code into a loop that works down column A until it finds a blank row.

I avoid overwriting data below the current row by inserting rows as necessary.

I have changed your code in ways that I believe makes the code more maintainable. I have explained my reasons for these changes.

I have not explained the new statements I have used. It is generally easy to look up a statement once you know it exists but do ask questions if anything is unclear.

I hope this helps.

Option Explicit
Sub splitcells()

  ' * With VBA, Integer declares a 16-bit value while Long declares a 32-bit
  '   value. 16-bit values require special processing and are slower. So
  '   Long is preferred.
  ' * I do not like variable names such as i.  It does not really matter with
  '   a tiny macro but with a larger macro it does.  It does not matter now
  '   but it matters when you return to this macro in 6 or 12 months to amend
  '   it.  You want to be able to look at variables and immediately know what
  '   they are.  I have named variables according to my system.  I am not
  '   asking you to like my system but to have a system.  I can return to
  '   macros I wrote years ago and immediately recognise all the variables.
  Dim InxSplit As Long
  ' Dim i As Integer

  ' * Split returns a string array.  A Variant can be hold a string array but
  '   access is slower.  Variants can be very useful but only use then when
  '   you need the flexibility they offer.
  Dim SplitCell() As String
  ' Dim cell1 As Variant

  Dim RowCrnt As Long

  ' * "Range" operates on the active worksheet.  You are relying on the correct
  '   worksheet being active when the macro is called.  Also, when you return
  '   to the macro in 6 or 12 months will you remember which worksheet is
  '   supposed to be active.  ".Range" operates on the worksheet specified in
  '   the With statement.  It doe not matter which worksheet is active and it
  '   is absolutely clear which worksheet is the target of this code.
  With Worksheets("Sheet1")

    RowCrnt = 1         ' The first row containing data.

    Do While True

      ' * I use .Cells(row, column) rather than .Range because it is more
      '   convenient when you need to change the row and/or column numbers.
      ' * Note the column value can be a number or a column identifier.
      '   A = 1, B=2, Z=26, AA = 27, etc.  I am not doing arithmetic with
      '   the columns so I have used "A" and "B" which I find more
      '   meaningful than 1 and 2.
      If .Cells(RowCrnt, "A").Value = "" Then
        Exit Do
      End If

      SplitCell = Split(.Cells(RowCrnt, "A").Value, ",")

      If UBound(SplitCell) > 0 Then
        ' The cell contained a comma so this row is to be spread across
        ' two or more rows.
        ' Update the current row
        .Cells(RowCrnt, "A").Value = SplitCell(0)

        ' For each subsequent element of the split value, insert a row
        ' and place the appropriate values within it.
        For InxSplit = 1 To UBound(SplitCell)
          RowCrnt = RowCrnt + 1
          ' Push the rest of the worksheet down
          .Rows(RowCrnt).EntireRow.Insert
          ' Select the appropriate part of the original cell for this row
          .Cells(RowCrnt, "A").Value = SplitCell(InxSplit)
          ' Copy the value from column B from the previous row
          .Cells(RowCrnt, "B").Value = .Cells(RowCrnt - 1, "B").Value
        Next
      End If

      RowCrnt = RowCrnt + 1

    Loop

  End With

 End Sub

Comments

[Admin]

HI - Thanks a lot for the help. The code works flawlessly. And I really appreciate you providing such a neat example with all the comments and explanations. Regards - MB

[Alan Treanor]

Hi @TonyDallimore Is there a simple way of converting this code to past information into columns, adding columns along the way if there is text in the next column if this makes sense? E.G. Split Column A based on Char "|" then put text in Column B if it is empty then Add a column if Column C has content. I will be getting this to loop through each Row and not just 1 row.

[Tony Dallimore]

@AlanTreanor I do not have a clear understanding of what you want to do. However, consider ColCrnt = .Cells(RowCrnt, .Columns.Count).End(xlToLeft).Column If the row is empty, this sets ColCrnt to 1. If the row is not empty, it sets ColCrnt to the number of the last cell with a value. Follow with If .Cells(RowCrnt, ColCrnt).Value <> "" Then ColCrnt = ColCrnt + 1 and ColCrnt will identify the first empty cell after any used cells. The first statement is the VBA equivalent of placing the cursor in the rightmost column of a row and clicking Ctrl+Left. Try it.