Pulling Specific Text from a String of Text

srsev

New Member
Joined
Feb 7, 2009
Messages
18
I have enjoyed many of the posts and responses about pulling text from a string of text and learned some new formulas. Thank you all.

Unfortunately I did not find what I needed for my spreadsheet. It seems all the formulas I am seeing are pulling text from a string of text that start at a certain character location. My data is not all the same as far as location of the "PO#####" characers. The only commonality in each cell is the "PO". Is there a way for excel to search for "PO" and then pull up to 8 characters beginning with P?

For the examples below, I want to pull the PO numbers.
Row 1 = PO-14885
Row 2 = PO16326
Row 3 = PO16326

<TABLE style="WIDTH: 827pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1103 border=0><COLGROUP><COL style="WIDTH: 827pt; mso-width-source: userset; mso-width-alt: 40338" width=1103><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 827pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=1103 height=20>Task 001/Task/TU ABC 2/TU 794 F1 (Task:Equipment) PO-14885 TU2 794 F1 5 - COTUINATE Phone CHANGE FOR Removed WITH ABC in queue .MW.TU is ready.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Task BUNDLE 2 /AGG/SEV ABC 1/SEV 003 (ABCDEFGI:Equipment) PO16326 SEV 003 CSR 5 - ASSIGN Your tasks NUMBER/ 7750 Tool PORTS in queue .West.SEV is ready.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Task SEV 003 (Your SITE) PO16326 SEV 003 CSR 3B - ADD 7705 Tool TO T1 PATH in queue AB.West.SEV is ready.</TD></TR></TBODY></TABLE>

I get hundreds of these notifications a day and need to only pull the PO number. My spreadsheet begins on cell A2 and remains in column A but extends to row 100 +.

You can also see that the commonality is the PO. This remains constant.

Any help would be greatly appreciated. Thanks. :rolleyes: ;)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello,

Try this B2 & copy down...

=TRIM(MID(A2,SEARCH("PO",A2),8))
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
srsev,


Excel Workbook
AB
1Task 001/Task/TU ABC 2/TU 794 F1 (Task:Equipment) PO-14885 TU2 794 F1 5 - COTUINATE Phone CHANGE FOR Removed WITH ABC in queue .MW.TU is ready.PO-14885
2Task BUNDLE 2 /AGG/SEV ABC 1/SEV 003 (ABCDEFGI:Equipment) PO16326 SEV 003 CSR 5 - ASSIGN Your tasks NUMBER/ 7750 Tool PORTS in queue .West.SEV is ready.PO16326
3Task SEV 003 (Your SITE) PO16326 SEV 003 CSR 3B - ADD 7705 Tool TO T1 PATH in queue AB.West.SEV is ready.PO16326
4
Sheet1





The formula in cell B1, copied down:

=MID(A1,FIND("PO",A1,1),FIND(" ",A1,FIND("PO",A1,1))-FIND("PO",A1,1))
 

srsev

New Member
Joined
Feb 7, 2009
Messages
18
Both of these formulas worked perfectly. Thank you so much. You all are absolutely fantastic!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

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
Top