help needed

[BDCC]

I have an excel spreadsheet which has a column of text in it. What I want
to know, is it possible to search each row for a specific string and then
extract the next 10 characters and copy it into a new colum??

Any help would be appreciated.

thanks

 

[Tom Ogilvy]

Is this a programming question (you have posted to several groups).

Do you want to search in a single column or do you want to search the entire
row.

How about selecting all cells of interest and doing Edit=>Find which whole
unchecked. If you turn on the macro recorder while you do this manually,
this will provide you the code you need to do it. Then you can then add
code to get the next 10 characters.

This is what I record:
Sub Macro1()

Selection.Find(What:="def", After:=ActiveCell, LookIn:=xlValues, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
End Sub


I would modify it like this:
Sub Macro2()
Dim rng As Range
Dim iloc As Long, sStr As String
Set rng = Cells.Find(What:="def", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then
iloc = InStr(rng.Value, "def")
iloc = iloc + 3
sStr = Mid(rng.Value, iloc, 10)
MsgBox sStr
Else
MsgBox "Not found"
End If
End Sub



Which worked for me.