Get serial no. out of text

Hi follows,
Have exported oracle DB data in an Excel sheet. There is a column with text and numbers in it. I need to get out the serial numbers of this text columns of up to 12000 data fields.
My problem is that the serial number is not always the same format. There might be also two serial numbers in one text field.
One cell can look as the following:
GEYSER BURST - SERIAL NO:102 15712, 10 AUGUST 2005,150 LITRE SUPA-FLOW, EASILY ACCESIBLE. App made for today as per Victor ( Notes by Pamela on the 18/07/2008 13:30)

Out of this cell the Serial No is 102 15712.
There are other formats like:
550501 009879
H 100107452
H100/110230
Sometimes there are only four or five numbers or there are special characters between the numbers like - or /.
I would like to seperate these serial numbers in a seperate cell. There might be also a cell without any serial number in it.

Is there any other way to get these numbers out except of doing it manually?

Thank you very much for every input.
ise

Hi there,
Thanks for your fast response. With your solution I was able to get about 10% of the serial numbers. Badly there is no system within this text. Sometimes it begins with SERIAL NO:, SN:, S/N:, SERIALNO:, SN-, serial no-...
So there is no system in it. I thought it would be very hard to get them out without doing it manually. But your solution helped me alot. Thank you both!

Ise

Is there a way to get the serial numbers beginning with 52, 55 or 57? Just to get the following 13 characters. Badly there are also some serial numbers which have 2 spaces between the numbers itselfs.
550502 025767
570 502 012443 <---
520402-027925

Thank you all!
Ise

