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

 

[markmzz]

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>

Any help would be greatly appreciated.

Jim

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.

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

You're right, but I don't think that he had a Project with a code with 15 digits (P123456789012345).

Anyway, lets wait for the User.

Markmzz​

 

[Peter_SSs]

You're right, but I don't think that he had a Project with a code with 15 digits (P123456789012345).

You are right too, but I did prefix various of my previous comments with things like "It may well be impossible with the OP's data," and "if the number of digits can be reasonably large". I was just trying to think as widely as possible since the OP said the digits could be "any length".

Anyway, lets wait for the User.

I agree, and hopefully we will find that the OP has had a number of successful suggestions to choose what suits them most.

 

[jps1986]

Thank you for all your suggestions. This site never disappoints.

What if the source text had multiple "P -Numbers" like below.
Could more than 1 be extracted for a result like P1234567, P0987?

Project P1234567 and P0987 for Org 2005-2017

<tbody>
</tbody>

 

[Peter_SSs]

What if the source text had multiple "P -Numbers" like below.
Could more than 1 be extracted for a result like P1234567, P0987?

Project P1234567 and P0987 for Org 2005-2017

<tbody>
</tbody>

Sure.

Function Pcode(S As String) As String
  Static RX As Object
  Dim M As Object
  Dim e As Variant
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "\bP\d+\b"
  Set M = RX.Execute(S)
  For Each e In M
    Pcode = Pcode & ", " & e
  Next e
  Pcode = Mid(Pcode, 3)
End Function

 

[markmzz]

Thank you for all your suggestions. This site never disappoints.

What if the source text had multiple "P -Numbers" like below.
Could more than 1 be extracted for a result like P1234567, P0987?

Project P1234567 and P0987 for Org 2005-2017

<tbody>
</tbody>

Hi!

Maybe another user have a smart code for your problem (like Peter's code above), but try the formula below too (for until two projects 1 and 2 in red):

=IFERROR("P"&MID(AGGREGATE(14,6,--(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")&","),1),2,255),"")&
IFERROR(", P"&MID(AGGREGATE(14,6,--(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),2,255),"")

Ps: if you have more projects, do a small modification in the formula - add another &IFERROR(...,3),2,255),"")


Markmzz

 

[Rick Rothstein]

What if the source text had multiple "P -Numbers" like below.
Could more than 1 be extracted for a result like P1234567, P0987?

Project P1234567 and P0987 for Org 2005-2017

<tbody>
</tbody>

Here is the UDF that I posted in Message #2 modified to return all of the P-Codes in the cell...

[table="width: 500"]
[tr]
	[td]Function GetPCode(S As String) As String
  Dim V As Variant
  Const Delim As String = ", "
  For Each V In Split(S)
    If V Like "P" & String(Len(V) - 1, "#") Then GetPCode = GetPCode & Delim & V
  Next
  GetPCode = Mid(GetPCode, Len(Delim) + 1)
End Function[/td]
[/tr]
[/table]

 

[Peter_SSs]

If you don't mind mammoth formulas )) and you have the TEXTJOIN function (Excel through Office 365), I think this formula using worksheet functions should extract pretty much any number of the terms (so long as any number after the P is no more than 15 digits).
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B
1	P23 n P24 m P8 q P323 q P98 s P03 G P00 xxQ67 YY P900	P23, P24, P8, P323, P98, P03, P00, P900
2	Project P1234567 and P0987 for Org 2005-2017	P1234567, P0987
3	Nothing
4
5	Project P123456789012345 for Org 2005-2017	P123456789012345
6	Project P1234567 and P0987 for Org 2005-2017	P1234567, P0987
7	P9 P8 P76 P65 P99 P54 P4 P34 P23 P12 P09 P56	P9, P8, P76, P65, P99, P54, P4, P34, P23, P12, P09, P56
Sheet2