Search for specific character in alphanumeric string

Question

I have a string of characters that I need to search for the letter capital P from between six numerals, three on each side. It's position can vary in the string, but will have the condition of six digits each time. The digits will be 000 through 999 on both sides of the P.

I want to find the numeric position in the string of the letter P for a formula.

Here is a sample of the string: (Please notice that there are several capital P's to contend with.)

TCPXX*,CWOP-1:@082050z4713.76N/12228.23W_005/005g010t040r000p000P000h96b10210L086eWUHU216DAVISVP2.

Is it the same digits only (eg `222P222`), or any digits (eg `274P963`)? — chris neilsen, Jan 09 '15 at 03:24
A formula solution (if possible) would be complex, as an example look at this thread on [chandoo](http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/) — brettdj, Jan 09 '15 at 14:59

score 0 · Answer 1 · edited Jan 10 '15 at 05:50

Thanks to chris neilson, chandoo, and brettdj for your input.

A formula would be difficult to build.

I did a more intense search and actually came across this VBA solution.

http://www.excelfox.com/forum/f22/find-a-text-substring-that-matches-a-given-pattern-331/

Works like a champ for this application.

Code from link:

Function GetPattern(Source As String, ByVal Pattern As String) As String
  Dim X As Long, FindPattern As Long
  Do Until Left(Pattern, 1) <> "*"
    Pattern = Mid(Pattern, 2)
  Loop
  For X = 1 To Len(Source)
    If Mid(Source, X) Like Pattern & "*" Then
      FindPattern = X
      Exit For
    End If
  Next
  If FindPattern = 0 Then Exit Function
  For X = 1 To Len(Source) - FindPattern + 1
    If Mid(Source, FindPattern, X) Like Pattern Then
      GetPattern = Mid(Source, FindPattern, X)
      Exit For
    End If
  Next
End Function

score 0 · Accepted Answer · answered Jan 10 '15 at 17:19

Given you have gone the VBA route I would use a RegExp over a character by character loop. This finds the P position directly.

samples

    Sub Test()
    Debug.Print StripStr("TCPXX*,CWOP-1:@082050z4713.76N/12228.23W_005/005g010t040r000p000P000h96b10210L086eWUHU216DAVISVP2.")
    Debug.Print StripStr("notme")
    Debug.Print StripStr("123P456")
    End Sub

code

Function StripStr(strIn As String) As String
Dim objRegex As Object
Dim objRegexMC As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Pattern = "\d{3}P\d{3}"
    If .Test(strIn) Then
        Set objRegexMC = .Execute(strIn)
        StripStr = objRegexMC(0).firstindex + 4
    Else
        StripStr = "not matched"
    End If
End With
End Function

score -1 · Answer 3 · edited May 23 '17 at 12:12

-1

You can do it by using Regular expressions.

See this article where they address how to use them in Excel:

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

In your case you could want the expression:

\d\d\dP\d\d\d

which matches 3 digits, a P and then 3 digits.

edited May 23 '17 at 12:12

Community

1
1

answered Jan 09 '15 at 03:19

Daniel

21,933
14
72
101

Thanks, Mondkin. RegEx is a great option, but since I'll be distributing this to other users, I want to try a formula first. – Patrick Jan 09 '15 at 04:28

Search for specific character in alphanumeric string

3 Answers3