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

userxyz777

New Member
Joined
Apr 12, 2018
Messages
15
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
Code:
[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:
Code:
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
Code:
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi
Data in column A starts A1
Result in column B
Code:
Sub test()
    Dim x As Variant
    x = Join(Split(Cells(1, 1), ", "), "|")
    Cells(1, 1).Offset(, 1) = x
End Sub
 
Last edited:
Upvote 0
Thanks mahadin,

This gives

Code:
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
 
Upvote 0
Ops
Rather
Code:
     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
 
Last edited:
Upvote 0
Thanks again! but went further is the opposite direction

Code:
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.
 
Upvote 0
For clarification, I need

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

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

:)
 
Last edited:
Upvote 0
How about
Code:
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top