Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
help needed
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
help needed
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. -- Regards, Tom Ogilvy End Sub -- Regards, Tom Ogilvy "BDCC" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows With Non-Needed Data between Needed Data | Excel Worksheet Functions | |||
MVP HELP NEEDED ! | Excel Worksheet Functions | |||
Help needed | New Users to Excel | |||
Help Needed - Please | Excel Discussion (Misc queries) | |||
If Then help needed | New Users to Excel |