Solved

Excel split cell to Rows

Posted on 2016-08-18
9
68 Views
Last Modified: 2016-08-19
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
Comment
Question by:W.E.B
  • 4
  • 3
  • 2
9 Comments
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41761796
Would you provide an example of what you expect the result to look like?
0
 

Author Comment

by:W.E.B
ID: 41761827
Hello,
Tab 1, before
Tab 2, After

thanks
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41762398
No attachment.
0
 

Author Comment

by:W.E.B
ID: 41762404
please see attached.
Sample.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 17

Expert Comment

by:xtermie
ID: 41762572
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
 
LVL 17

Expert Comment

by:xtermie
ID: 41762623
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
 
LVL 17

Accepted Solution

by:
xtermie earned 500 total points
ID: 41762631
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
 

Author Comment

by:W.E.B
ID: 41762670
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
 

Author Closing Comment

by:W.E.B
ID: 41763340
Thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office 2016 User Guides 5 33
highlight duplicate entry 16 30
Boolean help 6 28
I don't want to use Fn or Fn lock for F1 to F11 key on a Lenovo t460p laptop 13 41
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Outlook Free & Paid Tools
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now