Get serial no. out of text

**ise** · 09-01-2009, 03:48 AM

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

**Paul** · 09-01-2009, 12:58 PM

Hi Ise, and welcome to the forum.

If your cell always contains "SERIAL NO:" immediately before the serial #, and a comma always appears afterward, then this should hopefully work for you:

=MID(A1,FIND("SERIAL NO:",A1)+10,FIND(",",A1,FIND("SERIAL NO:",A1)+10)-(FIND("SERIAL NO:",A1)+9)-1)

**DonkeyOte** · 09-01-2009, 01:50 PM

Based on the same logic outlined by Paul another variant could be:

B1:
=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("SERIAL NO:",A1)+9,""),",",REPT(" ",100)),100))

**ise** · 09-02-2009, 02:32 AM

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

**DonkeyOte** · 09-02-2009, 02:48 AM

Originally Posted by ise

Sometimes it begins with SERIAL NO:, SN:, S/N:, SERIALNO:, SN-, serial no-...

Please list all the phrases etc that denote the commencement of a serial number ? Also, is the serial number always followed by a comma ?

**DonkeyOte** · 09-02-2009, 03:07 AM

If those listed are the only variants and as previously mentioned all serial numbers are followed by a comma then it would be possible to normalise the initial strings such that:

B1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"SN:","SERIAL NO:"),"SERIALNO:","SERIAL NO:"),"S/N:","SERIAL NO:"),"SN-","SERIAL NO:"),"serial no-","SERIAL NO:")
copied down

C1:
=TRIM(LEFT(SUBSTITUTE(REPLACE($B1,1,FIND("SERIAL NO:",$B1)+9,""),",",REPT(" ",100)),100))
copied down

**ise** · 09-02-2009, 02:45 AM

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

**ise** · 09-02-2009, 03:11 AM

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!

**DonkeyOte** · 09-02-2009, 03:21 AM

To be honest it's sounds as though it will be quite difficult to establish a pattern for all records... if you're saying you're only interested in 12 digit strings beginning with 52,55,57 then perhaps:

B1:
=MID(SUBSTITUTE(A1," ",""),MIN(FIND({52,55,57},SUBSTITUTE(A1&525557," ",""))),12)

But obviously the above does not confirm to your earlier requirements with Serial Numbers with constructs such as:

Originally Posted by ise

102 15712
H 100107452
H100/110230

and the above approach is still open to error... you may need to think about using Regular Expressions either via VBA UDF or via add-in like morefunc.xll but I think it would make sense to post a file containing a relatively large representative sample of your strings so that we can test more thoroughly.

**ise** · 09-02-2009, 03:40 AM

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

**DonkeyOte** · 09-02-2009, 04:04 AM

EDIT: file reloaded at 09:07 am UK time

Ise, it seems that a Serial Number always has a minimum of 5 digits - you could possibly use this as the basis for establishing the pattern for extraction... eg utilising Regular Expressions via a UDF one approach might be:

Function SNO(rngString As Range)
Dim RegExp As Object, RegExpMatch As Object
Set RegExp = CreateObject("vbscript.regexp")
With RegExp
    .Global = True
    .IgnoreCase = True
    .Pattern = "[0-9]{5,12}"
End With
Set RegExpMatch = RegExp.Execute(Replace(Replace(rngString, " ", ""),"-",""))
SNO = RegExpMatch(0)
Set RegExpMatch = Nothing
Set RegExp = Nothing
End Function

The above would omit preceding alpha char - not sure if important or not... you could alter the pattern to

[A-Z]{0,1}[0-9]{5,12}

but the above might generate some misleading results

I've attached a working sample...(you will need to ensure macros are enabled) - this is unlikely to be perfect but it may get you most of the way ...

**ise** · 09-02-2009, 05:47 AM

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

Get serial no. out of text

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Re: Get serial no. out of text

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions