ignore first 10 characters with find and replace

[Guest]

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!

 

[Bernie Deitrick]

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

 

[Guest]

Thanks very much for that Bernie, I've been trying to solve that (on and off)
for 3 weeks!
--
Regards,

Phil

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

 

[Bernie Deitrick]

Phil,

You're welcome - Next time, don't wait so long!

Bernie
MS Excel MVP