Extracting a 'number' from text

Hi

I've been given a text file with one particular colum that is 'free text
entrry'

There is an 8 figure serial number that always starts with 39 (39xxxxxx).
However, as this has been typed in by various people, it can appear anywhere
with in the rest of the text such as: -
fred blogs 39123456 12/02/04
12.02.04 blogs fred, 39123456
fred, 39123456 12-feb-04

As many people are typing information in, some people use different methods
(commas, spaces, slashes, no spaces, no commas etc)

The only thing that is common is that the 39 figue is always 8 characters long

How can I extract the 39xxxxxx number?

TIA

=mid(A1,find("39",A1),8)

"Anthony Slater" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I've been given a text file with one particular colum that is 'free text
> entrry'
>
> There is an 8 figure serial number that always starts with 39 (39xxxxxx).
> However, as this has been typed in by various people, it can appear
> anywhere
> with in the rest of the text such as: -
> fred blogs 39123456 12/02/04
> 12.02.04 blogs fred, 39123456
> fred, 39123456 12-feb-04
>
> As many people are typing information in, some people use different
> methods
> (commas, spaces, slashes, no spaces, no commas etc)
>
> The only thing that is common is that the 39 figue is always 8 characters
> long
>
> How can I extract the 39xxxxxx number?
>
> TIA
>
>

Bob

Thanks mate, that worked a treat

"Bob Umlas" wrote:

> =mid(A1,find("39",A1),8)
>
> "Anthony Slater" <[email protected]> wrote in message
> news:[email protected]...
> > Hi
> >
> > I've been given a text file with one particular colum that is 'free text
> > entrry'
> >
> > There is an 8 figure serial number that always starts with 39 (39xxxxxx).
> > However, as this has been typed in by various people, it can appear
> > anywhere
> > with in the rest of the text such as: -
> > fred blogs 39123456 12/02/04
> > 12.02.04 blogs fred, 39123456
> > fred, 39123456 12-feb-04
> >
> > As many people are typing information in, some people use different
> > methods
> > (commas, spaces, slashes, no spaces, no commas etc)
> >
> > The only thing that is common is that the 39 figue is always 8 characters
> > long
> >
> > How can I extract the 39xxxxxx number?
> >
> > TIA
> >
> >
>
>
>

I'd use a User Defined Function:

Option Explicit
Function GetNumbers(rng As Range) As Variant

Dim iCtr As Long
Dim myStr As String
Dim myTestStr As String
Dim FoundIt As Boolean

Set rng = rng(1)
myStr = rng.Value

FoundIt = False
For iCtr = 1 To Len(myStr)
myTestStr = Mid(myStr, iCtr, 8)
If myTestStr Like "39######" Then
FoundIt = True
Exit For
End If
Next iCtr

If FoundIt = True Then
GetNumbers = "'" & myTestStr 'for Text values
'or
GetNumbers = myTestStr 'for real number values
Else
GetNumbers = ""
End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=GetNumbers(a1)

Anthony Slater wrote:
>
> Hi
>
> I've been given a text file with one particular colum that is 'free text
> entrry'
>
> There is an 8 figure serial number that always starts with 39 (39xxxxxx).
> However, as this has been typed in by various people, it can appear anywhere
> with in the rest of the text such as: -
> fred blogs 39123456 12/02/04
> 12.02.04 blogs fred, 39123456
> fred, 39123456 12-feb-04
>
> As many people are typing information in, some people use different methods
> (commas, spaces, slashes, no spaces, no commas etc)
>
> The only thing that is common is that the 39 figue is always 8 characters long
>
> How can I extract the 39xxxxxx number?
>
> TIA

--

Dave Peterson

Three easy (but not completely infalliable) options:

=MID(D2,FIND(" 39",D2,1)+3,6)
=MID(D3,SEARCH(" 39?????? ",D3 & " ",1)+3,6)

=IF(ISERROR(VALUE(MID(D4,SEARCH(" 39?????? ",D4&" ",1)+3,6))),"not found",MID(D4,SEARCH(" 39?????? ",D4&" ",1)+3,6))

What you decide to do depends on the volume and reliability of the data. I would probably go for a user defined function and some VBA code to fully validate the data.

Hope this helps

Dave

Thats excellent m8.

You've really helped me out as you help out a lot of others on this
excellent source of Excel information.

Have a wonderful day coz thanks to you, I will

"Dave Peterson" wrote:

> I'd use a User Defined Function:
>
> Option Explicit
> Function GetNumbers(rng As Range) As Variant
>
> Dim iCtr As Long
> Dim myStr As String
> Dim myTestStr As String
> Dim FoundIt As Boolean
>
> Set rng = rng(1)
> myStr = rng.Value
>
> FoundIt = False
> For iCtr = 1 To Len(myStr)
> myTestStr = Mid(myStr, iCtr, 8)
> If myTestStr Like "39######" Then
> FoundIt = True
> Exit For
> End If
> Next iCtr
>
> If FoundIt = True Then
> GetNumbers = "'" & myTestStr 'for Text values
> 'or
> GetNumbers = myTestStr 'for real number values
> Else
> GetNumbers = ""
> End If
>
> End Function
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Short course:
>
> Open your workbook.
> Hit alt-f11 to get to the VBE (where macros/UDF's live)
> hit ctrl-R to view the project explorer
> Find your workbook.
> should look like: VBAProject (yourfilename.xls)
>
> right click on the project name
> Insert, then Module
> You should see the code window pop up on the right hand side
>
> Paste the code in there.
>
> Now go back to excel.
> Into a test cell and type:
> =GetNumbers(a1)
>
> Anthony Slater wrote:
> >
> > Hi
> >
> > I've been given a text file with one particular colum that is 'free text
> > entrry'
> >
> > There is an 8 figure serial number that always starts with 39 (39xxxxxx).
> > However, as this has been typed in by various people, it can appear anywhere
> > with in the rest of the text such as: -
> > fred blogs 39123456 12/02/04
> > 12.02.04 blogs fred, 39123456
> > fred, 39123456 12-feb-04
> >
> > As many people are typing information in, some people use different methods
> > (commas, spaces, slashes, no spaces, no commas etc)
> >
> > The only thing that is common is that the 39 figue is always 8 characters long
> >
> > How can I extract the 39xxxxxx number?
> >
> > TIA
>
> --
>
> Dave Peterson
>