Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel split cell to Rows

Posted on 2016-08-18
9
Medium Priority
?
103 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:W.E.B
ID: 41762404
please see attached.
Sample.xlsx
0
 
LVL 18

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 18

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 18

Accepted Solution

by:
xtermie earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

885 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