#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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?
|
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
I'm so sorry, I meant like if a cell contains a word from a list.
|
#5
|
||||
|
||||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Also, apparently if there are more than one of those words, it'll return the last word. I will need the first word.
Thanks |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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! |
#10
|
||||
|
||||
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. |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Both of these work perfectly! Thank you so much!
|
#13
|
|||
|
|||
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! |
#14
|
|||
|
|||
ArviLaanemets thank you for your response.
|
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 |
Wildcard replace any string in context with a specified string | wardw | Word | 7 | 05-07-2018 09:13 AM |
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 within string. | PRA007 | Word VBA | 18 | 02-12-2016 08:11 PM |
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 |