Thread: Return Location of Alpha/Numeric String

Hello,

How can I formulaically find the starting location of a string that is letter + 6 numbers + letter in a longer string?

As in,

Find 'a123456b' from po34ksa123456b95043, with an answer of 7.

I was thinking FIND() with wild cards, but the wold cards would have to specify whether it was text or numeral. Any help appreciated!

Greg

Why would you need wildcards?

=FIND("a123456b","po34ksa123456b95043",1)

if you KNOW what the string is ahead of time, a simple SEARCH() or FIND() function will return that position for you.

If you are asking Excel to find ANY string that is in that syntax, then you would need a new function written for you. It would be a VBA solution.

Or you could just ask a mod to move it for you.

Moved to Programming

For a programming solution, this new function will do what you ask:

Function EightCode(MyStr As String) As String
Dim m

With CreateObject("VBScript.RegExp")
    .Pattern = "[A-Za-z]\d{6}[A-Za-z]"     'letter-6 digits-letter
    For Each m In .Execute(MyStr)
        EightCode = m.Value
    Next m
End With

End Function

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.


Now, use this function in a cell like so:

=EIGHTCODE(A2)


To get the "position" over in your table, then use this in K2:

=SEARCH(EIGHTCODE(J2),J2)

This works properly:

Function FindCustom(strng As String)
FindCustom = "NOT IN STRING"
For i = 1 To Len(strng)
    test = Mid(strng, i, 8) Like "[a-zA-Z]######[a-zA-Z]"
    If test = True Then
        FindCustom = i
        Exit Function
    End If
Next i
End Function

I'm not sure if you read my entire post. My Function is designed to find the string, true, but then I showed you how to get the location using standard SEARCH function. So mine allows you to use it for both purposes, to identify the string itself, then use it directly in other functions.

=SEARCH(EIGHTCODE(J2),J2)