Need to Extract Text

[hardeep.kanwar]

Hi

I want to extract the Text after PO mentioned in below text

Note: All PO number are 14 Digit Alphanumaric

1	BEING BILL NO JG/18-19/251 AGAINST HERO'S PO NO-CHN:18-19:1517, PO DATE-6-8-2018,ACTIVITY TOWARDS -LOAN MELA (DOWN PAYMENT RS./-1) BARNALA, DURATION-22/06/2018 TO 24/6/2018
2	BEING BILL NO JG/18-19/250 AGAINST HERO'S PO NO-CHN:18-19:1518,PO DATE-6-8-2018,ACTIVITY-(5)-LOAN MELA AT OPP. BHAGWAN SUGAR MILL, DHURI, DURATION-25/07/2018 TO 27/7/2018
3	BEING BILL NO JG/18-19/252 AGAINST HERO'S PO -CHN:18-19:1519, PO DATE-06-Aug-18, ACTIVITY TOWARDS -(5) LOAN MELA (DOWN PAYMENT RS./-1) SANGRUR, DURATION-22/06/2018 TO 24/6/2018.
4	Being Invoice no A02-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:4095\RB\017 Dated 03/04/2018
5	Being Invoice no A01-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:3450\RBA\015 Dated 03/04/2018

<tbody>
</tbody>

Thanks in Advance

Hardeep Kanwar

 

[jtakw]

Hi,

See if this helps:

Book1
	A	B
1	BEING BILL NO JG/18-19/251 AGAINST HERO'S PO NO-CHN:18-19:1517, PO DATE-6-8-2018,ACTIVITY TOWARDS -LOAN MELA (DOWN PAYMENT RS./-1) BARNALA, DURATION-22/06/2018 TO 24/6/2018	CHN:18-19:1517
2	BEING BILL NO JG/18-19/250 AGAINST HERO'S PO NO-CHN:18-19:1518,PO DATE-6-8-2018,ACTIVITY-(5)-LOAN MELA AT OPP. BHAGWAN SUGAR MILL, DHURI, DURATION-25/07/2018 TO 27/7/2018	CHN:18-19:1518
3	BEING BILL NO JG/18-19/252 AGAINST HERO'S PO -CHN:18-19:1519, PO DATE-06-Aug-18, ACTIVITY TOWARDS -(5) LOAN MELA (DOWN PAYMENT RS./-1) SANGRUR, DURATION-22/06/2018 TO 24/6/2018.	CHN:18-19:1519
4	Being Invoice no A02-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:4095\RB\017 Dated 03/04/2018	BPL:17-18:4095
5	Being Invoice no A01-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:3450\RBA\015 Dated 03/04/2018	BPL:17-18:3450
Sheet211

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(MID(TRIM(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("PO",A1),255),"NO",""),"-"," ")),4,14)," ","-")

Formula copied down.

 

[Fennek]

Hi,

try this:

for i = 1 to cells(rows.count, 1).end(xlup).row
    for b = 1 to len(cells(i, 1) - 14
        if mid(cells(i, 1), b, 14) like "[A-Z][A-Z][A-Z] ##-## ####" then 
            cells(i,2) = mid(cells(i, 1),b+14)
        end if
    next b
next i

(not testet)

regards

 

[hardeep.kanwar]

Hi

Could you pls tell me how to use this code?

Hi,

try this:

for i = 1 to cells(rows.count, 1).end(xlup).row
    for b = 1 to len(cells(i, 1) - 14
        if mid(cells(i, 1), b, 14) like "[A-Z][A-Z][A-Z] ##-## ####" then 
            cells(i,2) = mid(cells(i, 1),b+14)
        end if
    next b
next i

(not testet)

regards

 

[hardeep.kanwar]

Hi

Thanks for the Formula,many cases it works,but some cases its not working (due to format), not coz of formula

Thanks again

Hi,

See if this helps:

	A	B
1	BEING BILL NO JG/18-19/251 AGAINST HERO'S PO NO-CHN:18-19:1517, PO DATE-6-8-2018,ACTIVITY TOWARDS -LOAN MELA (DOWN PAYMENT RS./-1) BARNALA, DURATION-22/06/2018 TO 24/6/2018	CHN:18-19:1517
2	BEING BILL NO JG/18-19/250 AGAINST HERO'S PO NO-CHN:18-19:1518,PO DATE-6-8-2018,ACTIVITY-(5)-LOAN MELA AT OPP. BHAGWAN SUGAR MILL, DHURI, DURATION-25/07/2018 TO 27/7/2018	CHN:18-19:1518
3	BEING BILL NO JG/18-19/252 AGAINST HERO'S PO -CHN:18-19:1519, PO DATE-06-Aug-18, ACTIVITY TOWARDS -(5) LOAN MELA (DOWN PAYMENT RS./-1) SANGRUR, DURATION-22/06/2018 TO 24/6/2018.	CHN:18-19:1519
4	Being Invoice no A02-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:4095\RB\017 Dated 03/04/2018	BPL:17-18:4095
5	Being Invoice no A01-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:3450\RBA\015 Dated 03/04/2018	BPL:17-18:3450

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet211​

Worksheet Formulas Cell Formula B1 =SUBSTITUTE(MID(TRIM(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("PO",A1),255),"NO",""),"-"," ")),4,14)," ","-") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Formula copied down.

 

