Code help required

[Rohith1324]

Hi,

I have one spreadsheet with multiple lines ( in thousands )

In total I have 10 columns( headers ) and many rows. Each row will be unique.

In one of the column user have an option to enter multiple values, so they update it like 5555565657/5786543789/5654321678

Sometime they have used the separator "/", "&", ","...

My requirement is for the value after each separator should have one seperate line with same row line details for the 3 lines except this cell value... For example in the above example I have 3 values so by running macro excel should split that into 3 lines.

5555565657
5786543789
5654321678

Regards,
Rohith

 

[Akuini]

Even after changing it to variant just it is removing special characters from my data and nothing else

That's strange. Here's an example:

Book1
	E
2	5555565657/5786543789/5654321678
3	5555565657&5786543789,5654321678
Sheet4

result:

Book1
	E
2	5555565657 5786543789 5654321678
3	5555565657 5786543789 5654321678
Sheet4

 

[JLGWhiz]

This should fix the blank cell problem

Sub t()
Dim col As Long, c As Range, i As Long, spl As Variant
col = 5
With ActiveSheet
    For Each c In Intersect(.UsedRange, .Cells(2, col).Resize(.UsedRange.Rows.Count - 1))
        If c <> "" Then
        For i = 1 To Len(c.Value)
            If Not IsNumeric(Mid(c.Value, i, 1)) Then
                c.Replace Mid(c.Value, i, 1), " "
            End If
        Next
        spl = Split(c, " ")
        c.EntireRow.Copy
        If UBound(spl) > 0 Then
            c.Offset(1).Resize(UBound(spl)).EntireRow.Insert
        End If
        Application.CutCopyMode = False
        c.Resize(UBound(spl) + 1) = Application.Transpose(spl)
        End If
     Next
End With
End Sub

 

[Rohith1324]

That's strange. Here's an example:

Book1
	E
2	5555565657/5786543789/5654321678
3	5555565657&5786543789,5654321678
Sheet4

result:

Book1
	E
2	5555565657 5786543789 5654321678
3	5555565657 5786543789 5654321678
Sheet4

Thanks for your reply....but may be i was not clear while writing my issues.

the requrirement for me was to create one new line

That's strange. Here's an example:

Book1
	E
2	5555565657/5786543789/5654321678
3	5555565657&5786543789,5654321678
Sheet4

result:

Book1
	E
2	5555565657 5786543789 5654321678
3	5555565657 5786543789 5654321678
Sheet4

sorry my requirement is to split in the lines in different rows

 

[Rohith1324]

This should fix the blank cell problem

Sub t()
Dim col As Long, c As Range, i As Long, spl As Variant
col = 5
With ActiveSheet
    For Each c In Intersect(.UsedRange, .Cells(2, col).Resize(.UsedRange.Rows.Count - 1))
        If c <> "" Then
        For i = 1 To Len(c.Value)
            If Not IsNumeric(Mid(c.Value, i, 1)) Then
                c.Replace Mid(c.Value, i, 1), " "
            End If
        Next
        spl = Split(c, " ")
        c.EntireRow.Copy
        If UBound(spl) > 0 Then
            c.Offset(1).Resize(UBound(spl)).EntireRow.Insert
        End If
        Application.CutCopyMode = False
        c.Resize(UBound(spl) + 1) = Application.Transpose(spl)
        End If
     Next
End With
End Sub

Sorry I missed to say that I also have the text in between:

here is the data I'm having:

where ever there is blanks...it will remain as is and also where ever it is written no code assigned even that line will stay as it is. ( no new lines required for those ) only where the codes are there this task to be performed.

Codes
5209000029
5206700006&5206000561
5201000489
5228000243&5228000244
5205008313
5232000705
5232000967
5201000328&5201000329&5201000330&5201000331
5201000328&5201000329&5201000330&5201000331
5201000341&5201000342&5201000343&5201000344&5201000345&5201000346&5201000347&5201000348
5201000336&5201000337&5201000360&5201000361&5201000362&5201000363
5201000336&5201000337&5201000360&5201000361&5201000362&5201000363
5232000752
5232000982
5232000982
No Code assigned
5205007766
5205007886&5205007887&5205007889
5215004013
5215004010

 

[JLGWhiz]

Sorry I missed to say that I also have the text in between:

]
You could move your "No Cose assigned" statement to a different column and the procedure should then work OK. I am not sure how I can modify the code to ignore the text entries, because all the cells with delimiters are also seen by Excel as text, event though they contain numbers. Otherwise, that is the best i can offer for a solution.

 

[Rohith1324]

]
You could move your "No Cose assigned" statement to a different column and the procedure should then work OK. I am not sure how I can modify the code to ignore the text entries, because all the cells with delimiters are also seen by Excel as text, event though they contain numbers. Otherwise, that is the best i can offer for a solution.

Thank you so much...i will clean my data and then use this code...

 

[JLGWhiz]

Thank you so much...i will clean my data and then use this code...

You're welxome,
regards, JLG