Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 13th 07, 06:16 PM posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2006
Posts: 21
Default How to extract data from File Path

I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents

I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.

How can I do this quickly?


  #2   Report Post  
Old June 13th 07, 06:22 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,123
Default How to extract data from File Path

Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DK" wrote in message oups.com...
I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents

I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.

How can I do this quickly?

  #3   Report Post  
Old June 13th 07, 06:31 PM posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2006
Posts: 21
Default How to extract data from File Path

That would extract all the data which has \ as separator in the
columns. I just want 3rd and 4th. There is no specific length of the
text. If there had been, I would have used MID.

On Jun 13, 1:22 pm, "Ron de Bruin" wrote:
Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"DK" wrote in ooglegroups.com...
I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents


I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.


How can I do this quickly?- Hide quoted text -


- Show quoted text -



  #4   Report Post  
Old June 13th 07, 06:37 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,290
Default How to extract data from File Path


Another approach...

Sub tested()
Dim str As String
Dim var As Variant
str = "I:\Account_Documents\ABC\ABCXYZ\GOV Documents"
'zero based array is returned by the Split function.
var = VBA.Split(str, "\", -1)
MsgBox var(2) & vbCr & var(3)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"DK"
wrote in message I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents
I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.

How can I do this quickly?

  #5   Report Post  
Old June 13th 07, 06:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,123
Default How to extract data from File Path

See Jim's reply

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DK" wrote in message oups.com...
That would extract all the data which has \ as separator in the
columns. I just want 3rd and 4th. There is no specific length of the
text. If there had been, I would have used MID.

On Jun 13, 1:22 pm, "Ron de Bruin" wrote:
Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"DK" wrote in ooglegroups.com...
I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents


I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.


How can I do this quickly?- Hide quoted text -


- Show quoted text -





  #6   Report Post  
Old June 13th 07, 07:05 PM posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2006
Posts: 21
Default How to extract data from File Path

Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.
On Jun 13, 1:41 pm, "Ron de Bruin" wrote:
See Jim's reply

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"DK" wrote in ooglegroups.com...
That would extract all the data which has \ as separator in the
columns. I just want 3rd and 4th. There is no specific length of the
text. If there had been, I would have used MID.


On Jun 13, 1:22 pm, "Ron de Bruin" wrote:
Hi DK


Why not use DataText to columns in the menubar


--


Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm


"DK" wrote in ooglegroups.com...
I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents


I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.


How can I do this quickly?- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -



  #7   Report Post  
Old June 13th 07, 07:43 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,290
Default How to extract data from File Path


For Each rCell in rngCol.Cells
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"DK"
wrote in message
Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.

  #8   Report Post  
Old June 13th 07, 08:05 PM posted to microsoft.public.excel.programming
DK DK is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2006
Posts: 21
Default How to extract data from File Path

Hi Jim
Thanks for your help!!

I have modified the code to this:-
Sub Macro2()
'
Dim var As Variant
For Each rCell In Range("D265536")
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

It extracted the values but gives me an error Subscript out of range.
Am I doing something wrong?

Thanks again!!
On Jun 13, 2:43 pm, "Jim Cone" wrote:
For Each rCell in rngCol.Cells
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"DK" wrote in message

Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.



  #9   Report Post  
Old June 13th 07, 08:32 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,290
Default How to extract data from File Path


How can it extract the values and yet still give an error?
On what line does the error occur?
I suspect you have some short file paths and var(3) does not exist.
'--
Use Option Explicit at the top ot the module.
Declare all variables
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"DK"
wrote in message
Hi Jim
Thanks for your help!!

I have modified the code to this:-
Sub Macro2()
'
Dim var As Variant
For Each rCell In Range("D265536")
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
End Sub

It extracted the values but gives me an error Subscript out of range.
Am I doing something wrong?

Thanks again!!
On Jun 13, 2:43 pm, "Jim Cone" wrote:
For Each rCell in rngCol.Cells
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"DK" wrote in message

Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.



  #10   Report Post  
Old June 13th 07, 09:22 PM posted to microsoft.public.excel.programming
GS GS is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 364
Default How to extract data from File Path

Hi DK,

As Jim states, your code doesn't declare all the variables.
Also, you are specifying 65536 rows in the range, which is more than the
35000 you stated earlier. The Split() function is causing problems with empty
cells. You might want to include a conditional, and specify the last row.

Try:

Option Explicit

'To add to Jim's suggestion:
'
'If you want to create a full directory path in separate columns then modify
Jim's code as follows..

Sub tested()
Dim str As String, sPath1 As String, sPath2 As String
Dim var As Variant
Dim rCell As Range, rngCol As Range
Dim lLastRow As Long, r As Long

str = InputBox("Enter the range to start extracting the path data from")
If str = "" Then Exit Sub '//user cancels
With ActiveSheet
lLastRow = .Cells(Rows.Count, .Range(str).Column).End(xlUp).Row
Set rngCol = .Range(str).EntireColumn
End With

With rngCol
For r = 2 To lLastRow
'zero based array is returned by the Split function.
If Not .Cells(r, 1) = "" Then
var = VBA.Split(.Cells(r).Text, "\", , vbTextCompare)

sPath1 = var(0) & "\" & var(1) & "\" & var(2)
sPath2 = sPath1 & "\" & var(3)

' .Cells(r, 1).Offset(0, 3).Value = var(2)
.Cells(r, 1).Offset(0, 3).Value = sPath1
' .Cells(r, 1).Offset(0, 4).Value = var(3)
.Cells(r, 1).Offset(0, 4).Value = sPath2
End If
Next
End With
End Sub

hth
Best regards,
Garry


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to extract only file name from folder path string in Excel? JayKay Excel Worksheet Functions 1 March 20th 09 05:57 PM
Formula too long - new file path is shorter than old file path - Excel 2003 Greg J Excel Worksheet Functions 1 November 22nd 06 06:16 PM
Using Relative path for XML data file? Mark W. Robbins Charts and Charting in Excel 0 June 7th 06 06:41 AM
Extract Path From String Andibevan[_2_] Excel Programming 2 June 15th 05 07:04 PM
Extract full path given filename Kim J. Excel Programming 3 October 14th 04 04:38 AM


All times are GMT +1. The time now is 08:59 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017