Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA or Code For split the data

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Posts
    205
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default VBA or Code For split the data

    Hi All,

    Thanks in advance,

    i have a data

    Coloumn 1 Coloumn 2
    2305
    2054
    0654

    now i want to split, but when we split the data, "0" is not coming

    Col 1 Col 2 Col 3 Col 4 Col 5
    2305 2 3 5
    2054 2 5 4
    0654 6 5 4

    pls provide the VBA code/ formula

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA or Code For split the data

    Given first cell (2305) is in A2 try this in B2 and copy across:

    IF(VALUE(MID($A2,COLUMNS($B$1:B1),1))=0,"",MID($A2,COLUMNS($B$1:B1),1))
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    Board Regular
    Join Date
    Mar 2019
    Posts
    205
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA or Code For split the data

    Thanks Tyija1995

  4. #4
    Board Regular
    Join Date
    Mar 2019
    Posts
    205
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA or Code For split the data

    Still 0 is not coming

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,347
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA or Code For split the data

    How about
    ABCDEF
    1
    21230512305
    320542054
    406540654

    Data



    Worksheet Formulas
    CellFormula
    B2=MID($A2,COLUMNS($B$1:B1),1)

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    270
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA or Code For split the data

    And what about
    Code:
    Sub test()
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To lr
            For j = 1 To Len(Cells(i, 1))
                 Cells(i, j).Offset(0, 1) = Mid(Cells(i, 1), j, 1)
            Next
        Next
    End Sub

  7. #7
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,677
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA or Code For split the data

    Quote Originally Posted by tyija1995 View Post
    given first cell (2305) is in a2 try this in b2 and copy across:

    If(value(mid($a2,columns($b$1:b1),1))=0,"",mid($a2,columns($b$1:b1),1))
    Code:
    if(value(mid($a2,columns($b$1:b1),1))=0,"0",text(mid($a2,columns($b$1:b1),1)))
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  8. #8
    Board Regular
    Join Date
    Mar 2019
    Posts
    205
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA or Code For split the data

    thanks Fluff
    bro,

    great its working
    Last edited by Vishaal; Aug 26th, 2019 at 01:17 AM. Reason: forget the mention name

  9. #9
    Board Regular
    Join Date
    Mar 2019
    Posts
    205
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA or Code For split the data

    thanks mohadin bro,

    its easy and fast

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,347
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA or Code For split the data

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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