+ Reply to Thread
Results 1 to 2 of 2

How to split a data cell to a number of data in sequence?

Hybrid View

WILLOWGLEN How to split a data cell to a... 11-08-2012, 12:34 AM
Jacc Re: How to split a data cell... 11-10-2012, 12:09 AM
  1. #1
    Registered User
    Join Date
    11-08-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    1

    How to split a data cell to a number of data in sequence?

    i am a basic excel user, please kindly find the attachment and give me some suggestion on solving the problem i met.
    Attached Files Attached Files
    Last edited by WILLOWGLEN; 11-08-2012 at 02:07 AM.

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,883

    Re: How to split a data cell to a number of data in sequence?

    This piece of code does the job. It can sometimes be wise to split the result in two columns(easier to sort) so there is code in there for that too.

    Option Explicit
    Sub SplitData()
    Dim i As Integer, j As Integer, x As Integer, Start As Variant, Stopp As Integer
    Dim AC As Range, SC As Range
    Dim txt As String, StartCell As String
    i = 0
    j = 0
    
    Set AC = Range("A1")
    Set SC = AC.Offset(0, 1)
    While AC.Offset(0, 1).Value <> "" And i < 100
        txt = AC.Offset(0, 1).Value
        Start = Left(txt, InStr(1, txt, "-") - 1)
        Stopp = Right(txt, Len(txt) - InStr(1, txt, "-"))
        For x = Start To Stopp
            'SC.Offset(j, 2).Value = AC.Value
            'SC.Offset(j, 3).Value = x
            SC.Offset(j, 2).Value = AC.Value & " " & x
            j = j + 1
        Next x
        i = i + 1
        Set AC = AC.Offset(1, 0)
    Wend
    End Sub
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1