ignore first 10 characters with find and replace
Phil,
Select the cells that you want to convert, and run the macro below. Change
IgnoreLen = 10
to a smaller number if it misses some dates that it should convert, or to a larger number if it
converts some names that it should have ignored.
HTH,
Bernie
MS Excel MVP
Sub MonthNameReplace()
Dim myR As Range
Dim i As Integer
Dim IgnoreLen As Integer
IgnoreLen = 10
Set myR = Selection
myR.Offset(0, 1).Resize(, 2).EntireColumn.Insert
myR.Offset(0, 1).FormulaR1C1 = "=LEFT(RC[-1]," & IgnoreLen & ")"
myR.Offset(0, 2).FormulaR1C1 = "=MID(RC[-2]," & IgnoreLen + 1 & ",LEN(RC[-2]))"
With myR.Offset(0, 1).Resize(, 2)
.Value = .Value
End With
For i = 1 To 12
myR.Offset(0, 2).replace What:=Format(DateValue(i & "/25/2007"), "mmmm "), _
Replacement:=i & "/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
With myR.Offset(0, 2)
.Value = .Value
End With
myR.FormulaR1C1 = "=RC[1] & RC[2]"
myR.Value = myR.Value
myR.Offset(0, 1).Resize(, 2).EntireColumn.Delete
End Sub
"Phil Cowley" wrote in message
...
Hi all, hope someone can help with this:
I'm working with a list of invoices, where the narrative for each is a name
followed by a period. I'm trying to convert any month namesin this string
into dates (e.g. "May" into "5/1-5/31") using find and replace through VBA,
but I keep coming across people with names that are months ("April", "May",
etc). Is it possible to make find and replace ignore the first 10 characters
of a string when searching for the text to replace?
I'm working on Office 2003
Any help on this would be gratefully recevied, I've been tearing my hair out
over this!
--
Regards,
Phil
|