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

skydivetom

New Member
Joined
Oct 26, 2017
Messages
5
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.

Code:
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
 

Attachments

  • Extraction.JPG
    Extraction.JPG
    158.6 KB · Views: 9

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,466
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 26, 2017
Messages
5

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,349
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Here is the solution I posted to the ExcelForum forum where the OP cross-posted his question...
VBA Code:
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
 
Last edited:
Solution

Forum statistics

Threads
1,141,478
Messages
5,706,610
Members
421,460
Latest member
Taamrak

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
Top