[Peter_SSs]

Based on your sample data ..

Excel Workbook
	A	B
1	BEING BILL NO JG/18-19/251 AGAINST HERO'S PO NO-CHN:18-19:1517, PO DATE-6-8-2018,ACTIVITY TOWARDS -LOAN MELA (DOWN PAYMENT RS./-1) BARNALA, DURATION-22/06/2018 TO 24/6/2018	CHN:18-19:1517
2	BEING BILL NO JG/18-19/250 AGAINST HERO'S PO NO-CHN:18-19:1518,PO DATE-6-8-2018,ACTIVITY-(5)-LOAN MELA AT OPP. BHAGWAN SUGAR MILL, DHURI, DURATION-25/07/2018 TO 27/7/2018	CHN:18-19:1518
3	BEING BILL NO JG/18-19/252 AGAINST HERO'S PO -CHN:18-19:1519, PO DATE-06-Aug-18, ACTIVITY TOWARDS -(5) LOAN MELA (DOWN PAYMENT RS./-1) SANGRUR, DURATION-22/06/2018 TO 24/6/2018.	CHN:18-19:1519
4	Being Invoice no A02-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:4095\RB\017 Dated 03/04/2018	BPL:17-18:4095
5	Being Invoice no A01-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:3450\RBA\015 Dated 03/04/2018	BPL:17-18:3450
Sheet1

If this doesn't work because of different possible texts, could we see those variations and the expected results from them?

 

[Peter_SSs]

If you are looking for a vba solution, then you could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function PO(s As String) As String
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "[A-Z]{3}:\d{2}\-\d{2}:\d{4}"
  If RX.Test(s) Then PO = RX.Execute(s)(0)
End Function

Excel Workbook
	A	B
1	BEING BILL NO JG/18-19/251 AGAINST HERO'S PO NO-CHN:18-19:1517, PO DATE-6-8-2018,ACTIVITY TOWARDS -LOAN MELA (DOWN PAYMENT RS./-1) BARNALA, DURATION-22/06/2018 TO 24/6/2018	CHN:18-19:1517
2	BEING BILL NO JG/18-19/250 AGAINST HERO'S PO NO-CHN:18-19:1518,PO DATE-6-8-2018,ACTIVITY-(5)-LOAN MELA AT OPP. BHAGWAN SUGAR MILL, DHURI, DURATION-25/07/2018 TO 27/7/2018	CHN:18-19:1518
3	BEING BILL NO JG/18-19/252 AGAINST HERO'S PO -CHN:18-19:1519, PO DATE-06-Aug-18, ACTIVITY TOWARDS -(5) LOAN MELA (DOWN PAYMENT RS./-1) SANGRUR, DURATION-22/06/2018 TO 24/6/2018.	CHN:18-19:1519
4	Being Invoice no A02-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:4095\RB\017 Dated 03/04/2018	BPL:17-18:4095
5	Being Invoice no A01-2018 dated 04/04/2018 towords activity of Outdoor Display And Hording against PO BPL:17-18:3450\RBA\015 Dated 03/04/2018	BPL:17-18:3450
Sheet2