Solved

Excel split cell to Rows

Posted on 2016-08-18
9
63 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:Wass_QA
  • 4
  • 3
  • 2
9 Comments
 
LVL 7

Expert Comment

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

Author Comment

by:Wass_QA
Comment Utility
Hello,
Tab 1, before
Tab 2, After

thanks
0
 
LVL 7

Expert Comment

by:tomfarrar
Comment Utility
No attachment.
0
 

Author Comment

by:Wass_QA
Comment Utility
please see attached.
Sample.xlsx
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 17

Expert Comment

by:xtermie
Comment Utility
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
Comment Utility
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
Comment Utility
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:Wass_QA
Comment Utility
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:Wass_QA
Comment Utility
Thank you
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

9 Experts available now in Live!

Get 1:1 Help Now