Need a formula (or VBA) which extracts **multiple** keywords containing an underscore "_" in cell value

[skydivetom]

Experts:

I need some assistance with either a) creating a custom-formula or b) using some VBA or macro which will extract all *entire* words that include an underscore ("_") in a cell.

The cell values include carriage returns as well as duplicate occurrences of a particular keyword. Below shows and example of the raw data, the output of the keywords (w/ duplicates) as well as the string output without duplicates.

Ideally, I would like to get to the output of the "2nd string" (w/o duplicates). Also, each "found keyword" should be separated by a semi-colon (or comma).

My question: How can the output below accomplished via a formula or VBA? I have included a sample XLS containing 3 different example strings (with different lengths) and their associated outputs.

Raw Data
=========

SELECT DISTINCT
   155 AS sort_number,
   00 AS foreign_key,
   'one_liner' AS field_name,
   'Y' AS is_null
FROM
   mishap
WHERE
   (mshp_oneliner IS NULL AND mshp_id = plForeignKey)


1st string output (with duplicates)
===================================
sort_number; foreign_key; one_liner; field_name; is_null; mshp_oneliner; mshp_id; foreign_key


2nd string output (without duplicates)
======================================
sort_number; foreign_key; one_liner; field_name; is_null; mshp_oneliner; mshp_id

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need a formula (or VBA) which extracts keywords containing an underscore "_" in cell value
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[skydivetom]

I apologize for cross-posting... I will ensure to only choose one forum the next time.

Need a formula (or VBA) which extracts keywords containing an underscore "_" in cell value [SOLVED]

Experts: I need some assistance with either a) creating a custom-formula or b) using some VBA or macro which will extract all *entire* words that include an underscore ("_") in a cell. The cell values include carriage returns as well as duplicate occurrences of a particular keyword...

www.excelforum.com

 

[Rick Rothstein]

Here is the solution I posted to the ExcelForum forum where the OP cross-posted his question...

Give this macro a try...
[CODE]Sub UniqueListOfWordsWithUnderlines()
  Dim X As Long, Cell As Range, Txt As String, Data As Variant
  Application.ScreenUpdating = False
  On Error GoTo Whoops
  For Each Cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    Txt = LCase(Cell.Value)
    For X = 1 To Len(Txt)
      If Mid(Txt, X, 1) Like "[!A-Za-z0-9_]" Then Mid(Txt, X) = " "
    Next
    Data = Filter(Split(Application.Trim(Txt)), "_")
    With CreateObject("Scripting.Dictionary")
      For X = 0 To UBound(Data)
        .Item(Data(X)) = 1
      Next
      Cell.Offset(, 1).Value = Join(.Keys, "; ")
    End With
  Next
Whoops:
  Application.ScreenUpdating = True
End Sub

Note: Here is the link to that other thread...
Need a formula (or VBA) which extracts keywords containing an underscore "_" in cell value