Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel split cell to Rows

Posted on 2016-08-18
9
Medium Priority
?
96 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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 helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

688 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