Microsoft Office Forums Looking for certain words in a string

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-06-2019, 06:45 PM
Guinness Guinness is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2019
Novice
Looking for certain words in a string
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default Looking for certain words in a string

If I wanted to return the words "glass", "marble" or "wood" in the below strings, how would I do it?



String Results
The counter was made of marble. marble
The window was made of glass glass
The cabinets were made of wood wood


Thank you
Reply With Quote
  #2  
Old 08-06-2019, 07:07 PM
Guinness Guinness is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2019
Novice
Looking for certain words in a string
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default

I have almost 1000 records (strings) that I need to pull a whole list of words out of. I know I can't do it with nested formulas. What would be the best way to do that?
Reply With Quote
  #3  
Old 08-07-2019, 02:03 AM
xor xor is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default Looking for certain words in a string

With your text string in A1 use

=IFERROR(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),MAX(1,FIND("marble",SUBSTITUTE(A1," ",REPT(" ",99)))-50),99)),"")

to extract marble.

In another cell to extract glass

=IFERROR(TRIM(MID(SUBSTITUTE(A1;" ";REPT(" ";99));MAX(1;FIND("glass";SUBSTITUTE(A1;" ";REPT(" ";99)))-50);99)),"")

and so on.
Reply With Quote
  #4  
Old 08-07-2019, 11:29 AM
Guinness Guinness is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2019
Novice
Looking for certain words in a string
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default

I'm so sorry, I meant like if a cell contains a word from a list.
Reply With Quote
  #5  
Old 08-07-2019, 12:16 PM
NBVC's Avatar
NBVC NBVC is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 207
NBVC will become famous soon enough
Default

Assuming your list of lookup values is in say J1:J3, then try:


=LOOKUP(2,1/SEARCH($J$1:$J$3,A1),$J$1:$J$3)


where A1 contains first sentence... copied down
Reply With Quote
  #6  
Old 08-07-2019, 03:55 PM
Guinness Guinness is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2019
Novice
Looking for certain words in a string
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default

Thank you, but I am not getting that to work. I've got my main sheet with the descriptions and a second tab titled "beads" with a list of beads types (i.e., acrylic, glass, stone, crystal, etc.). The descriptions are around 500 characters. Below is an example of one (ignore the HTML). I want it to pull "stone" out of this string. Any other types of beads in this example, like silver beads, don't matter. I just want the one from the list (always the first type).

I have my workbook set up with a beads tab with all the types in column A in a named table (beads). The second tab is called Description and the description is in Column E, which is not set up as a named table.

"These are simply beautiful Anglican rosary prayer beads. They are handmade with Multi-Colored Picasso Stone and silver beads. They would be a wonderful gift for someone special for Easter, birthdays, Mothers or Fatherís Day, new baby gifts, or your local priest or just for yourself. If you require a custom design, these can be made to order completely to your requirements. Simply message me for details.<br><br><p>Features:</p><ul><li>Handmade Item</li><li>Materials: Multi-Colored Picasso Stone and silver beads</li><li>Approximately 10.5 inches long</li><li>Beautiful</li><li>Great gift</li></ul>"

Should you want more examples I can provide that. Thanks very much for your help
Reply With Quote
  #7  
Old 08-07-2019, 06:32 PM
Guinness Guinness is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2019
Novice
Looking for certain words in a string
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default

Also, apparently if there are more than one of those words, it'll return the last word. I will need the first word.

Thanks
Reply With Quote
  #8  
Old 08-07-2019, 06:50 PM
NoSparks NoSparks is offline Looking for certain words in a string Windows 7 64bit Looking for certain words in a string Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 743
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Are you receptive to using VBA ?
How about posting a workbook so we're all dealing with the same thing and can test possible solutions
Reply With Quote
  #9  
Old 08-07-2019, 07:23 PM
Guinness Guinness is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2019
Novice
Looking for certain words in a string
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default

Sure. You may have to walk me through it somewhat. I have a very basic knowledge of VB

I have attached a sample of my spreadsheet. Thanks!
Attached Files
File Type: xlsx Example_ListLookup.xlsx (22.5 KB, 5 views)
Reply With Quote
  #10  
