Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!

Need help - extracting data from cell
 

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!