Extracting the nth number from an alphanumeric string

**Chinchin** · 04-15-2011, 01:24 PM

Hi,

I've been trying out some things I've learned from people here and I'm currently trying to make a formula to extract the nth number from an alphanumeric string. I've managed to get the second, but the formula is getting huge already and so I'm certain there is a neater method. Any tips would be gratefully received.

Eg.

if n=1:

df34 drft 467rtthy43p --> 34
bn638m 3546mfh939 --> 638

if n=2:

df34 drft 467rtthy43p --> 467
bn638m 3546mfh939 --> 3546

etc.

Thanks a lot,

Chinchin

**NBVC** · 04-15-2011, 01:52 PM

Try adding this User Defined Function (Alt+F11, Insert|Module)

and paste into the VB editor:

Please Login or Register  to view this content.

Then use formula:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER not just ENTER

Where A1 contains the original string.

and A2 contain the "n" number you want pulled out.

**Chinchin** · 04-15-2011, 02:29 PM

Thanks - works great. Will take me a while to figure out what's going on though so I might have to come back with a question or two.

I assume this is not possible, or is prohibitively complex, without using vba?

Chinchin