extracting words from the word string

[lukasz_rz]

Hello Dear Users.

My question might be a little bit confusing, but I will try to describe it as good as possible.

Let's say i do have words : cow, dog, giraffe, elephant, parrot.

The rest will try to explain in the excel file. Thanks in advance.

Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

 

[Aladin Akyurek]

Sorry Guys, I'm na new guy to this story.

https://app.box.com/s/u86gbkswysmngne8myz4

Hope it works now.

Probably a tad faster than the COUNTIF option...

A5, control+shift+enter, not just enter, copy across to E5, and down:

=IFERROR(INDEX($A$1:$E$1,SMALL(IF(ISNUMBER(SEARCH($A$1:$E$1,$F5)),
  COLUMN($A$1:$E$1)-COLUMN($A$1)+1),COLUMNS($A5:A5))),"")

 

[markmzz]

markmzz,

Nicely done - one for my archives.



Thank you very much.

Hi Hiker95,

You're welcome and I think that the best way, in this case, is code.

Markmzz

 

[lukasz_rz]

lukasz_rz,

Thanks for the workbook.

The macro (using three arrays in memory) will adjust for a varying number of words in row 1, and, for a varying number of strings beginning in cell F5, down.

Sample raw data:

Excel 2007

	A	B	C	D	E	F	G
1	cow	dog	giraffe	elephant	parrot
2
3
4
5						cow makes muu
6						giraffe is tall
7						elephant is big
8						parrot is a colorful bird
9						each little dog is cute
10						dog eats meat
11						giraffe eats leafes
12						elephant has a huge trump
13						parrot can make human's voices
14						giraffe can be met in Africa
15						Once I had a dog but it ran away.
16						some cows give a lot of milk
17						elephant is the biggest animal on the planet
18						elephant is bigget than a parrot
19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After the macro:

Excel 2007

	A	B	C	D	E	F	G
1	cow	dog	giraffe	elephant	parrot
2
3
4
5	cow					cow makes muu
6	giraffe					giraffe is tall
7	elephant					elephant is big
8	parrot					parrot is a colorful bird
9	dog					each little dog is cute
10	dog					dog eats meat
11	giraffe					giraffe eats leafes
12	elephant					elephant has a huge trump
13	parrot					parrot can make human's voices
14	giraffe					giraffe can be met in Africa
15	dog					Once I had a dog but it ran away.
16	cow					some cows give a lot of milk
17	elephant					elephant is the biggest animal on the planet
18	elephant / parrot					elephant is bigget than a parrot
19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ExtractWords()
' hiker95, 10/13/2013
' http://www.mrexcel.com/forum/excel-questions/732529-extracting-words-word-string.html
Dim L, a As Variant, f As Variant
Dim i As Long, ii As Long, h As String, c As Long
L = Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
f = Range("F5:F" & Range("F" & Rows.Count).End(xlUp).Row)
ReDim a(1 To UBound(f, 1), 1 To 1)
For i = LBound(f, 1) To UBound(f, 1)
  h = ""
  For c = LBound(L, 2) To UBound(L, 2)
    If InStr(f(i, 1), Trim(L(1, c))) > 0 Then
      h = h & Trim(L(1, c)) & " / "
    End If
  Next c
  If Right(h, 3) = " / " Then
    a(i, 1) = Left(h, Len(h) - 3)
  End If
Next i
With Range("A5").Resize(UBound(f, 1))
  .ClearContents
  .Value = a
  .Columns(1).AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractWords macro.

I did exactly what You said and it also works great. The last thing that bothers me what should I do change in macro, If I would like to remove data column from for example F to Z (assuming that previous columns from B to Y already have some data put). Which data parameters should I change in the macro?

Sorry for noobie questions, but I'm indeed a noob, willing to learn

 

[hiker95]

lukasz_rz,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.

And, when you respond to your helper, please use their site ID/username/handle.

I did exactly what You said and it also works great.

Thanks for the feedback. Glad I could help.

If I would like to remove data column from for example F to Z (assuming that previous columns from B to Y already have some data put). Which data parameters should I change in the macro?

You want the macro, at the end of filling column A, A5:A18 (in this example) to remove all data from column F to the last used column in the worksheet?

 

[lukasz_rz]

hiker95

Sorry for the mess.

