Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Here is an UDF to start with

It needs a reference to Microsoft VBScript Regular Expressions 1.0

'-------------------------------------------------
Function Fileno(sFilespec As String) As String
Static re As RegExp
Dim mc As MatchCollection

If re Is Nothing Then
Set re = New RegExp
re.Pattern = "\\(\d{5})\\"
End If

Set mc = re.Execute(sFilespec)
If mc.Count < 1 Then
Fileno = CVErr(xlErrValue)
Else
Fileno = mc(0).submatches(0)
End If

End Function
'-------------------------------------------------------

HTH
--
AP

a écrit dans le message de news:
...
Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Thank you so much for your reply.
Can you please explain this to me a little mo
1. What expressions to change in this function to get the data I want?
Does it need a specific column? My data is in column C and the
extracting is needed in Column F.
2. How to run this program?
Thanks for your help! ;o)

Ardus Petus wrote:
Here is an UDF to start with

It needs a reference to Microsoft VBScript Regular Expressions 1.0

'-------------------------------------------------
Function Fileno(sFilespec As String) As String
Static re As RegExp
Dim mc As MatchCollection

If re Is Nothing Then
Set re = New RegExp
re.Pattern = "\\(\d{5})\\"
End If

Set mc = re.Execute(sFilespec)
If mc.Count < 1 Then
Fileno = CVErr(xlErrValue)
Else
Fileno = mc(0).submatches(0)
End If

End Function
'-------------------------------------------------------

HTH
--
AP

a écrit dans le message de news:
...
Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Type Alt+F11 to get to the VBE
Right-click on VBA Project (Your Workbook)
select Insert
select Module

Copy the code I sent you
Paste it into the newly created module

Click menu ToolsReferences
check the line Microsoft VBScript Regular Expressions 1.0
click OK

Type alt+Q to get back to Excel

In your worksheet, in cell F1 enter:
=Fileno(C1)
then drag down

Et voila!

HTH
--
AP


"DK" a écrit dans le message de news:
...
Thank you so much for your reply.
Can you please explain this to me a little mo
1. What expressions to change in this function to get the data I want?
Does it need a specific column? My data is in column C and the
extracting is needed in Column F.
2. How to run this program?
Thanks for your help! ;o)

Ardus Petus wrote:
Here is an UDF to start with

It needs a reference to Microsoft VBScript Regular Expressions 1.0

'-------------------------------------------------
Function Fileno(sFilespec As String) As String
Static re As RegExp
Dim mc As MatchCollection

If re Is Nothing Then
Set re = New RegExp
re.Pattern = "\\(\d{5})\\"
End If

Set mc = re.Execute(sFilespec)
If mc.Count < 1 Then
Fileno = CVErr(xlErrValue)
Else
Fileno = mc(0).submatches(0)
End If

End Function
'-------------------------------------------------------

HTH
--
AP

a écrit dans le message de news:
...
Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Hi Kaur,
This modified reply from Don Guillett to another question should also work
for you.
Set the range as required.
Regards.

Sub ExtractNumbersFromText()
For Each c In Range("a1:a5")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
MsgBox Application.Trim(ms)
Next c
End Sub

" wrote:

Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Hi Jack,
There is a slight issue:
1. It gives me the value in a display box instead of a cell which I
think can be easily fixed.
2. The cell has more numbers like a date 07.12.03 and this macro is
picking up that as well.

Can you please suggest the changes?
Thanks!
JackL wrote:
Hi Kaur,
This modified reply from Don Guillett to another question should also work
for you.
Set the range as required.
Regards.

Sub ExtractNumbersFromText()
For Each c In Range("a1:a5")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
MsgBox Application.Trim(ms)
Next c
End Sub

" wrote:

Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Ardus,
Thanks for the instructions.
I did exactly as you stated until checking VB Script Regular
Expressions 1.0. I do not have that option. I am using Microsoft Excel
2003. Also, when I check the other two similar options, VS Debug and VB
1.0, then I got an error variable not defined.

Please help !

Ardus Petus wrote:
Type Alt+F11 to get to the VBE
Right-click on VBA Project (Your Workbook)
select Insert
select Module

Copy the code I sent you
Paste it into the newly created module

Click menu ToolsReferences
check the line Microsoft VBScript Regular Expressions 1.0
click OK

Type alt+Q to get back to Excel

In your worksheet, in cell F1 enter:
=Fileno(C1)
then drag down

Et voila!

HTH
--
AP


