Searching part of a string

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a list of data in column A:

apple
orange
pear

I want to search for this string in the data:

Code:
small oranges sold

and return 2 (because orange is the second value in the list).

I have tried:

Code:
Like "*orange*"

as well as the Instr function but neither worked,

Is this even possible?

Thanks
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Well, it depends on how you are breaking your words. A char(10) is what you have now between each word. I think it is a carriage return or a line feed.
But, this does what you're asking for:

NOTE: This works for Office 365. I'm not sure if TEXTSPLIT is available in 2019 version.

mr excel questions 17.xlsm
AB
1apple orange pear2
Sheet5
Cell Formulas
RangeFormula
B1B1=MATCH("orange",TEXTSPLIT(SUBSTITUTE($A$1,CHAR(10),"/"),"/"),0)




Here it is without the substitution function:
mr excel questions 17.xlsm
ABC
1apple orange pearorange2
2apple1
3pear3
Sheet5
Cell Formulas
RangeFormula
C1:C3C1=MATCH(B1,TEXTSPLIT($A$1,CHAR(10),"/"),0)
 
Upvote 0
Well, it depends on how you are breaking your words. A char(10) is what you have now between each word. I think it is a carriage return or a line feed.
But, this does what you're asking for:

NOTE: This works for Office 365. I'm not sure if TEXTSPLIT is available in 2019 version.

mr excel questions 17.xlsm
AB
1apple orange pear2
Sheet5
Cell Formulas
RangeFormula
B1B1=MATCH("orange",TEXTSPLIT(SUBSTITUTE($A$1,CHAR(10),"/"),"/"),0)




Here it is without the substitution function:
mr excel questions 17.xlsm
ABC
1apple orange pearorange2
2apple1
3pear3
Sheet5
Cell Formulas
RangeFormula
C1:C3C1=MATCH(B1,TEXTSPLIT($A$1,CHAR(10),"/"),0)
Yeah, I just confirmed TEXTSPLIT was not made available until Late Summer 2022, So it is probably not in Version 2019.
 
Upvote 0
Yeah, I just confirmed TEXTSPLIT was not made available until Late Summer 2022, So it is probably not in Version 2019.
Thanks.

I swapped my logic round and it worked.

Code:
    Dim str As String

    str= "small oranges sold"

    Dim i As Long
    
    i = 1
    
    Do Until InStr(1, Str, Sheet1.Cells(i, 1).Value, vbTextCompare) <> 0 And _
             Sheet1.Cells(i, 1).Value <> vbNullString
    
        i = i + 1
    
    Loop
 
Upvote 0
VBA Code:
Public Sub test()
 Dim str As String

    str = "small oranges sold"

    Dim i As Long
        i = 1

    Do Until Sheet1.Cells(i, 1).Value = vbNullString
    If InStr(1, str, Sheet1.Cells(i, 1).Value, vbTextCompare) <> 0 Then
        MsgBox i
        Exit Do
    End If
        i = i + 1
    Loop
  End Sub
 
Upvote 0
VBA Code:
Public Sub test()
 Dim str As String

    str = "small oranges sold"

    Dim i As Long
        i = 1

    Do Until Sheet1.Cells(i, 1).Value = vbNullString
    If InStr(1, str, Sheet1.Cells(i, 1).Value, vbTextCompare) <> 0 Then
        MsgBox i
        Exit Do
    End If
        i = i + 1
    Loop
  End Sub
Thanks.

Can you tell me why when I changed my original code to:

Code:
Dim str As String

    str = "zzz"

    Dim i As Long
   
    i = 1
   
    Do Until InStr(1, str, Sheet1.Cells(i, 1).Value, vbTextCompare) <> 0
        i = i + 1
   
    Loop

i returns 4.

Effectively, I am asking why the following code returns 1?

Code:
?InStr(1, "anyvalue", "", vbTextCompare)
 
Upvote 0
Excel Formula:
=match("orange",A1:A3,0)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top