Thread
:
Need help - extracting data from cell
View Single Post
#
13
Posted to microsoft.public.excel.programming
DK
external usenet poster
Posts: 21
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!
Reply With Quote
DK
View Public Profile
Find all posts by DK