Taking characters from the middle of a string

[doublej41]

Hi All,

Column A of my worksheet contains a list of reference numbers in the format AB-CD-125-EFG.

I need to extract the number from the middle of the sting, and have been doing so using the formula =MID(A1;7;3), which in this case will return "125".

However, some of the reference numbers now being generated only have a 2 digit number in the middle e.g. AB-CD-65-EFG. In this case the formula above returns the value "65-".

Is there a formula I can use that will only return the number and no other characters, regardless of whether the number in the middle of the string is 2 or 3 digits long?

All help is greatly appreciated.

 

[njimack]

=mid(a1,find("#",substitute(a1,"-","#",2))+1,find("~",substitute(a1,"-","~",3))-find("#",substitute(a1,"-","#",2))-1)

 

[VoG]

Try

=SUBSTITUTE(MID(A1;7;3),"-";"")

 

[doublej41]

Thanks for the replies, both work a treat.