What I mean is to make this macro work no matter where the data column (in my case is F) is. So, f.e. If I put more columns with data behind the A and F, F will obviously become K, L, M or whatever, dependent on how many new columns I put there. How to modify the macro then to still compare the past column F (which is now under different letter with A (which stays the same):

F.E. J
A
dog
elephant but the text data is in the column J now, not F.
giraffe
etc

Is it possible to make easy to change manually for a layman like me?

Appreciate your help once more.

 

[hiker95]

lukasz_rz,

If I understand you correctly.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1	cow	dog	giraffe	elephant	parrot	cat	lion	snake
2
3
4
5						cow makes muu
6						giraffe is tall
7						elephant is big
8						parrot is a colorful bird
9						each little dog is cute
10						dog eats meat
11						giraffe eats leafes
12						elephant has a huge trump
13						parrot can make human's voices
14						giraffe can be met in Africa
15						Once I had a dog but it ran away.
16						some cows give a lot of milk
17						elephant is the biggest animal on the planet
18						elephant is bigget than a parrot
19						lion can be met in Africa
20						a snake can be poisonous
21
Sheet1

After the new macro:

Excel 2007
	A	B	C	D	E	F	G	H
1	cow	dog	giraffe	elephant	parrot
2
3
4
5	cow
6	giraffe
7	elephant
8	parrot
9	dog
10	dog
11	giraffe
12	elephant
13	parrot
14	giraffe
15	dog
16	cow
17	elephant
18	elephant / parrot
19	lion
20	snake
21
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ExtractWordsV2()
' hiker95, 10/13/2013
' http://www.mrexcel.com/forum/excel-questions/732529-extracting-words-word-string.html
Dim L, a As Variant, f As Variant
Dim i As Long, ii As Long, h As String, c As Long, luc As Long
L = Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
f = Range("F5:F" & Range("F" & Rows.Count).End(xlUp).Row)
ReDim a(1 To UBound(f, 1), 1 To 1)
For i = LBound(f, 1) To UBound(f, 1)
  h = ""
  For c = LBound(L, 2) To UBound(L, 2)
    If InStr(f(i, 1), Trim(L(1, c))) > 0 Then
      h = h & Trim(L(1, c)) & " / "
    End If
  Next c
  If Right(h, 3) = " / " Then
    a(i, 1) = Left(h, Len(h) - 3)
  End If
Next i
With Range("A5").Resize(UBound(f, 1))
  .ClearContents
  .Value = a
  .Columns(1).AutoFit
End With
luc = ActiveSheet.UsedRange.Columns.Count
h = Split(Cells(1, luc).Address, "$")(1)
Columns("F:" & h).ClearContents
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractWordsV2 macro.

 

[hiker95]

kasz_rz,

What I mean is to make this macro work no matter where the data column (in my case is F) is. So, f.e. If I put more columns with data behind the A and F, F will obviously become K, L, M or whatever, dependent on how many new columns I put there. How to modify the macro then to still compare the past column F (which is now under different letter with A (which stays the same):

I do not understand?

Can I have another workbook, with:
1. the raw data in Sheet1
2. what the raw data from Sheet1 (will look like after the macro) in Sheet2

 

[lukasz_rz]

hiker95

Thanks for patience

https://app.box.com/s/4pcszu5covu8nydwfkxw

First page shows what you aready created. source data in column F - final extract in column A. Perfect.

Second page shows the different situation. Extract data is still in column A, but the source data is in column J, not F. Then macro is not working anymore.

My question sounds: is it simple to modify this macro to change it manually by me, while I would change the position of the source data column? If you could give me at least how the macro from the second page should like, I could try to compare it and figure out myself.

Would be honored to get your support once again.

Thx!

 

[hiker95]

kasz_rz,

My question sounds: is it simple to modify this macro to change it manually by me, while I would change the position of the source data column?

How about we give the column where the search strings begin in row 5, in row 4 in that column, a title/search word that the macro can search for in row 4?

What would that unique title/search word be?

If the title/search word is not found in row 4 the macro will terminate with a message "Title/search word not fount in row 4 - macro terminated!"

 

[lukasz_rz]

hiker95

I'm afraid we totally do not understand each other. I'm completely fresh in VBA and your explanations seems to difficult for me.
All I needed now to see similar macro but for the page two of my uploaded pic to compare the differences.

Sorry for taking your time