Referencing a subset of text within a cell

[mbbx5va2]

Hi

Suppose I have a column C of random text data e.g:

Cell C1 = "fffabcffff", Cell C2 = "fabcffffff", Cell C3 = "ffABcfffff" C4 = "fffffff"

How do I reference only cells that are either constants or capitals and contain the sequence abc. So in this case I want to reference Cells 1 to 3 only.

Now the mid function for Cell 1 would be =mid(C1,4,3) but it won't read cell 2 as the abc sequence is in a different section. Again with the capital AB in C3 I'm not sure how to get it to reference this cell.

Any thoughts?

Much appreciated

[mancubus]

does this help?

http://www.excel-easy.com/examples/find-vs-search.html

[mbbx5va2]

does this help?

http://www.excel-easy.com/examples/find-vs-search.html

Thanks I'll experiment with these.

[mbbx5va2]

Right so =MID(D8,SEARCH("abc",D8),3) solves the issue with the substring location changing within the string. However if I apply ther forumula for the Cell C3 case i.e C3= ffABcfffff it seems to read it as ABc however I will need it to be read as abc preferably. Unless I can include an "OR" within the first part of the IF statement that I intend to put the formula in.
So in the VBA editor I will put:
Sub test () IF MID(D8,SEARCH("abc",D8),3) Then mailmerge code End Sub

However if I can also put somehow OR MID(D8,SEARCH("ABc",D8),3) then this would be ok. Not sure what I can put instead of the "OR" to make in work within VB.

[mancubus]

so you need a vba solution...



Sub test()
If InStr(UCase(Range("D8").Value), "ABC") > 0 Then 'substring ABC found
'do stuff
Else 'substring ABC not found
'do stuff
End If
End Sub

[mbbx5va2]

[QUOTE=mancubus;312235]so you need a vba solution...


Yeah. I'm trying to populate different letters depending on whether a column specifies letter type A or B in excel. So I was given two methods: either put the mail merge code in and amend the SQL string or use Ctrl+F9 to create the mail merge fields in a word document with the if statements in the word doc too. Then all I do in VB is assign a button so that it executes the mail merge. This latter method works for me. But for the project I'm working on each row in the column specifying the letter is in a "fffabcff" type format which makes it a little trickier.

I'll have a try with your code a little later. Thanks for the help

[mbbx5va2]

so you need a vba solution...



Sub test()
If InStr(UCase(Range("D8").Value), "ABC") > 0 Then 'substring ABC found
'do stuff
Else 'substring ABC not found
'do stuff
End If
End Sub


Sub test()

Dim Data, Data2 As String
Set Data = Sheets("Sheet1").Range("D8")
Set Data2 = Sheets("Sheet1").Range("C8")

If Data = Mid(Data, InStr(UCase(Data.Value), "ABC"), 3)

Then Data2 = "B"

End If




End Sub
Here I seem to get the error object required when I run it. If I try to use the InStr function only like in the case below then I get the same error.



Sub test()

Dim Data, Data2 As String
Set Data = Sheets("Sheet1").Range("D8")
Set Data2 = Sheets("Sheet1").Range("C8")

InStr(UCase(Data.Value), "ABC") = Data2

End Sub

I am trying to write in VB: If cell D8 contains a substring abc, abC, ABC (case insensitive) in any part of the string then I want to put a B in cell C8. Any help much appreciated. file:///C:\Users\User\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif

[mancubus]

Sub test()
If InStr(UCase(Range("D8").Value), "ABC") > 0 Then Range("C8").Value = "B"
End Sub


if your string in D8 does not contain special letters from different alphabets, you can use below line as well.


Sub test()
If InStr(1, Range("D8").Value, "abc", vbTextCompare) > 0 Then Range("C8").Value = "B"
End Sub



ps: pls notice that optional "start" argument is included in the function when optional "compare" method argument is added.

[mbbx5va2]

Hi

Thanks this works. I was initially thinking that as InStra function gives you the position I wasn't sure how specifying it to be non-negative would be equivalent to telling it to filter by "abc" with the additional rules. So I tried to insert your formula into the position argument in the mid function. But I was wrong.

Thanks for the help. :hi:

[mancubus]

you are welcome.

since you test the presence of a substring in a string, the InStr function will suffice.