extract "PO Number" from text.

P

penambangliar

New Member
Joined
May 4, 2010
Messages
4
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.
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
maybe

=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))
 
Upvote 0
penambangliar,


Sample data before the macro and function:


Excel Workbook
AB
1GRP10-0137-
2GRP10-0212-PO10-0213
320032010 - MAJU TERUS-PO10-0088-GRP10-0071
420032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071
5
Sheet1





After the macro and function:


Excel Workbook
AB
1GRP10-0137-0
2GRP10-0212-PO10-0213PO10-0213
320032010 - MAJU TERUS-PO10-0088-GRP10-0071PO10-0088
420032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071PO10-0087-A
5
Sheet1





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).

Adding the Macro
1. Copy the below macro and function, by highlighting the macro and function code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub GetPO()
' 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) = PatternExtract(c, "PO\d{2}-\d{4}(-[A-Z]-){0,1}", 1)
Next c
Columns(2).AutoFit
Application.ScreenUpdating = True
End Sub


Function PatternExtract(rngString As Range, strPattern As String, _
            Optional boolIgnoreCase As Boolean = True, Optional lngInstance As Long = 1) As Variant
'
' Original function created by DonkeyOte , 09/03/2010, EF744451
' Modified by hiker95, 09/04/2010, ME492837
'
'=PATTERNEXTRACT(A1,"PO\d{2}-\d{4}(-[A-Z]-){0,1}",1)
'
'If A1 = "20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071"
'Then B1 = "PO10-0087-A"
'
Dim RegExp As Object, RegExpMatch As Object
On Error Resume Next
Set RegExp = CreateObject("vbscript.regexp")
With RegExp
  .Global = True
  .IgnoreCase = boolIgnoreCase
  .Pattern = strPattern
End With
Set RegExpMatch = RegExp.Execute(rngString)
If lngInstance > RegExpMatch.Count Then
  PatternExtract = 0
Else
  If Right(RegExpMatch(lngInstance - 1), 1) = "-" Then
    PatternExtract = Left(RegExpMatch(lngInstance - 1), Len(RegExpMatch(lngInstance - 1)) - 1)
  Else
    PatternExtract = RegExpMatch(lngInstance - 1)
  End If
End If
Set RegExpMatch = Nothing
Set RegExp = Nothing
End Function


Then run the "GetPO" macro.


Thank you DonkeyOte.
 
Upvote 0
Hi

If the PO number is always 9 characters long, like in the examples, try:

=LOOKUP("z",IF({1,0},"0",MID(A1,FIND("PO",A1),9)))


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">GRP10-0137-</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">GRP10-0212-PO10-0213</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">PO10-0213</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">20032010 - MAJU TERUS-PO10-0088-GRP10-0071</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">PO10-0088</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">PO10-0087</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet2</td></tr></table>
 
Upvote 0
pgc01,

The PO number string is not always 9 characters.

This:
20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071

Should return this:
PO10-0087-A


I am trying to learn Regular Expressions.

Can this Pattern:
PO\d{2}-\d{4}(-[A-Z]-){0,1}

Be modified to exclude certain characters like:
-GRP
 
Upvote 0
Hi hiker95

Thank you, I should have read the post more carefully.

So if I understand correctly, the text can:

- not have PO number
- have a PO number followed by "-GRP"
- have a PO number not followed by "-GRP". In this case the PO number goes till the end of the text

Try thid udf:

Code: 
Function GetPO(s As String)
 
With CreateObject("VBScript.RegExp")
    .Pattern = "PO.*(?=-GRP)|PO.*"
    If .test(s) Then GetPO = .Execute(s)(0) Else GetPO = 0
End With
End Function

In B1:

=GetPO(A1)


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">GRP10-0137-</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">GRP10-0212-PO10-0213</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">PO10-0213</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">20032010 - MAJU TERUS-PO10-0088-GRP10-0071</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">PO10-0088</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">PO10-0087-A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet2</td></tr></table>

Remark: hiker95, I don't use any add-in, I wrote a program to generate the html, it's not yet finished.
 
Upvote 0
hiker95 said:
PO\d{2}-\d{4}(-[A-Z]-){0,1}
Click to expand...

hiker95

Do you mean that the PO number has always the format

PO99-9999

or

PO99-9999-A

where 9 represents a digit and A a letter?

I could not get that from the OP's post, I could only get that it's from "PO" till either the end of the text of until "-GRP", whichever comes first.

penambangliar, can post the allowed formats for the PO number?
 
Upvote 0
Thank you for the heads-up, Luke.

hiker95, I'm not against crossposting, but only if it's announced as crossposting and we have the links to the other forums. Else we may be wasting time working on a solution that someone else has already posted which is frankly disagreable.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Extracting Number String from a Text String Cell
Replies
9
Views
826
Glenn Kennedy
G
J
  • Locked
  • Question
Formula Text String Help - Extract a sequence of numbers/letters from a text string
Replies
1
Views
1K
Fluff
Fluff
C
  • Solved
Extracting numbers from a text and multiplying them numbers eachother
5 6 7
Replies
68
Views
2K
cadandcode
C
ExcelNewbie2020
  • Solved
extract a recent year from a text in excel
Replies
4
Views
253
Fluff
Fluff
M
  • Solved
Need to Extract Values from a Column Relative to Text Values Above
Replies
4
Views
264
Herakles
H

Forum statistics

Threads
1,215,391
Messages
6,124,679
Members
449,179
Latest member
jacobsscoots

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top