Extracting a number (part of a series of numbers) from a text string

**dannyjoer** · 11-08-2012, 10:11 AM

Hi all

I am working on a project involving a large sparepart catalogue. The dataset coantains a messy and alterning textfile in which a specific sparepart number is found.

The textfile is contained in a single cell and could look something like this:

"For T/C NA57 Serial no.1954 Spec. AD44 A4K326;Maker: Mitsui Engineering;1 stk. Clamping Sleeve 596.092 (3C-03049);Indk.pris Eur/stk xxx,xx -25% lev 2 uger ( ref: A 450168 D )"

or:

GASKET 517.085 for T/C NA48S SERIES: 1184536;PLANT INFO SIGER:;TURBOCHARGER TYPE NA48/SO1053.;Pris euro/stk xxx,xx -25% lev.tid 2 uger fra ordre;( A443765 B)

The number i want to extract/copy from the cell to an adjacent cell is the 5xx.xxx number from the lines. That way i wold be able to identify and search the whole catalogue using that unique number.

Is this possible?
I have encountered lots of trouble trying to employ my limited excel skills on the problem using the =mid, =left and =len functions.

The problem is that the number 5 for instance would appear in the cell before Excel hits the 5xx.xxx i want to find, and then copy that number to an adjacent cell.

Can you guys help? Either by formulas or VBA?

Thanks alot
/Danny

**Ron Coderre** · 11-08-2012, 10:45 AM

With your text in cell A1
Example:
A1: For T/C NA57 Serial no.1954 Spec. AD44 A4K326;Maker: Mitsui Engineering;1 stk. Clamping Sleeve 596.092 (3C-03049);Indk.pris Eur/stk xxx,xx -25% lev 2 uger ( ref: A 450168 D )

(EDITED: Yikes! Every time I look at this thing I find another section to add)
This regular formula extracts the first instance of the pattern ###.###

Please Login or Register  to view this content.

In the above example, that formula returns: 596.092

Is that something you can work with?

**dannyjoer** · 11-08-2012, 11:49 AM

Hi Ron

Thx ever so much, i works like a charm.

Just ran it in a sheet containing 1500 cells containing similar text files as mentioned, and it located the number in 95% of the cases.

The majority of the "errors" is in the number being seperated by a "-" instead of a "." But i can deal with that just changing the parameter in the code.

Just to pick your brain;

Some of the numbers look like this: 5xx.x.xxx, is there an easy way to implement that in the formula as well?

Best regards

/Danny

**Ron Coderre** · 11-08-2012, 12:51 PM

You're starting to nose into User Defined Function (UDF) territory. The formula I posted is already ugly enough. I suspect the new requirements would double its size. Would you consider using a UDF if we showed you how to add it to your workbook and use it?

If yes, I'm not an expert with regular expressions, but this seems to work.

• Select the workbook that will contain the code
• ALT+F11...to open the vba editor
• For that workbook....
...Insert.Module
• Copy the below VBA code and paste it into that module

Please Login or Register  to view this content.

That UDF returns any of these patterns:
###.###
###-###
###.#.###
###-#-###

Example on howto use that UDF for cell A1...
=sparepartnum(A1)

Is that something you can use?

**tigeravatar** · 11-08-2012, 01:26 PM

Danny,

Welcome to the forum!
This looked like an interesting challenge so I gave it a shot. I know Ron has already provided a solution, but here's what I came up.
If you are always looking for a 5xx.xxx number, then this should work:

Please Login or Register  to view this content.

However, if the number does not always start with 5, and if the length of the number is not always 7 (xxx.xxx) then this will extract it regardless (this formula also converts - into . so that it will still pull the number:

Please Login or Register  to view this content.

As for your later stated requirement of finding a different pattern, I didn't have much luck with that, so hopefully the UDF Ron provided will work for you

**FDibbins** · 11-08-2012, 01:34 PM

@ Ron you should carry that thing around in you're pocket (if it will fit) and use it against muggers and the like lol

**dannyjoer** · 11-09-2012, 04:44 AM

Thx so much guys, the formulas are solid.

@Ron, the module works perfect. I'll try to expand it even further.. That VBA thing is really powerful..

I am very impressed by this forum.

My problem is solved!
/Danny

**Ron Coderre** · 11-09-2012, 10:17 AM

I'm glad I could help!

Extracting a number (part of a series of numbers) from a text string

LinkBack

Thread Tools

Rate This Thread

Display

Extracting a number (part of a series of numbers) from a text string

Re: Extracting a number (part of a series of numbers) from a text string

Re: Extracting a number (part of a series of numbers) from a text string

Re: Extracting a number (part of a series of numbers) from a text string

Re: Extracting a number (part of a series of numbers) from a text string

Re: Extracting a number (part of a series of numbers) from a text string

Re: Extracting a number (part of a series of numbers) from a text string

Re: Extracting a number (part of a series of numbers) from a text string

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions