Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a string of text separated by a semi-colon, maybe 50 separate items.

    I want each item to haveits own separate row.

    How might this be done?

    I found this macro:
    Sub MyReplace2()
    Cells.Replace What:="; ", Replacement:=Chr(13), LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    End Sub

    But it does not separate the items into their own row.

    Thanks, Joe

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This will separate and transpose cell A1

    Code:
    Sub SeparateTransposeA1()
    	Range("A1").TextToColumns Destination:=Range("A1"), _
      	DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
      	ConsecutiveDelimiter:=False, Semicolon:=True
    	Range(Range("A1"), Range("A1").End(xlToRight)).Copy
    	Range("A2").PasteSpecial Transpose:=True
    	Rows(1).Delete
    End Sub

Posting Permissions

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