Macro needed to find text

N

newman

I have sheet with 2 columns and 1200 rows.
Column A is numbers 1 to 1200
Column B is varying length text strings.

I wish to paste different longer text strings into column C and then
find if any of the strings in column B occur within the strings in
column C. If yes, I wish to have the corresponding number from column A
put into column D.

e.g.

1 abc defghij 2
2 efgh xyzxyz
3 mnopq wwwabcwww 1
4 rstu wefghw 2

Is this possible with a function or a macro?

Regards
 
D

Dave Peterson

Maybe you could just use a formula in column C.

I put your data in A1:B4 and this array formula in C1

=SUM(--ISNUMBER(SEARCH($A$1:$A$4,B1)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

Then dragged down to C4.
 
D

Don Guillett

Give some detailed examples of what you would put in col C and what you
expect in col D
 
N

newman

Don said:
Give some detailed examples of what you would put in col C and what you
expect in col D
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Click to expand...


I wish to find any string from col B occurring in any of the longer
strings in col C, and if found require the index no. from col A [or if
easier, the row no.] to be inserted in col D.

Does this make it clearer?

Col A B C D

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxx 1
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxx 4

Regards
 
D

Don Guillett

Either of these will do. Just change the references to suit.
Sub ifinstr()
For Each x In Range("h1:h5")
For Each c In Range("i1:i5")
If InStr(1, c, x) > 0 Then c.Offset(, 1) = x.Row
Next c
Next x
End Sub

Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 1) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
newman said:
Don said:
Give some detailed examples of what you would put in col C and what you
expect in col D
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Click to expand...


I wish to find any string from col B occurring in any of the longer
strings in col C, and if found require the index no. from col A [or if
easier, the row no.] to be inserted in col D.

Does this make it clearer?

Col A B C D

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxx 1
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxx 4

Regards
Click to expand...
 
N

newman

Don said:
Either of these will do. Just change the references to suit.
Sub ifinstr()
For Each x In Range("h1:h5")
For Each c In Range("i1:i5")
If InStr(1, c, x) > 0 Then c.Offset(, 1) = x.Row
Next c
Next x
End Sub

Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 1) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
newman said:
Don said:
Give some detailed examples of what you would put in col C and what you
expect in col D
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Have a look in the vba help index for FINDNEXT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
I have sheet with 2 columns and 1200 rows.
Column A is numbers 1 to 1200
Column B is varying length text strings.

I wish to paste different longer text strings into column C and then
find if any of the strings in column B occur within the strings in
column C. If yes, I wish to have the corresponding number from
column A
put into column D.

e.g.

1 abc defghij 2
2 efgh xyzxyz
3 mnopq wwwabcwww 1
4 rstu wefghw 2

Is this possible with a function or a macro?

Regards

I have never user VBA

Regards
Click to expand...


I wish to find any string from col B occurring in any of the longer
strings in col C, and if found require the index no. from col A [or if
easier, the row no.] to be inserted in col D.

Does this make it clearer?

Col A B C D

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxx 1
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxx 4

Regards
Click to expand...
Click to expand...

Don

Thank you

The first routine works well. However I have noticed a small problem.
Some of my larger strings in column C contain more than one string from
column B. How can I have these identified , perhaps in further columns
E,F,G,H etc

Col A B C D E F

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxrstuxvwxyxx 1 4 5
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxxmnopqxx 4 3


Regards
 
D

Don Guillett

This seems to do it
Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mc = Cells(c.Row, cel.Column).End(xlToRight).Column + 1
Cells(c.Row, mc) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
newman said:
Don said:
Either of these will do. Just change the references to suit.
Sub ifinstr()
For Each x In Range("h1:h5")
For Each c In Range("i1:i5")
If InStr(1, c, x) > 0 Then c.Offset(, 1) = x.Row
Next c
Next x
End Sub

Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 1) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
newman said:
Don Guillett wrote:

Give some detailed examples of what you would put in col C and what
you
expect in col D
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Have a look in the vba help index for FINDNEXT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
I have sheet with 2 columns and 1200 rows.
Column A is numbers 1 to 1200
Column B is varying length text strings.

I wish to paste different longer text strings into column C and
then
find if any of the strings in column B occur within the strings
in
column C. If yes, I wish to have the corresponding number from
column A
put into column D.

