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