Extract alphanumeric string always beginning with the same character

[jps1986]

I'm trying to extract a alphanumeric string from a mixed string.

Per the example below the text I need to extract is a "Pxxxxx", where the xxxxx could be any length but is always numbers.
The Source cell could be any length and contain other mixed text.

Source Cell	Required Results
Project P1234567 for Org 2005-2017	P1234567
P012345	P012345

<tbody>
</tbody><colgroup><col><col></colgroup>

Any help would be greatly appreciated.

Jim

 

[Rick Rothstein]

Can you make use of a UDF (user defined function)?

Function GetPCode(S As String) As String
  Dim V As Variant
  For Each V In Split(S)
    If V Like "P" & String(Len(V) - 1, "#") Then
      GetPCode = V
      Exit Function
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetPCode just like it was a built-in Excel function. For example,

=GetPCode(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[jps1986]

Excellent solution, Thank you...

 

[UniMord]

A formula alternative: =MID(A3,LOOKUP(255, FIND("P"&(ROW($1:$10)-1),A3)),FIND(" ", A3&" ",LOOKUP(255, FIND("P"&(ROW($1:$10)-1),A3)))-LOOKUP(255, FIND("P"&(ROW($1:$10)-1),A3)))

 

[Rick Rothstein]

A formula alternative: =MID(A3,LOOKUP(255, FIND("P"&(ROW($1:$10)-1),A3)),FIND(" ", A3&" ",LOOKUP(255, FIND("P"&(ROW($1:$10)-1),A3)))-LOOKUP(255, FIND("P"&(ROW($1:$10)-1),A3)))

We do not know what kind of text can appear before the code the OP is looking for, but if it can be varied, then it is possible to fool your formula. Try your formula on this example (there are a huge number of variations on these that will also fool your formula)...

ID P2E4AB Project P1234567 for Org 2005-2019

CODE ABCP2APR2CD Project P1234567 for Org 2005-2019

ABCP2/JA-2CD Project P1234567 for Org 2005-2019

 

[UniMord]

I assumed the OP's data resembles his examples. In situations such as you posed, I readily concede that it's time to use a UDF. My solution is already pretty much at the limit of what I'd consider a practical formula.

 

[markmzz]

Hi!

Maybe the formulas below can helps.

=IFERROR("P"&MID(LOOKUP(8^9,--(SUBSTITUTE(MID(MID(A1,SEARCH(" P"&{0;1;2;3;4;5;6;7;8;9}," "&A1),255),1,
SEARCH(" ",MID(A1&" ",SEARCH(" P"&{0;1;2;3;4;5;6;7;8;9}," "&A1),255))-1),"P","1")&".")),2,255),"")

Or

=IFERROR("P"&MID(LOOKUP(8^9,--(SUBSTITUTE(MID(MID(A1,SEARCH(" P"&{0;1;2;3;4;5;6;7;8;9}," "&A1),255),1,
SEARCH(" ",MID(A1&" ",SEARCH(" P"&{0;1;2;3;4;5;6;7;8;9}," "&A1),255))-1),"P","1")&",")),2,255),"")

Markmzz

 

[Peter_SSs]

If interested, here is another UDF that I think should do the job.

Function Pcode(S As String) As String
  With CreateObject("VBScript.Regexp")
    .Pattern = "\bP\d+\b"
    If .Test(S) Then Pcode = .Execute(S)(0)
  End With
End Function

@markmzz
Mark, It may well be impossible with the OP's data, but your formula would return an incorrect result for text like
A P66P3 B P5432 C

 

[markmzz]

@markmzz
Mark, It may well be impossible with the OP's data, but your formula would return an incorrect result for text like
A P66P3 B P5432 C

Peter, try this small modification (the formula with . and not ,):

=IFERROR("P"&MID(LOOKUP(8^9,--(SUBSTITUTE(" "&MID(MID(A14,SEARCH(" P"&{0;1;2;3;4;5;6;7;8;9}," "&A14),255),1,
SEARCH(" ",MID(A14&" ",SEARCH(" P"&{0;1;2;3;4;5;6;7;8;9}," "&A14),255))-1)," P","1")&".")),2,255),"")

Markmzz

 

[Peter_SSs]

Peter, try this small modification

Given the point below, you probably should increase the 8^9 significantly. As is, the formula would only safely extract up to about 7 digits.

.. extract is a "Pxxxxx", where the xxxxx could be any length

Even then, the formula could be problematic if the number of digits can be reasonably large.
For example, increasing the 8^9 to 8^99, the formula then returns an incorrect result (P123456789012340) for this data
A P123456789012345 B