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 |
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 |
All times are GMT +1. The time now is 01:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com