Search for a word in string

[QAVA]

Hello,

I am trying to find a word in string in excel cell.

That means if i have " I like qtp" and i am looking for "qtp" how can i find in excel.

below code works only if i have "qtp" in cell. if i have a sentence along with qtp it does not work.

Please help.

Code:

Dim xl t = "qtp" Set xl = createobject("excel.application") xl.Visible = true Set sd = xl.Workbooks.Open ("F:\us\FP.xls") Set st = sd.Worksheets("Sheet1") rows = st.usedrange.rows.count coloumn = st.usedrange.columns.count msgbox t For i = 1 to rows For j = 1 to 2 If st.cells(i,j) = t Then msgbox "find" End If Next next

[hiregoudar]

Hi,

What ever you are doing is correct, once you get the value from cell use the split method and try it. and let me if any problem

Thanks,
Mahantesh

[maroli]

You can use:
If Instr(st.cells(i,j),t) >0 Then

Insead of : If st.cells(i,j) = t Then

[QAVA]

I have tried whole lot and it is not working.

My excel sheet has data only in first column. it has about 30 rows.

My job is go through every cell if i see number and bracket( ex- 1) 2) 3)), delete this from the cell. Note there will be other data.

example of first cell: 1) hhhhhh
2nd row cell 2) kkkk

i need to delete only the number and bracket.

below is my code but not working. if you can please help.

Code:

Dim xl, no, bracket,k k = 1 t = "p" no = 1 bracket = ")" Set xl = createobject("excel.application") xl.Visible = true Set sd = xl.Workbooks.Open ("F:\usp\FP Tickets to Automate2.xls") Set st = sd.Worksheets("Sheet1") rows = st.usedrange.rows.count coloumn = st.usedrange.columns.count For k = 1 to 5 a = st.cells(k,1) msgbox "a " &a For m = 1 to 3 no = m & bracket msgbox no b = Replace(a, no, " ") msgbox b instringV = instr(b,no) msgbox instringV If instringV > 0 Then st.cells(k,1) = b Exit for End If msgbox b st.cells(k,1) = b Next Next

[ssvali]

Try this

Code:

Set objexcel = CreateObject("Excel.Application") objexcel.visible = TRUE Set oWorkbook = objexcel.Workbooks.Open ("Z:\Forum.xls") Set oWorksheet = objexcel.Worksheets("Sheet1") Set st = objexcel.Worksheets("Sheet1") r = st.UsedRange.Rows.Count For i = 1 to r c = st.Cells(i,1).Value ln = len(c) b = Mid(c,3) st.Cells(i,1).Value = b Next

[Saleel Prabhu]

Try this,

Code:

For i=1 to ColumnCount For j=1 to RowCount c= objSheet.Cells(j,i).Value k= InStr(c,")") if k>0 objSheet.Cells(j,i).Value= Mid(c,k+1) end if Next Next