e.g.

1 abc defghij 2
2 efgh xyzxyz
3 mnopq wwwabcwww 1
4 rstu wefghw 2

Is this possible with a function or a macro?

Regards

I have never user VBA

Regards


I wish to find any string from col B occurring in any of the longer
strings in col C, and if found require the index no. from col A [or if
easier, the row no.] to be inserted in col D.

Does this make it clearer?

Col A B C D

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxx 1
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxx 4

Regards
Click to expand...
Click to expand...

Don

Thank you

The first routine works well. However I have noticed a small problem.
Some of my larger strings in column C contain more than one string from
column B. How can I have these identified , perhaps in further columns
E,F,G,H etc

Col A B C D E F

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxrstuxvwxyxx 1 4 5
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxxmnopqxx 4 3


Regards
Click to expand...
 
N

newman

Don said:
This seems to do it
Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mc = Cells(c.Row, cel.Column).End(xlToRight).Column + 1
Cells(c.Row, mc) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
newman said:
Don said:
Either of these will do. Just change the references to suit.
Sub ifinstr()
For Each x In Range("h1:h5")
For Each c In Range("i1:i5")
If InStr(1, c, x) > 0 Then c.Offset(, 1) = x.Row
Next c
Next x
End Sub

Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 1) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Give some detailed examples of what you would put in col C and what
you
expect in col D
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Have a look in the vba help index for FINDNEXT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
I have sheet with 2 columns and 1200 rows.
Column A is numbers 1 to 1200
Column B is varying length text strings.

I wish to paste different longer text strings into column C and
then
find if any of the strings in column B occur within the strings
in
column C. If yes, I wish to have the corresponding number from
column A
put into column D.

e.g.

1 abc defghij 2
2 efgh xyzxyz
3 mnopq wwwabcwww 1
4 rstu wefghw 2

Is this possible with a function or a macro?

Regards

I have never user VBA

Regards


I wish to find any string from col B occurring in any of the longer
strings in col C, and if found require the index no. from col A [or if
easier, the row no.] to be inserted in col D.

Does this make it clearer?

Col A B C D

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxx 1
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxx 4

Regards
Click to expand...

Don

Thank you

The first routine works well. However I have noticed a small problem.
Some of my larger strings in column C contain more than one string from
column B. How can I have these identified , perhaps in further columns
E,F,G,H etc

Col A B C D E F

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxrstuxvwxyxx 1 4 5
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxxmnopqxx 4 3


Regards
Click to expand...
Click to expand...

Don

The second routine does not seem to do anything.

I am using Visual Basic 6.0

Regards
 
D

Don Guillett

This is vba within excel and was fully tested.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
newman said:
Don said:
This seems to do it
Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mc = Cells(c.Row, cel.Column).End(xlToRight).Column + 1
Cells(c.Row, mc) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
newman said:
Don Guillett wrote:

Either of these will do. Just change the references to suit.
Sub ifinstr()
For Each x In Range("h1:h5")
For Each c In Range("i1:i5")
If InStr(1, c, x) > 0 Then c.Offset(, 1) = x.Row
Next c
Next x
End Sub

Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 1) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Give some detailed examples of what you would put in col C and what
you
expect in col D
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Have a look in the vba help index for FINDNEXT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
I have sheet with 2 columns and 1200 rows.
Column A is numbers 1 to 1200
Column B is varying length text strings.

I wish to paste different longer text strings into column C
and
then
find if any of the strings in column B occur within the
strings
in
column C. If yes, I wish to have the corresponding number from
column A
put into column D.

e.g.

1 abc defghij 2
2 efgh xyzxyz
3 mnopq wwwabcwww 1
4 rstu wefghw 2

Is this possible with a function or a macro?

Regards

I have never user VBA

Regards


I wish to find any string from col B occurring in any of the longer
strings in col C, and if found require the index no. from col A [or
if
easier, the row no.] to be inserted in col D.

Does this make it clearer?

Col A B C D

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxx 1
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxx 4

Regards

Don

Thank you

The first routine works well. However I have noticed a small problem.
Some of my larger strings in column C contain more than one string from
column B. How can I have these identified , perhaps in further columns
E,F,G,H etc

Col A B C D E F

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxrstuxvwxyxx 1 4 5
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxxmnopqxx 4 3


