Marco - I need to trim/move the text from the beginning of each ce

S

suestew

I need to cut everything up to and including "v." from each row. The first
row below should read "County of Suffolk, 2007 WL 4565160 (E.D.N.Y. Dec. 21,
2007)"

Toussie v. County of Suffolk, 2007 WL 4565160 (E.D.N.Y. Dec. 21, 2007)
TPS, Inc. v. United States Dept. of Defense, 330 F.3d 1191 (9th Cir. 2003)
Tracy v. Fin. Ins. Mgmt. Corp., 2005 WL 2100261 (S.D. Ind. Aug. 22, 2005)
Trammell v. Anderson Coll., 2006 WL 1997425 (D.S.C. July 17, 2006)

Any help?
 
D

Don Guillett

Sub parsestring1()
On Error Resume Next
For Each c In Range("d3:d7")

x = InStr(c, "v") + 2
c.Offset(, 1) = Right(c, Len(c) - x)
'above for testing if OK delete and uncomment below line
'c.value = Right(c, Len(c) - x)

Next c
End Sub
 
J

JLGWhiz

You didn't say which column the data is in. This macro assumes it is in
column A.
You can modify accordingly.

Sub getCase()
Dim x As Long, s As Long, c As Range
For Each c In Range("A" & Cells(Rows.Count, 1).End(xlUp).Row)
If InStr(c.Value, "v") > 0 Then
x = InStr(Range("A2"), "v") + 2
s = Right(Range("A2"), Len(Range("A2").Value) - x)
MsgBox s 'For test only, substitute cells posting code
End If
Next
End Sub
 
J

JLGWhiz

Just realized I left test syntax in the code.
This will work better.

Sub getCase()
For Each c In Range("A" & Cells(Rows.Count, 1).End(xlUp).Row)
If InStr(c.Value, "v") > 0 Then
x = InStr(c.Value, "v") + 2
s = Right(c.Value, Len(Range("A2").Value) - x)
MsgBox s
End If
Next
End Sub

If any of the lines do not have a space after v. then it will clip the first
letter of the next word off. Be sure to edit for that.
 
R

Ron Rosenfeld

Sub parsestring1()
On Error Resume Next
For Each c In Range("d3:d7")

x = InStr(c, "v") + 2
c.Offset(, 1) = Right(c, Len(c) - x)
'above for testing if OK delete and uncomment below line
'c.value = Right(c, Len(c) - x)

Next c
End Sub
Click to expand...

This doesn't work if there is a "v" in the first name. e.g:

Travelers Ins v. Valerie Cohn, 2006 WL 1997425 (D.S.C. July 17, 2006)

--ron
 
R

Ron Rosenfeld

I need to cut everything up to and including "v." from each row. The first
row below should read "County of Suffolk, 2007 WL 4565160 (E.D.N.Y. Dec. 21,
2007)"

Toussie v. County of Suffolk, 2007 WL 4565160 (E.D.N.Y. Dec. 21, 2007)
TPS, Inc. v. United States Dept. of Defense, 330 F.3d 1191 (9th Cir. 2003)
Tracy v. Fin. Ins. Mgmt. Corp., 2005 WL 2100261 (S.D. Ind. Aug. 22, 2005)
Trammell v. Anderson Coll., 2006 WL 1997425 (D.S.C. July 17, 2006)

Any help?
Click to expand...

The macro below will place everything after the "v. " in the cell to the right
of "Selection".

============================================
Sub Defendant()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "^.*\bv\.\s+"

For Each c In Selection
If re.test(c.Value) = False Then
c.Offset(0, 1) = "no ""v. "" in string"
Else
c.Offset(0, 1).Value = re.Replace(c.Value, "")
End If
Next c
End Sub
================================================
--ron
 
R

Ron Rosenfeld

Good catch. But it does if you just add a dot after the v
v.
Click to expand...

Of course <sound of hand slapping side of head>.

I didn't look at the code closely, just the results.

It might be more robust, though, if you looked for
<space>v.<space>

Probably should be case sensitive also to avoid splitting on a name that
includes a V. (V.S. INT'L is a company name).
--ron
 
S

suestew

I'm working in a Mac. None of the macros are working. ??? Or maybe I not
doing something correctly on my end. I'll go try these on a PC but should
there be a problem?
 
O

Oorang

Well that was a little confusing, but I think this might do what yo
want.

Code
-------------------
Sub getCase()
Dim c As Excel.Range
Dim x As Long
Dim s() As String
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
s = Split(c.Value, " v. ")
c.Value = s(0)
c.Offset(0, 1).Value = s(1)
Next
End Su
 
Top