Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ignore first 10 characters with find and replace
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ignore first 10 characters with find and replace
Thanks very much for that Bernie, I've been trying to solve that (on and off)
for 3 weeks! -- Regards, Phil "Bernie Deitrick" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ignore first 10 characters with find and replace
Phil,
You're welcome - Next time, don't wait so long! Bernie MS Excel MVP Thanks very much for that Bernie, I've been trying to solve that (on and off) for 3 weeks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using wildcard characters in find & replace | Excel Worksheet Functions | |||
FIND and REPLACE characters needed | New Users to Excel | |||
FIND and REPLACE characters needed | Excel Worksheet Functions | |||
How do I find replace special characters? | Excel Discussion (Misc queries) | |||
Find and replace unusual characters ... | Excel Discussion (Misc queries) |