Extract a mainly numeric string (ISSN number) from an arbitrary string

Is there some reason that is preventing you from seeking a VBA solution?

Only because I've never had applications requiring anything more than simple formulas, therefore never used VBA macros.

If VBA is the way to go here, then so be it... but I don't even know where to start. Where do I paste your code, and then what?

1. Open up the Visual Basic Editor (Alt + F11)
2. Click Insert >> Module
3. Copy and Paste the code Leith provided into Module 1 (which will appear after completing step 2)
4. Save and close the Visual Basic Editor

Now GETISSN is available as a function (a user-defined function, or "UDF") anywhere in this workbook; just like all of the other formulas/functions you are used to (=SUM, =Average, etc.).

If an ISSN you wish to analyze is in, say, Cell A1, you would enter =GETISSN(A1) and the result will appear.

@Leith - Works beautifully for all cases except ISSNs having trailing characters (returns blank) which, unfortunately, I have a lot of. Can the code be modified to handle ISSNs amidst both leading and trailing text?

@AlvaroSiza - Clear and straightforward, thank you!

Removing the "/b" portions per OnErrorGoto0's suggestion handles ISSNs with leading & trailing text, but I should have stated it needs to consider trailing numbers as well.

I have strings that include long (>8 digit) numbers so the code erroneously considers the first 8 numbers encountered as an ISSN, so it needs to check for a 9th number and if it exists, reject the 9 numbers plus all consecutive numbers after it, i.e. reject the entire numeric block of the string.

Can the code be mod'd to reject any 9+ digit number?

Numbers after the other cases (7X, 4-4, 4-3X) are too unlikely so I won't worry about them.

Sorry for being away.
I had to reformat my HD and am in the frustrating process of "rebuilding" my pc.
Will get back asap.

@OEG0 - The latest version correctly rejects non-ISSN cases, but now it's including an extra character before and after a valid ISSN.

For example, for ABC1234-5678XYZ the code returns C1234-5678X

The code looks simple but because my VBA skill is zero I don't want to risk messing it up. Can you tweak it to avoid the first and last character while preserving the current ability to reject non-ISSNs?

@ OEG0 - I'm getting a #VALUE! error on the latest code.

@ Marcol - I've only glanced at it, but it appears to handle every valid case while rejecting every invalid one, very nice. My version has about 20 helper columns

@ OEG0 - Never mind! Gosh I don't know why I even mentioned the extra first/last character; it's trivial for me to just lop them off with a formula.

Thanks all, this one's solved.

You could if you wish reduce the number of helpers, I only left them in for clarity.
However with 2003 you will not get them down to say 2 with these formulae because of nesting limitations.

I have '03 and it seems to work.
It's for my own use so either version is fine, thx again.

Could someone modify the most recent UDF to also handle a lowercase "x" ?

Thank you Leith !