Need help - extracting data from cell

[kaur.dilpreet]

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 Petus]

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

 

[DK]

Thank you so much for your reply.
Can you please explain this to me a little more:
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]

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 Tools>References
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" <[email protected]> a écrit dans le message de (e-mail address removed)...
Thank you so much for your reply.
Can you please explain this to me a little more:
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)

 

[JackL]

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

 

[DK]

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!

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

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!

 

[DK]

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]

Once in Tools>References, you must scroll down until you find :
Microsoft VBScript Regular Expressions 1.0

Click the checkbox,
then click OK

Cheers,
--
AP

"DK" <[email protected]> a écrit dans le message de (e-mail address removed)...
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 !

 

[JackL]

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.

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!

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

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 Petus]

If you are stuck, post your workbook at (e-mail address removed)
I'll fix it.

--
AP

"DK" <[email protected]> a écrit dans le message de (e-mail address removed)...
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 !

 

[DK]

Ardus,
I am getting a delivery failure from your email address. I am trying to
send the spreadsheet to (e-mail address removed). Is this the correct
address?

 

[DK]

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!

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.

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!

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

:

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!

 

[DK]

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

 

[JackL]

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

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!

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.

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

:

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!

 

[DK]

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

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

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!

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.

:

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

:

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!

 

[JackL]

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

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

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

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.

:

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

:

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!