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

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 ###.###

B1: =MID(A1,MATCH(1,INDEX(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-6)),3)))*(MID(A1,ROW(INDIRECT("4:"
&LEN(A1)-3)),1)=".")*ISNUMBER(--(MID(A1,ROW(INDIRECT("5:"&LEN(A1)-2)),3)))*ISNUMBER(--MID(A1,ROW(INDIRECT("1:"
&LEN(A1)-7)),7))*(LEN(TRIM(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-6)),7)))=7),0),0),7)

In the above example, that formula returns: 596.092

Is that something you can work with?

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

Function SparePartNum(ByVal text As String) As String

Dim result As String
Dim allMatches As Object
Dim RE As Object

result = "no match"

Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "[0-9]{3}[\.\-][0-9]{3}"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(text)

If allMatches.Count <> 0 Then
    result = allMatches.Item(0)
Else
    RE.Pattern = "[0-9]{3}[\.\-]\d[\.\-][0-9]{3}"
    Set allMatches = RE.Execute(text)
    If allMatches.Count <> 0 Then
        result = allMatches.Item(0)
    End If
End If

SparePartNum = result

End Function

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

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

Is that something you can use?

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:

=MID(A1,SEARCH(" 5*.* ",A1)+1,7)

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:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),MIN(SEARCH(" "&{1,2,3,4,5,6,7,8,9,0}&"*.* ",SUBSTITUTE(SUBSTITUTE(A1&" 1.1 2.2 3.3 4.4 5.5 6.6 7.7 8.8 9.9 0.0 ","-",".")," ",REPT(" ",255)))),255))

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

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

I'm glad I could help!