Solved: Finding chars in strings and returning position

[Sir Phoenix]

If I had the string "My Cool Kitty", and I wanted to return the position of the letter 'K', is there an easy way to return it? (Assuming that the string will be of variable length and will have only 1 of the desired letter?)

[malik641]

is there an easy way to return it?Sure is:

If "My Cool Kitty" is in A1, then:

This is case-sensitive
=FIND("K",A1,1)

And if you want a case-insensitive formula, then:
=SEARCH("k",A1,1)

:thumb

[Sir Phoenix]

Is that a VB formula, or excel?

[BlueCactus]

Those were Excel formulae (typed into a cell).

If you need a VBA function, use Instr().

position = Instr("My Cool Kitty", "k", 0) ' is case-sensitive
position = Instr("My Cool Kitty", "k", 1) ' is case-insensitive.

You can also specify a start position. If you wanted to start at position 4:

position = Instr(4, "My Cool Kitty", "k", 1)

[malik641]

It's an Excel formula, if you want an VB formula then:

Option Explicit

Sub GetTextPosition()
Dim i As Long

i = Application.WorksheetFunction.Search("k", Selection, 1)

MsgBox i
End Sub

Sub GetTextPositionCS()
Dim i As Long

i = Application.WorksheetFunction.Find("K", Selection, 1)

MsgBox i
End Sub The first sub is the case insensitive and the second is case sensitive.

EDIT: Didn't realize BlueCactus posted while I was typing...my bad :doh:

Didn't know you could use the Instr property, good stuff :thumb

[Cyberdude]

Correct me if I'm wrong, but functions Find and Search generate a fatal error if the item being searched for isn't found. If you use them in VBA, be sure to add error handling of some sort. Personally, I think the InStr function would be a better choice...no fatal error.

[xCav8r]

http://vbaexpress.com/kb/getarticle.php?kb_id=668 :)

[Sir Phoenix]

Thanks much! Thanks for all your help, you've given me what I needed! Marking solved.