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" wrote in message
...
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" wrote in message
...
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

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