"DK" a écrit dans le message de news:
...
Thank you so much for your reply.
Can you please explain this to me a little mo
1. What expressions to change in this function to get the data I want?
Does it need a specific column? My data is in column C and the
extracting is needed in Column F.
2. How to run this program?
Thanks for your help! ;o)

Ardus Petus wrote:
Here is an UDF to start with

It needs a reference to Microsoft VBScript Regular Expressions 1.0

'-------------------------------------------------
Function Fileno(sFilespec As String) As String
Static re As RegExp
Dim mc As MatchCollection

If re Is Nothing Then
Set re = New RegExp
re.Pattern = "\\(\d{5})\\"
End If

Set mc = re.Execute(sFilespec)
If mc.Count < 1 Then
Fileno = CVErr(xlErrValue)
Else
Fileno = mc(0).submatches(0)
End If

End Function
'-------------------------------------------------------

HTH
--
AP

a écrit dans le message de news:
...
Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Once in ToolsReferences, you must scroll down until you find :
Microsoft VBScript Regular Expressions 1.0

Click the checkbox,
then click OK

Cheers,
--
AP

"DK" a écrit dans le message de news:
...
Ardus,
Thanks for the instructions.
I did exactly as you stated until checking VB Script Regular
Expressions 1.0. I do not have that option. I am using Microsoft Excel
2003. Also, when I check the other two similar options, VS Debug and VB
1.0, then I got an error variable not defined.

Please help !

Ardus Petus wrote:
Type Alt+F11 to get to the VBE
Right-click on VBA Project (Your Workbook)
select Insert
select Module

Copy the code I sent you
Paste it into the newly created module

Click menu ToolsReferences
check the line Microsoft VBScript Regular Expressions 1.0
click OK

Type alt+Q to get back to Excel

In your worksheet, in cell F1 enter:
=Fileno(C1)
then drag down

Et voila!

HTH
--
AP


"DK" a écrit dans le message de news:
...
Thank you so much for your reply.
Can you please explain this to me a little mo
1. What expressions to change in this function to get the data I want?
Does it need a specific column? My data is in column C and the
extracting is needed in Column F.
2. How to run this program?
Thanks for your help! ;o)

Ardus Petus wrote:
Here is an UDF to start with

It needs a reference to Microsoft VBScript Regular Expressions 1.0

'-------------------------------------------------
Function Fileno(sFilespec As String) As String
Static re As RegExp
Dim mc As MatchCollection

If re Is Nothing Then
Set re = New RegExp
re.Pattern = "\\(\d{5})\\"
End If

Set mc = re.Execute(sFilespec)
If mc.Count < 1 Then
Fileno = CVErr(xlErrValue)
Else
Fileno = mc(0).submatches(0)
End If

End Function
'-------------------------------------------------------

HTH
--
AP

a écrit dans le message de news:
...
Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Hi DK,
The message box is just to see that it is doing what you want. You can
comment it out with a ' and put the value of ms in whatever cell you want.
Use the Left function to extract the first 5 characters i.e.

Cells(row,col) = left(ms,5)

You will have to play around with the format. ms is a string but when you
place the value in a cell it thinks it's a number. i.e

Cells(row,col) = " ' " + left(ms,5) might work for you.

Regards.

"DK" wrote:

Hi Jack,
There is a slight issue:
1. It gives me the value in a display box instead of a cell which I
think can be easily fixed.
2. The cell has more numbers like a date 07.12.03 and this macro is
picking up that as well.

Can you please suggest the changes?
Thanks!
JackL wrote:
Hi Kaur,
This modified reply from Don Guillett to another question should also work
for you.
Set the range as required.
Regards.

Sub ExtractNumbersFromText()
For Each c In Range("a1:a5")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
MsgBox Application.Trim(ms)
Next c
End Sub

" wrote:

Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

If you are stuck, post your workbook at
I'll fix it.

--
AP

"DK" a écrit dans le message de news:
...
Ardus,
Thanks for the instructions.
I did exactly as you stated until checking VB Script Regular
Expressions 1.0. I do not have that option. I am using Microsoft Excel
2003. Also, when I check the other two similar options, VS Debug and VB
1.0, then I got an error variable not defined.

Please help !

Ardus Petus wrote:
Type Alt+F11 to get to the VBE
Right-click on VBA Project (Your Workbook)
select Insert
select Module

Copy the code I sent you
Paste it into the newly created module

Click menu ToolsReferences
check the line Microsoft VBScript Regular Expressions 1.0
click OK

Type alt+Q to get back to Excel

In your worksheet, in cell F1 enter:
=Fileno(C1)
then drag down

Et voila!

HTH
--
AP


"DK" a écrit dans le message de news:
...
Thank you so much for your reply.
Can you please explain this to me a little mo
1. What expressions to change in this function to get the data I want?
Does it need a specific column? My data is in column C and the
extracting is needed in Column F.
2. How to run this program?
Thanks for your help! ;o)

Ardus Petus wrote:
Here is an UDF to start with

It needs a reference to Microsoft VBScript Regular Expressions 1.0

'-------------------------------------------------
Function Fileno(sFilespec As String) As String
Static re As RegExp
Dim mc As MatchCollection

If re Is Nothing Then
Set re = New RegExp
re.Pattern = "\\(\d{5})\\"
End If

Set mc = re.Execute(sFilespec)
If mc.Count < 1 Then
Fileno = CVErr(xlErrValue)
Else
Fileno = mc(0).submatches(0)
End If

End Function
'-------------------------------------------------------

HTH
--
AP

a écrit dans le message de news:
...
Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Ardus,
I am getting a delivery failure from your email address. I am trying to
send the spreadsheet to . Is this the correct
address?
Ardus Petus wrote:
If you are stuck, post your workbook at
I'll fix it.

--
AP

"DK" a écrit dans le message de news:
...
Ardus,
Thanks for the instructions.
I did exactly as you stated until checking VB Script Regular
Expressions 1.0. I do not have that option. I am using Microsoft Excel
2003. Also, when I check the other two similar options, VS Debug and VB
1.0, then I got an error variable not defined.

Please help !

Ardus Petus wrote:
Type Alt+F11 to get to the VBE
Right-click on VBA Project (Your Workbook)
select Insert
select Module

Copy the code I sent you
Paste it into the newly created module

Click menu ToolsReferences
check the line Microsoft VBScript Regular Expressions 1.0
click OK

Type alt+Q to get back to Excel

In your worksheet, in cell F1 enter:
=Fileno(C1)
then drag down

Et voila!

HTH
--
AP


"DK" a écrit dans le message de news:
...
Thank you so much for your reply.
Can you please explain this to me a little mo
1. What expressions to change in this function to get the data I want?
Does it need a specific column? My data is in column C and the
extracting is needed in Column F.
2. How to run this program?
Thanks for your help! ;o)

Ardus Petus wrote:
Here is an UDF to start with

It needs a reference to Microsoft VBScript Regular Expressions 1.0

'-------------------------------------------------
Function Fileno(sFilespec As String) As String
Static re As RegExp
Dim mc As MatchCollection

If re Is Nothing Then
Set re = New RegExp
re.Pattern = "\\(\d{5})\\"
End If

Set mc = re.Execute(sFilespec)
If mc.Count < 1 Then
Fileno = CVErr(xlErrValue)
Else
Fileno = mc(0).submatches(0)
End If

End Function
'-------------------------------------------------------

HTH
--
AP

a écrit dans le message de news:
...
Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Jack:
This is how I modified the text
Sub ExtractNumbersFromText()
Dim row As Integer
Dim col As Integer
Dim cell As Integer
For Each c In Range("c2:c65536")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
Application.Trim (ms)
cell = Range("f2").Select
row = 2
col = 3
Cells(row, col) = "'" + Left(ms, 5)
cell = cell + 1
Next c
End Sub

There is a problem still. The value is coming in cell C2. THat is it. I
need the value in cell F2 for the cell C2 and then increment the value
until it reaches last row.
Pls recommend the changes. Thanks!
JackL wrote:
Hi DK,
The message box is just to see that it is doing what you want. You can
comment it out with a ' and put the value of ms in whatever cell you want.
Use the Left function to extract the first 5 characters i.e.

Cells(row,col) = left(ms,5)

You will have to play around with the format. ms is a string but when you
place the value in a cell it thinks it's a number. i.e

Cells(row,col) = " ' " + left(ms,5) might work for you.

Regards.

"DK" wrote:

Hi Jack,
There is a slight issue:
1. It gives me the value in a display box instead of a cell which I
think can be easily fixed.
2. The cell has more numbers like a date 07.12.03 and this macro is
picking up that as well.

Can you please suggest the changes?
Thanks!
JackL wrote:
Hi Kaur,
This modified reply from Don Guillett to another question should also work
for you.
Set the range as required.
Regards.

Sub ExtractNumbersFromText()
For Each c In Range("a1:a5")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
MsgBox Application.Trim(ms)
Next c
End Sub