Badly not.
Following might be at the beginning (I'm not sure if the following includes all versions):
SN:
Serial no-
SERIAL NO:
SN:
SN-
s/n
SN
UNIT LEAKING--
HOT WATHER--
UNIT LEAKING-
UNIT LEAKING*-- (that I saw just once)
/
-- (might be also in the same text to divide between other information e.g. date etc.. So it can look like UNIT LEAKING--DECEMBER 2005--Followed by the serial number)
There might be also just a space because there is something in front of the number that can change (like the date).

As you see there are alot of variables in front of the serial number because these idiots couldn't fill in the number correctly. Now I have to recheck everything...^^

It might end with several variables or just a space. But there might be also a space between the serial number blocks. Or it ends with:
,
- (might also be between the serial number blocks)
space

That's mostly it. So there is no way to define one makro or function for everything. Perhaps it would be easier to search for the serial number itselfs (like my upper post reply) or we use to search for the serial number beginning with H1 or H2. Most of them have 12 digits lengh.

Thank you very much!
Ise
Thanks for your help!

Ok, followed a view data lines from my excel list:

GEYSER BURST SN: 10132645--Date: June 2004---Size: 250L **--The job is re-allocated to ** As per ** the geyser won't fit through the trap door, client has to open the roof and once the roof is open she must contact the call centre to inform us. ( Notes by ** on the 08/08/2008 13:00)--27/10/2008--11:29-- ** called concerning the claim.--**. 27/10/08 J X Projects - ** is the people who will open the roof. ** please contact ** and ask him to call them to confirm time and date. VK--** made appointment for Thursday 30/10/08 --28/10/2008--**

GEYSER BURST - SERIAL NO: H150 242 724, DECEMBER 2004, 150 LITRE - G-TECH, EASILY ACCESIBLE, MR Form

NO HOT WATER SN: 550351014015 Date: Apr 2008 Size: 150L THERMOSTAT ADJUSTED CORRECT

GEYSER BURST: SN: H150 243137 Man date: 01/2006 150L....................... Reported by **.

GEYSER BURST - SERIAL NO: 101 88644, 18 APRIL 2005, 150 LITRE - SUPAFLO. EASILY ACCESIBLE. THE JOB RE-ALLOCATED TO **

NOT WORKING-- 7.5L Hydro Boil Di ** will be going out. Serial - 53981 Dm-

NO HOT WATER-- Hydro Boil 15L--Serial no- 51218--Dm- 11/2007 Report- Remot Faulty over boil switch --COMP- 14/10/2008--**

UNIT LEAKING-- Easy access Serial no- H150- 223761 DM- June 2005

INSUFFICIENT HOT WATER - HYDROBOIL NOT GIVING HOT WATER, SERIAL NO: 50185, NOVEMBER 2007, 15 LITRES, **

NO HOT WATER - Hydroboil not working. SN: 51647 Date: Dec 2007 5L Old FRA 51286 - was fixed, but the same problem again. 15/10/08 Replaced Thermostat. VK

GEYSER BURST, H150 183273, MAY 2004, 150 LITRES -- G-TECH, EASILY ACCESIBLE AS ITS IN THE ROOF. Replaced geyser new SN: 550351-017205 Man date: 06/2008. ( ** 28/10/2008 09:47)

RUSTED-- Sink Bough 3 months ago . Asper ** we must ask ** to send out a rep to inspect the Sink and come back with a report. Have informed ** and she took the details and will attend to this call 30/10/08 - Inspection + A/H. VK

NO HOT WATER - HYDROBOIL NOT HEATING UP AND IT WAS INSTALLED SATURDAY 26th JULY 2008, SERIAL NO: 53709, 5 LITRES. As per ** he got a go ahead from ** to replace the unit. Claim on hold until Monday

GEYSER BURST - SERIAL NO: 550502-106206, JULY 2007, 150 LITRE - **, EASILY ACCESIBLE. SPOKE TO ** FROM **, WILL BE DOING THE JOB FOR US.

UNIT LEAKING - SERIAL NO: 10101332, 05 NOVEMBER 2003, SUPAFLO - 150LITRE, EASILY ACCESIBLE. SPOKE TO ** - FROM ** AND SAID THEY REPLACED THE GEYSER ALREADY NEW SERIAL NO: 550502-036614, JUNE 2008, 150 LITRE FRANKE.

GEYSER BURST: SN: H150 235146 Man date: 10/2005 150L....................Reported by ** App made for today as per **. ( Notes by ** on the 30/07/2008 12:20)

GEYSER BURST: SN: 10222195 Man date: 02/09/2005 150L Superflow As per ** the element, thermostat and anode needs replacing, but it's out of warranty

OVERBOILING / HEATING- Cobra 150L Serial no- 570502-007342 DM- SP will provide Report- SN-570502-007342--DM- not sure --COMP--22/09/2008--**

UNIT KEEPS ON TRIPPING: SN: 53642 D.O.P 05/2008 5L................. Reported by ** As per Mill they replaced faulty 1.5KW screw on element. ( Notes by ** on the 01/08/2008)

OVERBOILING / HEATING-Super flow 150L Replace the masterflow 3- PA4.331 Serial no- 520402-026234 DM- 3 April 2008 2008/09/25 - ** - Call Withdrawn

GEYSER BURST-- G-Teck - 150L-- Easy Access Serial no- 101 72253 DM- 19/01/2005 MR Form

GEYSER BURST: SN: 10188430 MAn date: 18/04/2005 150L App made for today as per **. ( Notes by ** on the 30/07/2008 14:30)

BROKEN PARTS--FRA 50084 Victor went out 2nd time replace the wiring and the plastic cap-- As per ** he replaced the electrical cover. ( Notes by ** on the 06/08/2008 11:10)

GEYSER BURST - SERIAL NO: H150 222192, MAY 2005, 150 LITRE - G-TECH.


Just deleted names and phone numbers and replaced with **. All other is just like it is in the database. Don't know if this post includes all versions of serial numbers. Can't controll 12'000 entries

Thanks dude!
Ise

Hey DonkeyOte.
Thank you very much for your macro. I checked it and programmed now a small makro together with my friend. All I need to do now is controlling the empty rows because sometime it couldn't get the SN out because its written very specially.
If you're intrested in the makro just write me a private message and I will send you the code. Would like to thank you for spending your time to solve my problem. Right now I do not need to do a lot anymore.

Ise