VB code to convert delimited text to carriage returns

[brunotripod]

I have an Excel table that is a linked list to SharePoint. When data from a multiple choice field in the list syncs to Excel, the multiple choice values appear as a single line of text delimited by ";#" in the Excel table cells. I would like to convert the delimited text to separate lines in the cell (carriage returned). How do I write the VB code to loop through each cell in the table column array and convert the text?

Example: for each sales opportunity in a SharePoint list, a sales rep picks any combination of sales activities from multiple choice options. The data comes into Excel like this:

[Sales Opportunity] [Activity]
["Customer1"] ["Activity1;#Activity2;#Activity3"]
["Customer2"] ["Activity2;#Activity5;#Activity7"]

I would love to have code that loops through the [Activity] column and changes the cell contents to:

[Sales Opportunity] [Activity]
["Customer1"] ["Activity1
Activity2
Activity3"]
["Customer2"] ["Activity2
Activity5
Activity7"]

 

[Ralajer]

Welcome to the board, brunotripod.

Sub deliminatorSwap()

    Dim cell As Range, rngActivity As Range
    Dim deliminator as String
    Dim replaceWith as String

    deliminator = ";#"
    replaceWith = Chr(10)           '// Chr(10) == Carriage Return

    '// Assumes Activity is in Column B Starting at Row 2
    '// Change as needed
    Set rngActivity = Range(Range("B2"), Range("B2").End(xlDown))

    '// Loop through each cell in the range
    For Each cell In rngActivity
        '// Replaces all occurrences of the deliminator in the cell
        cell.Value = Replace(cell.Value, deliminator, replaceWith)
    Next cell
End Sub

The code should be self explanatory but if you have questions or problems let me know.

 

[brunotripod]

Thank you Rob, I appreciate your help. Your explanation is very clear.