" wrote:

Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Ardus,
Whoa! This has really been a long day!! :( Sorry I got the email
address wrong. Just sent it over to you at
Thanks for all your help!

DK wrote:
Ardus,
I am getting a delivery failure from your email address. I am trying to
send the spreadsheet to . Is this the correct
address?
Ardus Petus wrote:
If you are stuck, post your workbook at
I'll fix it.

--
AP

"DK" a écrit dans le message de news:
...
Ardus,
Thanks for the instructions.
I did exactly as you stated until checking VB Script Regular
Expressions 1.0. I do not have that option. I am using Microsoft Excel
2003. Also, when I check the other two similar options, VS Debug and VB
1.0, then I got an error variable not defined.

Please help !

Ardus Petus wrote:
Type Alt+F11 to get to the VBE
Right-click on VBA Project (Your Workbook)
select Insert
select Module

Copy the code I sent you
Paste it into the newly created module

Click menu ToolsReferences
check the line Microsoft VBScript Regular Expressions 1.0
click OK

Type alt+Q to get back to Excel

In your worksheet, in cell F1 enter:
=Fileno(C1)
then drag down

Et voila!

HTH
--
AP


"DK" a écrit dans le message de news:
...
Thank you so much for your reply.
Can you please explain this to me a little mo
1. What expressions to change in this function to get the data I want?
Does it need a specific column? My data is in column C and the
extracting is needed in Column F.
2. How to run this program?
Thanks for your help! ;o)

Ardus Petus wrote:
Here is an UDF to start with

It needs a reference to Microsoft VBScript Regular Expressions 1.0

'-------------------------------------------------
Function Fileno(sFilespec As String) As String
Static re As RegExp
Dim mc As MatchCollection

If re Is Nothing Then
Set re = New RegExp
re.Pattern = "\\(\d{5})\\"
End If

Set mc = re.Execute(sFilespec)
If mc.Count < 1 Then
Fileno = CVErr(xlErrValue)
Else
Fileno = mc(0).submatches(0)
End If

End Function
'-------------------------------------------------------

HTH
--
AP

a écrit dans le message de news:
...
Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Try this:

Sub ExtractNumbersFromText()
Dim row As Integer
Dim col As Integer
For Each c In Range("C2:C65536")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
row = c.Cells.row
col = c.Cells.column
'Set the + 1 below to select the column you want
Cells(row, col + 1) = "'" + Left(Trim(ms), 5)
Next c
End Sub

"DK" wrote:

Jack:
This is how I modified the text
Sub ExtractNumbersFromText()
Dim row As Integer
Dim col As Integer
Dim cell As Integer
For Each c In Range("c2:c65536")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
Application.Trim (ms)
cell = Range("f2").Select
row = 2
col = 3
Cells(row, col) = "'" + Left(ms, 5)
cell = cell + 1
Next c
End Sub

There is a problem still. The value is coming in cell C2. THat is it. I
need the value in cell F2 for the cell C2 and then increment the value
until it reaches last row.
Pls recommend the changes. Thanks!
JackL wrote:
Hi DK,
The message box is just to see that it is doing what you want. You can
comment it out with a ' and put the value of ms in whatever cell you want.
Use the Left function to extract the first 5 characters i.e.

Cells(row,col) = left(ms,5)

You will have to play around with the format. ms is a string but when you
place the value in a cell it thinks it's a number. i.e

Cells(row,col) = " ' " + left(ms,5) might work for you.

Regards.

"DK" wrote:

Hi Jack,
There is a slight issue:
1. It gives me the value in a display box instead of a cell which I
think can be easily fixed.
2. The cell has more numbers like a date 07.12.03 and this macro is
picking up that as well.

Can you please suggest the changes?
Thanks!
JackL wrote:
Hi Kaur,
This modified reply from Don Guillett to another question should also work
for you.
Set the range as required.
Regards.

Sub ExtractNumbersFromText()
For Each c In Range("a1:a5")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
MsgBox Application.Trim(ms)
Next c
End Sub

" wrote:

Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Hey JackL,
Thanks for this. It worked like a charm!! ;o) Just a slight issue, it
gives a runtime error 6, overflow.

Any ideas how to fix this?

Thank you so very much for your help!
Regards
Dilpreet

JackL wrote:

Try this:

Sub ExtractNumbersFromText()
Dim row As Integer
Dim col As Integer
For Each c In Range("C2:C65536")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
row = c.Cells.row
col = c.Cells.column
'Set the + 1 below to select the column you want
Cells(row, col + 1) = "'" + Left(Trim(ms), 5)
Next c
End Sub

"DK" wrote:

