Search for a text string contained within a cell & copy to adjacent cell

Hello, new to the forum and need either a formula or macro to do the following.
Essentially, I have a list of record titles, for example:

John's ID is DPC1234
Steve's ID is DPC5432
Mary's first ID was DPC6543 but her new ID is DPC8765
Phil's ID was DPC9635 but was changed to DPC9646 on May 30

The titles above would be contained in cells A1 to A4. What I want to do is extract all the ID numbers and copy those numbers to the adjacent cells in column B. The adjacent cells would then look like:

DPC1234
DPC5432
DPC6543 DPC8765
DPC9635 DPC9646

All IDs have the same DPC prefix. Is this possible with a formula or macro?
Thank you in advance.

Assuming your text is never longer than 300 characters (if it can be, increase the three 300's to a value as large or larger than the longest possible length), you can use this formula...

=TEXT(LEFT(TRIM(MID(SUBSTITUTE($A1,"DPC",REPT(" ",300)),COLUMNS($B:B)*300,300)),4),"""DPC""0000")

Okay, in an attempt to redeem myself how about this macro solution (which will handle as many IDs as there might be)...

Please Login or Register  to view this content.