Extract string after specific characters

[tonywall]

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!

 

[Guest]

Hi

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

and

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

 

[tonywall]

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.

 

[Guest]

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

 

[tonywall]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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

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

 

[Ron Rosenfeld]

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