Results 1 to 6 of 6

Split comma delimited string on comma replace comma with pipe then rejoin Join string

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Cool Split comma delimited string on comma replace comma with pipe then rejoin Join string

    Cells in column H are comma delimited strings. I heed to replace ONLY the comma between the sub-strings

    ex:

    Social sciences - i.e. human geography, psychology, economics, history, sociology, decision sciences, ecosynomics, Transformations systems - i.e.*theory, processes, structures of large scale systems change
    In this case the sub-strings are
    Social sciences - i.e. human geography, psychology, economics, history, sociology, decision sciences, ecosynomics
    Transformations systems - i.e.*theory, processes, structures of large scale systems change
    so I get
    Social sciences - i.e. human geography, psychology, economics, history, sociology, decision sciences, ecosynomics|Transformations systems - i.e.*theory, processes, structures of large scale systems change
    The code I have gives me
    Social sciences - i.e. human geography, psychology, economics, history, sociology, decision sciences, ecosynomics|Transformations systems - i.e.*theory|processes|structures of large scale systems change
    Sub FindReplacePipeCol_H()
    Dim rng As Range, Dn As Range, n As Long, K As Variant, nStr As String, fd As Boolean
    
    With Sheets("FullOptionLists")
        Set rng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    End With
    
    
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In rng
        If InStr(Dn.Value, ", ") Then .Item(Dn.Value) = Empty
    Next
    
    
    With Sheets("RawData")
        Set rng = .Range("H2", .Range("H" & Rows.Count).End(xlUp))
    End With
    
    
    For Each Dn In rng
       fd = False
        For Each K In .Keys
            If .Exists(Left(Dn.Value, Len(K))) And K = Left(Dn.Value, Len(K)) Then
                Dn.Value = K & Replace(Right(Dn.Value, Len(Dn.Value) - Len(K)), ", ", "|")
                fd = True
                Exit For
            End If
        Next K
    
    If Not fd Then Dn.Value = Replace(Dn.Value, ", ", "|")
    
    Next Dn
    End With
    End Sub
    I have been trying for hours get this to no avail

    Thanks for any help with this

    I have attached a file
    Attached Files Attached Files
    Last edited by capson; 05-25-2020 at 12:34 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract String from comma delimited list in cell
    By Beats.Nigel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-13-2019, 08:48 AM
  2. [SOLVED] Cell containing comma delimited string changes to number format issue
    By Sintek in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-20-2018, 03:59 AM
  3. [SOLVED] Simplification for regex code to replace numbers in a comma delimited string
    By Sintek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-08-2018, 06:30 AM
  4. [SOLVED] Summing Values Based on a Comma Delimited String
    By bryan0 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-18-2013, 11:43 AM
  5. Excel 2007 : Pivot Table String Comma Delimited
    By asyssovers in forum Excel General
    Replies: 0
    Last Post: 06-08-2011, 01:23 AM
  6. Building a comma delimited string from Cell Range
    By Patchworks in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-17-2008, 12:25 AM
  7. Searching a comma delimited string in a cell
    By migpics in forum Excel General
    Replies: 7
    Last Post: 11-26-2007, 05:58 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1