Jack:
This is how I modified the text
Sub ExtractNumbersFromText()
Dim row As Integer
Dim col As Integer
Dim cell As Integer
For Each c In Range("c2:c65536")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
Application.Trim (ms)
cell = Range("f2").Select
row = 2
col = 3
Cells(row, col) = "'" + Left(ms, 5)
cell = cell + 1
Next c
End Sub

There is a problem still. The value is coming in cell C2. THat is it. I
need the value in cell F2 for the cell C2 and then increment the value
until it reaches last row.
Pls recommend the changes. Thanks!
JackL wrote:
Hi DK,
The message box is just to see that it is doing what you want. You can
comment it out with a ' and put the value of ms in whatever cell you want.
Use the Left function to extract the first 5 characters i.e.

Cells(row,col) = left(ms,5)

You will have to play around with the format. ms is a string but when you
place the value in a cell it thinks it's a number. i.e

Cells(row,col) = " ' " + left(ms,5) might work for you.

Regards.

"DK" wrote:

Hi Jack,
There is a slight issue:
1. It gives me the value in a display box instead of a cell which I
think can be easily fixed.
2. The cell has more numbers like a date 07.12.03 and this macro is
picking up that as well.

Can you please suggest the changes?
Thanks!
JackL wrote:
Hi Kaur,
This modified reply from Don Guillett to another question should also work
for you.
Set the range as required.
Regards.

Sub ExtractNumbersFromText()
For Each c In Range("a1:a5")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
MsgBox Application.Trim(ms)
Next c
End Sub

" wrote:

Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!

Sorry for the delay. Try declaring row as Long, Int only goes to 32,767.

"DK" wrote:

Hey JackL,
Thanks for this. It worked like a charm!! ;o) Just a slight issue, it
gives a runtime error 6, overflow.

Any ideas how to fix this?

Thank you so very much for your help!
Regards
Dilpreet

JackL wrote:

Try this:

Sub ExtractNumbersFromText()
Dim row As Integer
Dim col As Integer
For Each c In Range("C2:C65536")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
row = c.Cells.row
col = c.Cells.column
'Set the + 1 below to select the column you want
Cells(row, col + 1) = "'" + Left(Trim(ms), 5)
Next c
End Sub

"DK" wrote:

Jack:
This is how I modified the text
Sub ExtractNumbersFromText()
Dim row As Integer
Dim col As Integer
Dim cell As Integer
For Each c In Range("c2:c65536")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
Application.Trim (ms)
cell = Range("f2").Select
row = 2
col = 3
Cells(row, col) = "'" + Left(ms, 5)
cell = cell + 1
Next c
End Sub

There is a problem still. The value is coming in cell C2. THat is it. I
need the value in cell F2 for the cell C2 and then increment the value
until it reaches last row.
Pls recommend the changes. Thanks!
JackL wrote:
Hi DK,
The message box is just to see that it is doing what you want. You can
comment it out with a ' and put the value of ms in whatever cell you want.
Use the Left function to extract the first 5 characters i.e.

Cells(row,col) = left(ms,5)

You will have to play around with the format. ms is a string but when you
place the value in a cell it thinks it's a number. i.e

Cells(row,col) = " ' " + left(ms,5) might work for you.

Regards.

"DK" wrote:

Hi Jack,
There is a slight issue:
1. It gives me the value in a display box instead of a cell which I
think can be easily fixed.
2. The cell has more numbers like a date 07.12.03 and this macro is
picking up that as well.

Can you please suggest the changes?
Thanks!
JackL wrote:
Hi Kaur,
This modified reply from Don Guillett to another question should also work
for you.
Set the range as required.
Regards.

Sub ExtractNumbersFromText()
For Each c In Range("a1:a5")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" Then
ms = ms & Mid(c, i, 1)
End If
Next i
MsgBox Application.Trim(ms)
Next c
End Sub

" wrote:

Hi!
I am fairly new to the programming area. And this one I really need
help on. I am importing a text file in excel and then running a macro
to extract useful data.
P:\DAVID\00019\xyz.wpd
P:\DAVID\00019\C\abc.wpd
P:\DAVID\CTC\Baker\99114\abc.wpd
P:\DAVID\CTC\Baker\99114\xyz.wpd
P:\DAVID\CTC\FGH\01161\abc.wpd

The first thing I need to do is to extract the five digit number from
this directory listing in each cell. Now, I thought it was pretty
simple because I could use Mid formula but the problem arises when the
location of the numbers change in every cell.

I am not very good at writing an IF clause for extracting this kind of
data,.

Please help!