+ Reply to Thread
Results 1 to 4 of 4

VBA Macro to split cells at every ";"

  1. #1
    drews
    Guest

    Cool VBA Macro to split cells at every ";"

    I am currently using this code to find every cell in column b1 and to find the ones that contain a ";" something like "hello;goodbye". The code will split the cell at the ";" and place "goodbye" directly beneath "hello;" on an entirely new row..

    What I need however is this... if a cell contains multiple ";" ("hello;goodbye;yo;hi;hey") it will split at EACH ";" not just the first and then move each to a new row directly beneath the other...

    What changes do I need to make?

    Dim r1 As Range, r2 As Range
    Dim saItem() As String


    For Each r1 In ActiveSheet.Range("B1", Cells(Application.Rows.Count, 2).End(xlUp))
    If InStr(1, r1.Value2, ";") > 0 Then
    saItem = Split(r1.Value2, ";")
    r1 = Trim$(saItem(0)) & ";"
    r1.Offset(1).EntireRow.Insert (xlDown)
    r1.Offset(1) = Trim$(saItem(1))
    End If
    Next r1

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,409

    Re: VBA Macro to split cells at every ";"

    Use SPLIT to get an array of entries using the ; as a delimiter.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: VBA Macro to split cells at every ";"

    drews,

    Please use code tags and not quote tags to surround code in the future. As to your query, something like this should work for you:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    drews
    Guest

    Re: VBA Macro to split cells at every ";"

    edit: Alright, thanks!

    Works perfectly! Thanks Tiger!
    Last edited by drews; 11-22-2011 at 02:27 PM.

+ 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