Extracting Custom Pattern Consisting of Numbers & Text From Alphanumeric String

[snb]

Well it's rather complicated to write concise code for inconsistent data, but in this case:

Function snb_002(c00)
    snb_002 = Join(Filter(Split(Replace(Replace(Replace(c00, "USA", " USA"), "TCI", " TCI"), ".", " . ")), "0"))
End Function

[larkspur]

book1.jpg

Thanks again snb for your updated UDF, however this extraction is just the first part of what I'm working on. So Rick's original separated field entry works just fine

Next step is to locate those individual numbers on another workbook, then return the corresponding ID number/s and value/s, which are in different cells back to individual cells on this workbook. As I'm new to macros would either of you be able to shed some light on the process (example image attached?)