extract "PO Number" from text.

[penambangliar]

Data Result
GRP10-0137-
GRP10-0212-PO10-0213
20032010 - MAJU TERUS-PO10-0088-GRP10-0071
20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071

Hi,

I have 4 combination text. I want to take PO number with one formula for 4 text as show above.
The bold characters is result.

For text GRP10-0137- , the result is zero (0)

Thank you for your help.

 

[hiker95]

pgc01, DonkeyOte,

Sorry for not cross posting. I was trying to find a solution for penambangliar in an area that I am trying to learn about, VBScript.RegExp.

MrExcel and Excel Forum are by far the two best sites I have come accross where I have learned so much to improve my VBA skills.

Thank you, thank you so much.

 

[hiker95]

penambangliar, pgc01, DonkeyOte,

For the data posted, here is my latest solution, per the help of pgc01, and DonkeyOte.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub GetMyPO()
' hiker95, 09/04/2010, ME492837
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  c.Offset(, 1) = GetPO(c.Value)
Next c
Columns(2).AutoFit
Application.ScreenUpdating = True
End Sub


Function GetPO(s As String)
'pgc01
'http://www.mrexcel.com/forum/showthread.php?t=492837
'
'If A1 = "20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071"
'B1 = GetPo(A1)
'Then B1 = "PO10-0087-A"
'
With CreateObject("VBScript.RegExp")
    .Pattern = "PO.*(?=-GRP)|PO.*"
    If .test(s) Then GetPO = .Execute(s)(0) Else GetPO = 0
End With
End Function

 

[pgc01]

Ok, hiker95. let's see if we are lucky and penambangliar posts a good definition of all the possible formats for the PO number and then maybe we can adjust the solution so that it is more consistent with them.

 

[penambangliar]

Ok, hiker95. let's see if we are lucky and penambangliar posts a good definition of all the possible formats for the PO number and then maybe we can adjust the solution so that it is more consistent with them.

thank for your help.

I'll try to apply formula and give you a feedback.

 

[jasonb75]

thank for your help.

I'll try to apply formula and give you a feedback.

Did my initial reply back in #2 using standard excel functions get overlooked with all the overkill attempts at providing a VBA solution?


=IF(ISERROR(FIND("PO",A1,1)),0,MID(A1,B1,ABS(MIN(FIND("PO",A1,1),FIND("GRP",A1,1))-MAX(FIND("PO",A1,1),FIND("GRP",A1,1)))-1))

The results are an exact match for your original example.

 

[DonkeyOte]

For a formula to work you would need to make some major assumptions I think.

For ex. - what happens with a string in which string "PO" appears without forming part of the PO##-#### pattern ?
You can test (COUNTIF/MATCH etc) for a string of *-PO??-????-* but you're still theoretically open to error

What happens if multiple PO patterns appear ?

A RegExp route allows for far greater certainty & flexibility over the values being looked for and returned. That said RegExp is not lightweight.

You may also want to disclose for OP the contents of B1 - I think we can deduce it but better to be transparent.

 

[GTO]

You may also want to disclose for OP the contents of B1 - I think we can deduce it but better to be transparent.

Or... for those of us who get dizzy easily when looking at formulas

 

[jasonb75]

Big slice of humble pie taken

The formula did work, I just missed an edit before posting, B1 was a temporary helper cell.

Apologies for any confusion

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 376px"><COL style="WIDTH: 97px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">GRP10-0137-</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">0</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">GRP10-0212-PO10-0213</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">PO10-0213</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20032010 - MAJU TERUS-PO10-0088-GRP10-0071</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">PO10-0088</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">PO10-0087-A</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(ISERROR(FIND("PO",A1,1)),0,MID(A1,FIND("PO",A1,1),ABS(MIN(FIND("PO",A1,1),FIND("GRP",A1,1))-MAX(FIND("PO",A1,1),FIND("GRP",A1,1)))-1))</TD></TR><TR><TD>B2</TD><TD>=IF(ISERROR(FIND("PO",A2,1)),0,MID(A2,FIND("PO",A2,1),ABS(MIN(FIND("PO",A2,1),FIND("GRP",A2,1))-MAX(FIND("PO",A2,1),FIND("GRP",A2,1)))-1))</TD></TR><TR><TD>B3</TD><TD>=IF(ISERROR(FIND("PO",A3,1)),0,MID(A3,FIND("PO",A3,1),ABS(MIN(FIND("PO",A3,1),FIND("GRP",A3,1))-MAX(FIND("PO",A3,1),FIND("GRP",A3,1)))-1))</TD></TR><TR><TD>B4</TD><TD>=IF(ISERROR(FIND("PO",A4,1)),0,MID(A4,FIND("PO",A4,1),ABS(MIN(FIND("PO",A4,1),FIND("GRP",A4,1))-MAX(FIND("PO",A4,1),FIND("GRP",A4,1)))-1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[DonkeyOte]

If you're willing to accept the assumptions required then you could (using the examples) use:

REPLACE(LEFT(A1,FIND("-GRP",A1&"-GRP")-1),1,FIND("-PO",A1),"")

for the extraction (where you deem PO to exist)