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.

[TABLE="width: 404"]
<tbody>[TR]
[TD]Source Cell[/TD]
[TD]Required Results[/TD]
[/TR]
[TR]
[TD]Project P1234567 for Org 2005-2017[/TD]
[TD]P1234567[/TD]
[/TR]
[TR]
[TD]P012345[/TD]
[TD]P012345[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]


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.
[TABLE="width: 404"]
<tbody>[TR]
[TD]Source Cell[/TD]
[TD]Required Results[/TD]
[/TR]
[TR]
[TD]Project P1234567 for Org 2005-2017[/TD]
[TD]P1234567[/TD]
[/TR]
[TR]
[TD]P012345[/TD]
[TD]P012345[/TD]
[/TR]
</tbody>[/TABLE]
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?
[TABLE="class: cms_table, width: 404"]
<tbody>[TR]
[TD]Project P1234567 and P0987 for Org 2005-2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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?
[TABLE="class: cms_table, width: 404"]
<tbody>[TR]
[TD]Project P1234567 and P0987 for Org 2005-2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?
[TABLE="class: cms_table, width: 404"]
<tbody>[TR]
[TD]Project P1234567 and P0987 for Org 2005-2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?
[TABLE="class: cms_table, width: 404"]
<tbody>[TR]
[TD]Project P1234567 and P0987 for Org 2005-2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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