Formula to find second 4 digit number in cell?

[tonywatsonhelp]

Hi Everyone,

I have to sort some data and the text is all combined into one cell,
I'd like to pull out the stock code number.
now this is the second 4 digit number in the cell
the data is in column A

here's a few examples but the 4 digit can be anywhere in the cell but I'll use a few to give you an idea of what I'm up against.

	A	B	C	D	E
1	Current text		What I want
2	3000 - Share Capital & Reserves:3006 - Retained Earnings​ <strike></strike>	<strike></strike><strike></strike>	3006
3	6100 - Utilities:6110 - Building maintenance-R&M air,heat,plum,paint,elec​ <strike></strike>	<strike></strike>	6110
4	6100 - Utilities:6108 - Lease of offices​ <strike></strike>	<strike></strike>	6108
5	6050 - Finance Charges:6056 - Bank charges​ <strike></strike>	<strike></strike>	6056
6	6000 - Professional Fees:6004 - Accountancy-accounting services​ <strike></strike>	<strike></strike>	6004
7	3000 - Share Capital & Reserves:3014 - R/Unrestricted Funds​ <strike></strike>	<strike></strike>	3014
8	2400 - Other Liabilites:2434 - O/C Credit Card Control​ <strike></strike>	<strike></strike>	2434
9
10

<tbody>
</tbody>

Please help if you can I'm totally stuck.
Thanks
Tony

 

[JackDanIce]

Is it always number after ":"? Try:

=VALUE(MID(A1,FIND(":",A1)+1,4))

 

[tonywatsonhelp]

Perfect Thanks Jack,

 

[JackDanIce]

You're welcome