• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Excel split cell to Rows

Hello,
can you please help with a macro that can split the trackings I have in Column "AW" into seperate rows.
- remove the _ from the start
- if there is a comma,split the cell and  insert new rows and copy the data to the new row
-  if there is a space ,split the cell and  insert new rows and copy the data to the new row
- if the cell has a number with brackets, split but keep the numbers with brackets and the number after it in one cell.
Please see sample attached.

Your help is much appreciated.
Sample.xlsx
0
W.E.B
Asked:
W.E.B
  • 4
  • 3
  • 2
1 Solution
 
tomfarrarCommented:
Would you provide an example of what you expect the result to look like?
0
 
W.E.BAuthor Commented:
Hello,
Tab 1, before
Tab 2, After

thanks
0
 
tomfarrarCommented:
No attachment.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
W.E.BAuthor Commented:
please see attached.
Sample.xlsx
0
 
xtermieCommented:
This will remove the _ in the front
Sub removetrailingunderscore()
Dim mywb As Workbook
Dim myws_Bef As Worksheet
Dim myws_Aft As Worksheet
Dim myval As String
Dim c As Range
Dim rng As Range

Set mywb = Application.ActiveWorkbook
Set myws_Bef = mywb.Sheets("Before")
Set myws_Aft = mywb.Sheets("After")
Set rng = Range(Cells(2, "AW"), Columns("AW").End(xlDown))
'Remove _ from beginning of AW
For Each c In rng
    c.Value = Right(c.Value, Len(c) - 1)
    'MsgBox c.Value
Next
'Format as number
Columns("AW:AW").Select
Selection.NumberFormat = "0"

End Sub

Open in new window

0
 
xtermieCommented:
Try this to split the strings with the space in between
Sub splitcellatspace()
Dim mywb As Workbook
Dim myws_Bef As Worksheet
Dim myws_Aft As Worksheet
Dim myval As String
Dim c As Range
Dim rng As Range
Dim str1() As String
Dim str2() As String
Dim strX() As String
Dim Sx() As String
Dim avarsplit As Variant

Set mywb = Application.ActiveWorkbook
Set myws_Bef = mywb.Sheets("Before")
Set myws_Aft = mywb.Sheets("After")
Set rng = Range(Cells(2, "AW"), Columns("AW").End(xlDown))

RowCount = 0

For Each c In rng
    c.Activate
    RowCount = RowCount + 1
    avarsplit = c.Value
    If InStr(1, avarsplit, " ") > 0 Then
            Sx = Split(avarsplit, " ")
            If LBound(Sx) - UBound(Sx) <> 0 Then
            For i = LBound(Sx) To UBound(Sx)
               ActiveCell.Offset(i + 1, 0).EntireRow.Insert shift:=xlDown
               c.Offset(i, 0).Value = Sx(i)
            Next
            End If
    End If
Next
End Sub

Open in new window

0
 
xtermieCommented:
and this should do the trick with the comma
Sub splitcellatcomma()
Dim mywb As Workbook
Dim myws_Bef As Worksheet
Dim myws_Aft As Worksheet
Dim myval As String
Dim c As Range
Dim rng As Range
Dim str1() As String
Dim str2() As String
Dim strX() As String
Dim Sx() As String
Dim avarsplit As Variant

Set mywb = Application.ActiveWorkbook
Set myws_Bef = mywb.Sheets("Before")
Set myws_Aft = mywb.Sheets("After")
Set rng = Range(Cells(2, "AW"), Columns("AW").End(xlDown))

RowCount = 0

For Each c In rng
    c.Activate
    RowCount = RowCount + 1
    avarsplit = c.Value
    If InStr(1, avarsplit, ",") > 0 Then
            Sx = Split(avarsplit, " ")
            If LBound(Sx) - UBound(Sx) <> 0 Then
            For i = LBound(Sx) To UBound(Sx)
               ActiveCell.Offset(i + 1, 0).EntireRow.Insert shift:=xlDown
               c.Offset(i, 0).Value = Sx(i)
            Next
            End If
    End If
Next
'Remove , from beginning or end of AW
For Each c In rng
    If InStr(1, c.Value, ",") > 0 Then
        If (Left(c.Value, 1) = ",") Then c.Value = Right(c.Value, Len(c) - 1)
        If (Right(c.Value, 1) = ",") Then c.Value = Left(c.Value, Len(c) - 1)
    End If
Next
End Sub

Open in new window

0
 
W.E.BAuthor Commented:
Hi XTermie,
First code to  removetrailingunderscore
it's eating some numbers from the end.
Example, Row 3
_99999199000290999
becomes
99999199000290900

Second Code to splitcellatspace, splitcellatcomma
it's not coyping the other columns data along with the new rows.

Also,
if the number has brackets, it should stay together with the number following it.
Example Row 8, 9
_9992999090009909999 (9922029) 9909999219919902
Should become
9992999090009909999
(9922029) 9909999219919902

_990099129290, 9900991292999 (9922921) 00299992102229902
Should become
990099129290
9900991292999
 (9922921) 00299992102229902


Thank you very much for your help.
0
 
W.E.BAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now