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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Current text[/TD]
[TD][/TD]
[TD]What I want[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]

3000 - Share Capital & Reserves:3006 - Retained Earnings​

<strike></strike>
[/TD]
[TD]<strike></strike><strike></strike>
[/TD]
[TD]3006[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]

6100 - Utilities:6110 - Building maintenance-R&M air,heat,plum,paint,elec​

<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]6110[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]

6100 - Utilities:6108 - Lease of offices​

<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]6108[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]

6050 - Finance Charges:6056 - Bank charges​

<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]6056[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]

6000 - Professional Fees:6004 - Accountancy-accounting services​

<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]6004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]

3000 - Share Capital & Reserves:3014 - R/Unrestricted Funds​

<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]3014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]

2400 - Other Liabilites:2434 - O/C Credit Card Control​

<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]2434[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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