replace the comas in strings that separate substrings to pipes using VBA

[userxyz777]

Hello,

I have text cells in a column that comes from a checkbox in a survey. the checkbox has an option list that people can select as many of the options as they want

Example of option list of checkbox

[TABLE="width: 266"]
<tbody>[TR]
[TD]Advocacy organizations[/TD]
[/TR]
[TR]
[TD]Business community - Small and Medium-sized[/TD]
[/TR]
[TR]
[TD]Clergy members[/TD]
[/TR]
[TR]
[TD]Community leaders[/TD]
[/TR]
[TR]
[TD]Contractors and developers[/TD]
[/TR]
[TR]
[TD]Corporations[/TD]
[/TR]
[TR]
[TD]Doctors and other medical professionals[/TD]
[/TR]
[TR]
[TD]Financial Institutions[/TD]
[/TR]
[TR]
[TD]Funders[/TD]
[/TR]
[TR]
[TD]Governors, mayors, city/town councilors, selectmen, etc.[/TD]
[/TR]
[TR]
[TD]Health and human service organizations and their line staff – youth workers, welfare case workers, etc.[/TD]
[/TR]
[TR]
[TD]Landlords[/TD]
[/TR]
[TR]
[TD]Local board members[/TD]
[/TR]
[TR]
[TD]Media organizations[/TD]
[/TR]
[TR]
[TD]Non-Prot community[/TD]
[/TR]
[TR]
[TD]Police and other law or regulation enforcement agencies[/TD]
[/TR]
[TR]
[TD]Policy makers[/TD]
[/TR]
[TR]
[TD]Researchers[/TD]
[/TR]
[TR]
[TD]Schools - teachers, counselors, aides, etc.[/TD]
[/TR]
[TR]
[TD]Social workers and psychotherapists[/TD]
[/TR]
[TR]
[TD]Other[/TD]
[/TR]
</tbody>[/TABLE]

the cell values are coma delimited

Example of a cell value:

Advocacy organizations, Business community - Small and Medium-sized, Clergy members, Community leaders, Contractors and developers, Corporations, Doctors and other medical professionals, Financial Institutions, Funders, Governors, mayors, city/town councilors, selectmen, etc., Health and human service organizations and their line staff – youth workers, welfare case workers, etc., Local board members, Media organizations, Non-Profit community, Police and other law or regulation enforcement agencies, Policy makers, Researchers, Schools - teachers, counselors, aides, etc., Social workers and psychotherapists

I need to find and replace the comas in text that separate the option to pipes |

Example of what I need

Advocacy organizations|Business community - Small and Medium-sized|Clergy members|Community leaders|Contractors and developers|Corporations|Doctors and other medical professionals|Financial Institutions|Funders|Governors, mayors, city/town councilors, selectmen, etc.|Health and human service organizations and their line staff – youth workers, welfare case workers, etc.|Local board members|Media organizations|Non-Profit community|Police and other law or regulation enforcement agencies|Policy makers|Researchers|Schools - teachers, counselors, aides, etc.|Social workers and psychotherapists

Here is a Google sheet with Data
https://docs.google.com/spreadsheets/d/1JcZ4uG6UqjoDdWuVLsikDCdPY5YPIaPT5HZR9azP3rU/edit?usp=sharing

I have been trying for hours to get this right but to no avail

Thanks

 

[mohadin]

Hi
Data in column A starts A1
Result in column B

Sub test()
    Dim x As Variant
    x = Join(Split(Cells(1, 1), ", "), "|")
    Cells(1, 1).Offset(, 1) = x
End Sub

 

[userxyz777]

Thanks mahadin,

This gives

Health and human service organizations and their line staff – youth workers, welfare case workers, etc. >> Health and human service organizations and their line staff – youth workers|welfare case workers|etc.

Schools - teachers, counselors, aides, etc. >> Schools - teachers|counselors|aides|etc.

Is there away to fix this?

Thanks

 

[mohadin]

Ops
Rather

     Sub test()    Dim x As Variant
    Dim i
    For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
    x = Join(Split(Join(Split(Cells(i, 2), ", "), "|"), "- "), "|")
    Cells(i, 2).Offset(, 1) = x
    Next
End Sub

 

[userxyz777]

Thanks again! but went further is the opposite direction

Health and human service organizations and their line staff – youth workers, welfare case workers, etc.  >>  Health and human service organizations and their line staff – youth workers|welfare case workers|etc.

Schools - teachers, counselors, aides, etc.  >>  Schools |teachers|counselors|aides|etc.

 

[userxyz777]

For clarification, I need

Health and human service organizations and their line staff – youth workers, welfare case workers, etc., Schools - teachers, counselors, aides, etc.

To become this

Health and human service organizations and their line staff – youth workers, welfare case workers, etc.|Schools - teachers, counselors, aides, etc.

 

[Fluff]

How about

Sub userxyz()
    Dim Ary As Variant, Sp As Variant
    Dim Cl As Range
    Dim i As Long, j As Long
    
    Ary = Sheets("OptionList").Range("A1").CurrentRegion.Value
    With Sheets("Have")
        For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
            For i = 2 To UBound(Ary)
                j = InStr(1, Cl, ", " & Ary(i, 1), vbTextCompare)
                If j > 1 Then Cl = Mid(Cl, 1, j - 1) & Replace(Cl.Value, ",", "|", j, 1)
            Next i
        Next Cl
    End With
End Sub

 

[userxyz777]

Fluff, Sweet! Thank you

 

[Fluff]

You're welcome & thanks for the feedback