Extracting first set of numbers of variable length from a string

Hi,

I have thousands of references and I need the first set of numbers from the reference but unfortunately some contain letters and some are of different lengths

REF-50789-LYD-12. (I need 50789 only)
REF-4089D-LYD-125 (I need 4089 only)
REF-23400-LYD-345 (I need 23400 only)

It will always lie between the first set of '-' but will be of varying lengths and i need the numbers only.

Is this possible? I'm.struggling to do this with just the MID function. Extremely.grateful for any help-thanks

hi Lydyth,

=MID(K5,(FIND("-",K5)+1),FIND("-",SUBSTITUTE(K5,"-","@",1))-(FIND("-",K5)+1))
Use this

Thanks
Rohit

Thanks rohit, that works for variable length but still seems to pull through letters

So ref-56749d-LYD-123 ( gives me 56749d instead of 56749)

Also thanks pepe, but long story short I need to resolve with formula if possible rather than vba.

Appreciate everyone's help.:D

Wow thank you thats perfect.

I may be pushing my luck but if possible can you explain what that is doing? I'm just trying to learn as much as I can. I recognise all of the functions used, just struggling to break it down. If not thanks anyway as that seems to have worked very well!

Hi Lydyth,

you can try this.

=LOOKUP(99^99,MID(A14,MIN(IFERROR(FIND({1;2;3;4;5;6;7;8;9;0},A14),"")),ROW(INDIRECT("1:25")))+0)

Thanks
Rohit