Extract string after specific characters
 

I have a worksheet with the drive paths to thousands of documents.
I would like to copy 2 sets of numbers (as string) to adjacent cells.
Example:
A1 contains "D:\OtherFiles\CLOSED\K\12-345 Smith, John\001 Estate
Planning\Correspondence\Memo.doc"
I need to extract the 6 characters after the 4th backslash ("12-345")
to B1, and the 3 characters after the 5th backslash ("001") to C1.
These numbers all fall after the 4th and 5th backslashes, but at
varying character lengths and the numbers are variable, between 01-001
and 99-999.
How do I search for those backslashes, and then copy the data to the
next cells?
Thanks!

Extract string after specific characters
 

Hi

=MID(SUBSTITUTE(A1,"\","^",4),FIND("^",SUBSTITUTE( A1,"\","^",4))+1,6)

and

=MID(SUBSTITUTE(A1,"\","^",5),FIND("^",SUBSTITUTE( A1,"\","^",5))+1,3)

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

Extract string after specific characters
 

Cool, thanks! That's close....
The only problem is that I need the data in B1 and C1 to be the actual
data from A1, and not the underlying formula itself.
Is there vb that can do this?
Sorry I didn't specify in the original post.

Extract string after specific characters
 

Copy these 2 cells and paste special as values. See Edit menu, then Paste
Special.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


" wrote:

Cool, thanks! That's close....
The only problem is that I need the data in B1 and C1 to be the actual
data from A1, and not the underlying formula itself.
Is there vb that can do this?
Sorry I didn't specify in the original post.

Extract string after specific characters
 

We can deal with that!
Thanks much for your help!

Extract string after specific characters
 

On Mon, 09 Jul 2007 14:07:29 -0700, wrote:

Cool, thanks! That's close....
The only problem is that I need the data in B1 and C1 to be the actual
data from A1, and not the underlying formula itself.
Is there vb that can do this?
Sorry I didn't specify in the original post.


You can try this. As written, it should work on the Selected Cells

=====================================
Option Explicit
Sub Extract()
Dim oRegex As Object
Dim mcMatchCollection As Object
Dim c As Range
Const sPattern As String = "(\\[\S\s]*?){3}\\([-0-9]*)[\s\S]*?\\([-0-9]*)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).NumberFormat = "@"
c.Offset(0, 2).ClearContents
c.Offset(0, 2).NumberFormat = "@"
If oRegex.Test(c.Text) = True Then
Set mcMatchCollection = oRegex.Execute(c.Text)
c.Offset(0, 1).Value = mcMatchCollection(0).SubMatches(1)
c.Offset(0, 2).Value = mcMatchCollection(0).SubMatches(2)
End If
Next c
End Sub
===================================
--ron

Extract string after specific characters
 

Nice Ron

c.Offset(0, 1).ClearContents
c.Offset(0, 1).NumberFormat = "@"
c.Offset(0, 2).ClearContents
c.Offset(0, 2).NumberFormat = "@"

could be done in 2 lines with a Resize.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Ron Rosenfeld" wrote:

On Mon, 09 Jul 2007 14:07:29 -0700, wrote:

Cool, thanks! That's close....
The only problem is that I need the data in B1 and C1 to be the actual
data from A1, and not the underlying formula itself.
Is there vb that can do this?
Sorry I didn't specify in the original post.


You can try this. As written, it should work on the Selected Cells

=====================================
Option Explicit
Sub Extract()
Dim oRegex As Object
Dim mcMatchCollection As Object
Dim c As Range
Const sPattern As String = "(\\[\S\s]*?){3}\\([-0-9]*)[\s\S]*?\\([-0-9]*)"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.Pattern = sPattern

For Each c In Selection
c.Offset(0, 1).ClearContents
c.Offset(0, 1).NumberFormat = "@"
c.Offset(0, 2).ClearContents
c.Offset(0, 2).NumberFormat = "@"
If oRegex.Test(c.Text) = True Then
Set mcMatchCollection = oRegex.Execute(c.Text)
c.Offset(0, 1).Value = mcMatchCollection(0).SubMatches(1)
c.Offset(0, 2).Value = mcMatchCollection(0).SubMatches(2)
End If
Next c
End Sub
===================================
--ron

Extract string after specific characters
 

On Wed, 11 Jul 2007 03:20:01 -0700, Wigi
wrote:

Nice Ron

Thanks



c.Offset(0, 1).ClearContents
c.Offset(0, 1).NumberFormat = "@"
c.Offset(0, 2).ClearContents
c.Offset(0, 2).NumberFormat = "@"

could be done in 2 lines with a Resize.

Good suggestion:


c.Offset(0, 1).Resize(1, 2).ClearContents
c.Offset(0, 1).Resize(1, 2).NumberFormat = "@"


--ron