Regards
Click to expand...
Click to expand...

Don

The second routine does not seem to do anything.

I am using Visual Basic 6.0

Regards
Click to expand...
 
D

Don Guillett

Send a workbook to me if desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett said:
This is vba within excel and was fully tested.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
newman said:
Don said:
This seems to do it
Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mc = Cells(c.Row, cel.Column).End(xlToRight).Column + 1
Cells(c.Row, mc) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Either of these will do. Just change the references to suit.
Sub ifinstr()
For Each x In Range("h1:h5")
For Each c In Range("i1:i5")
If InStr(1, c, x) > 0 Then c.Offset(, 1) = x.Row
Next c
Next x
End Sub

Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 1) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Give some detailed examples of what you would put in col C and
what
you
expect in col D
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Have a look in the vba help index for FINDNEXT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
I have sheet with 2 columns and 1200 rows.
Column A is numbers 1 to 1200
Column B is varying length text strings.

I wish to paste different longer text strings into column C
and
then
find if any of the strings in column B occur within the
strings
in
column C. If yes, I wish to have the corresponding number
from
column A
put into column D.

e.g.

1 abc defghij 2
2 efgh xyzxyz
3 mnopq wwwabcwww 1
4 rstu wefghw 2

Is this possible with a function or a macro?

Regards

I have never user VBA

Regards


I wish to find any string from col B occurring in any of the longer
strings in col C, and if found require the index no. from col A [or
if
easier, the row no.] to be inserted in col D.

Does this make it clearer?

Col A B C D

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxx 1
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxx 4

Regards

Don

Thank you

The first routine works well. However I have noticed a small problem.
Some of my larger strings in column C contain more than one string
from
column B. How can I have these identified , perhaps in further columns
E,F,G,H etc

Col A B C D E F

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxrstuxvwxyxx 1 4 5
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxxmnopqxx 4 3


Regards
Click to expand...

Don

The second routine does not seem to do anything.

I am using Visual Basic 6.0

Regards
Click to expand...
Click to expand...
 
N

newman

I think I have found the problem. It may have been a variables issue. I
was running routine 2 after running routine 1.

I opened a new workbook and it ran ok.
Click to expand...


That routine works ok but the result does not fully meet my needs. I
need to take another approach.

I wish to search a block of text in a cell for any strings of text
within double quotation marks and put this string in a new column. If
there are more than one set of quotation marks then the subsequent
strings should be put in the next row. Is this possible?

e.g..

xxx"abc"xxxx"def"xxxx"ghijk"xxxxxx abc
def
ghijk

Regards
 
N

newman

Don said:
Either of these will do. Just change the references to suit.
Sub ifinstr()
For Each x In Range("h1:h5")
For Each c In Range("i1:i5")
If InStr(1, c, x) > 0 Then c.Offset(, 1) = x.Row
Next c
Next x
End Sub

Sub iffound()
For Each cel In Range("h1:h5")
With Columns("i")
Set c = .Find(cel, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 1) = cel.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
newman said:
Don said:
Give some detailed examples of what you would put in col C and what you
expect in col D
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Don Guillett wrote:

Have a look in the vba help index for FINDNEXT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
I have sheet with 2 columns and 1200 rows.
Column A is numbers 1 to 1200
Column B is varying length text strings.

I wish to paste different longer text strings into column C and then
find if any of the strings in column B occur within the strings in
column C. If yes, I wish to have the corresponding number from
column A
put into column D.

e.g.

1 abc defghij 2
2 efgh xyzxyz
3 mnopq wwwabcwww 1
4 rstu wefghw 2

Is this possible with a function or a macro?

Regards

I have never user VBA

Regards
Click to expand...


I wish to find any string from col B occurring in any of the longer
strings in col C, and if found require the index no. from col A [or if
easier, the row no.] to be inserted in col D.

Does this make it clearer?

Col A B C D

Row

1 1 abc xxefghxxx 2
2 2 efgh xxyyyxx
3 3 mnopq xxxabcxxx 1
4 4 rstu xxxefghxxx 2
5 5 vwxy xxrstuxxx 4

Regards
Click to expand...
Click to expand...

Don

The first routine is working well. However The number of rows in both
columns varies and I have to change the macro each time. Can it be
modified to check down both columns for all strings in that column.

Regards
 
Top