Old 08-08-2019, 05:44 AM
NBVC's Avatar
NBVC NBVC is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 207
NBVC will become famous soon enough
Default

Does this Array* formula work?


=INDEX(BeadTypes!$A$2:$A$12, MATCH(MIN(IFERROR(SEARCH(BeadTypes!$A$2:$A$12,D2), "x")), IFERROR(SEARCH(BeadTypes!$A$2:$A$12,D2),"x"),0))


*you must confirm the formula with CTRL+SHIFT+ENTER not just ENTER, then copy down.

#N/A means no matches found.
Reply With Quote
  #11  
Old 08-08-2019, 06:29 AM
NoSparks NoSparks is offline Looking for certain words in a string Windows 7 64bit Looking for certain words in a string Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 743
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

This macro, gives the same results as NBVC's formula from post 10 (except the #N/A is left blank)
Code:
Sub FirstBeadType()
    Dim listRng As Range, cel As Range
    Dim i As Long, lr As Long
    
With Sheets("BeadTypes")
    Set listRng = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
End With

With Sheets("Description")
    lr = .Range("D" & .Rows.Count).End(xlUp).Row
    For Each cel In listRng
        For i = 2 To lr
            If .Cells(i, "A") = "" And InStr(1, .Cells(i, "D"), cel.Value, vbTextCompare) > 0 Then
                .Cells(i, "A") = cel.Value
            End If
        Next i
    Next cel
End With
End Sub
Reply With Quote
  #12  
Old 08-08-2019, 07:28 AM
Guinness Guinness is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2019
Novice
Looking for certain words in a string
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default

Both of these work perfectly! Thank you so much!
Reply With Quote
  #13  
Old 08-08-2019, 10:34 PM
ArviLaanemets ArviLaanemets is offline Looking for certain words in a string Windows 8 Looking for certain words in a string Office 2016
Expert
 
Join Date: May 2017
Posts: 488
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

Had Internet blackout almost full day yesterday and couldn't post my solution. Anyway I don't want to waste it, so here it is!


I prefer to work with defined Tables, so I used them. Columns with colored headers are helper columns - you can hide them.

As FIND() is case sensitive, I changed all bead type definitions to lower case.

Currently all items are marked for bead type, whenever the key string appears anywhere in description. Because there are bead type declarations (a string in description which declares the type of item) like "Bead Type: blue crystals" you can't use type definitions like "Bead Type: crystal" for getting entries with only declared type - unless you make separate type for e.g. every crystal color. (I'm afraid with thousands of items you aren't ready to replace all type declarations with ones like "Bead Type: crystal; Color: blue;".)


I added a report sheet where you select a type, and get full list of items having key string in description.


To have a possibility to extract the type string from description, and as follows to get a more flexible solution with less of needed helper columns you must have type declarations to be standardized with string start and end being exactly defined. Currently there is a mess in this department!
Attached Files
File Type: xlsx Example_ListLookup.xlsx (28.8 KB, 2 views)
Reply With Quote
  #14  
Old 08-09-2019, 08:50 AM
Guinness Guinness is offline Looking for certain words in a string Windows 10 Looking for certain words in a string Office 2019
Novice
Looking for certain words in a string
 
Join Date: Aug 2019
Location: Deep South
Posts: 16
Guinness is on a distinguished road
Default

ArviLaanemets thank you for your response.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find (highlight) two and more words in a list of 75k single words in Word 2010 Usora Word 8 05-29-2018 03:34 AM
Looking for certain words in a string Wildcard replace any string in context with a specified string wardw Word 7 05-07-2018 09:13 AM
Looking for certain words in a string How can I compare a string in a cell to another string? Amitti Word VBA 2 04-10-2017 07:35 PM
How to find all string withing string. PRA007 Word VBA 18 02-12-2016 08:11 PM
Looking for certain words in a string Way to search for a string in text file, pull out everything until another string? omahadivision Excel Programming 12 11-23-2013 12:10 PM


All times are GMT -7. The time now is 09:48 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft