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

userxyz777

New Member
Joined
Apr 12, 2018
Messages
10
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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:

userxyz777

New Member
Joined
Apr 12, 2018
Messages
10
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
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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:

userxyz777

New Member
Joined
Apr 12, 2018
Messages
10
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.
 

userxyz777

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,902
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,902
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,454
Messages
5,468,752
Members
406,604
Latest member
DisgruntledVBANovice

This Week's Hot Topics

Top