Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choping Up a String
Hi
I was wondering if anyone new exactly how to use the chop function to chop up a string. Basically what I am trying to do is chop up thre namer of a file which contains the filename with date. I want to get the date for that file name; Here is an example MY Sheet_070327_US.xls I want to chop it up so I just end up with the date. Your help is appreicated. Thanking you in advance Pam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choping Up a String
Try something like the following (untested)
fName = activeworkbook.name For I = 1 to len(fName) if mid(fname, i, 1) = "_" then tempS = mid(fName, I+1 exit for end if next for I = 1 to len(tempS) if mid(temps, i,1) = "_" then dateS = left(temps, i-1) exit for end if next msgbox dateS Bob Flanagan Macro Systems 144 Dewberry Drive Hockessin, Delaware, U.S. 19707 Phone: 302-234-9857, cell 302-584-1771 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Pam" wrote in message ups.com... Hi I was wondering if anyone new exactly how to use the chop function to chop up a string. Basically what I am trying to do is chop up thre namer of a file which contains the filename with date. I want to get the date for that file name; Here is an example MY Sheet_070327_US.xls I want to chop it up so I just end up with the date. Your help is appreicated. Thanking you in advance Pam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choping Up a String
On Mar 27, 9:49 am, "Bob Flanagan" wrote:
Try something like the following (untested) fName = activeworkbook.name For I = 1 to len(fName) if mid(fname, i, 1) = "_" then tempS = mid(fName, I+1 exit for end if next for I = 1 to len(tempS) if mid(temps, i,1) = "_" then dateS = left(temps, i-1) exit for end if next msgbox dateS Bob Flanagan Macro Systems 144 Dewberry Drive Hockessin, Delaware, U.S. 19707 Phone: 302-234-9857, cell 302-584-1771http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Pam" wrote in message ups.com... Hi I was wondering if anyone new exactly how to use the chop function to chop up a string. Basically what I am trying to do is chop up thre namer of a file which contains the filename with date. I want to get the date for that file name; Here is an example MY Sheet_070327_US.xls I want to chop it up so I just end up with the date. Your help is appreicated. Thanking you in advance Pam- Hide quoted text - - Show quoted text - If ALL of your examples have the underscore "_" in them, you can use the "Text to Columns" option in Excel (located in the Data Menu). VBA's way of doing this same operation is the SPLIT function. So, you could do something like this as well (nest the code in a For...Next loop if you have multiple entries): Sub dateOnly() Dim txtVal Dim dateVal txtVal = Range("A1").Value dateVal = Split(txtVal, "_")(1) End Sub Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choping Up a String
Hi Pam -
Here is another version that selects the date from the filename as the first 6 consecutive numeric characters. It can therefore tolerate filenames that don't conform to your example as well as extra, non-date numeric characters. It also provides a conversion from the date substring to an excel date value. Sub Pam() fname = "MY Sheet_070331_US.xls" For i = 1 To InStr(fname, ".xls") - 1 digit = Mid(fname, i, 1) If IsNumeric(digit) Then If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = i Else If i - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 If digitCount = 6 Then Exit For End If lastDigitPosition = i Else sDate = Empty digitCount = 0 End If End If Else lastDigitPosition = i sDate = emtpy digitCount = 0 End If Next i MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ "The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "yy/mm/dd") End Sub -- Jay "Pam" wrote: Hi I was wondering if anyone new exactly how to use the chop function to chop up a string. Basically what I am trying to do is chop up thre namer of a file which contains the filename with date. I want to get the date for that file name; Here is an example MY Sheet_070327_US.xls I want to chop it up so I just end up with the date. Your help is appreicated. Thanking you in advance Pam |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choping Up a String
Pam, here's a solution using VBA:
Dim var1() As String Dim Dt As Long Var1 = Split(ActiveWorkbook.Name,"_") Dt = Var1(1) -- Charles Chickering "A good example is twice the value of good advice." "Pam" wrote: Hi I was wondering if anyone new exactly how to use the chop function to chop up a string. Basically what I am trying to do is chop up thre namer of a file which contains the filename with date. I want to get the date for that file name; Here is an example MY Sheet_070327_US.xls I want to chop it up so I just end up with the date. Your help is appreicated. Thanking you in advance Pam |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choping Up a String
On Mar 27, 5:56 pm, Jay wrote:
Hi Pam - Here is another version that selects the date from the filename as the first 6 consecutive numeric characters. It can therefore tolerate filenames that don't conform to your example as well as extra, non-date numeric characters. It also provides a conversion from the date substring to an excel date value. Sub Pam() fname = "MY Sheet_070331_US.xls" For i = 1 To InStr(fname, ".xls") - 1 digit = Mid(fname, i, 1) If IsNumeric(digit) Then If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = i Else If i - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 If digitCount = 6 Then Exit For End If lastDigitPosition = i Else sDate = Empty digitCount = 0 End If End If Else lastDigitPosition = i sDate = emtpy digitCount = 0 End If Next i MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ "The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "yy/mm/dd") End Sub -- Jay "Pam" wrote: Hi I was wondering if anyone new exactly how to use the chop function to chop up a string. Basically what I am trying to do is chop up thre namer of a file which contains the filename with date. I want to get the date for that file name; Here is an example MY Sheet_070327_US.xls I want to chop it up so I just end up with the date. Your help is appreicated. Thanking you in advance Pam- Hide quoted text - - Show quoted text - Hi Jay: Thank You for your response. I Tried your suggestion I got it to work using the example name forthe book and it works fine but I should have given the real name because I am having a problem. The name of the book is 3G_Test_Analysis_CPs_070320_USA.xls. The problem I am having is when I put is the pring statements for digit it shows the digits but it does not start counting until it gets to 7, there fore I get a date 70323 instead of 070323. I know it is because of the name starting with a number. Below is the code I had to tweak to get to work. The workbook name changes everyday by date difference only. For i = 1 To InStr(workbook_name, ".xls") - 1 digit = Mid(workbook_name, i, 1) If IsNumeric(digit) Then MsgBox ("What is digit" & digit) If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = i Else If i - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 MsgBox ("What is digit count" & digitCount) MsgBox ("What is date" & sDate) If digitCount = 6 Then 'if I change the number to 5 it will hit this but it should be 6 MsgBox ("What is last position" & i) Exit For End If lastDigitPosition = i Else sDate = Empty digitCount = 0 End If End If End If Next i MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ "The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "mm/dd/yyyy") Your help is greatly appreciated! Pam 03/28/07 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choping Up a String
Hi Pam -
Test this code on your actual workbook names and see what happens. For i = 1 To InStr(workbook_name, ".xls") - 1 digit = Mid(workbook_name, i, 1) If IsNumeric(digit) Then If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = i Else If i - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 If digitCount = 6 Then Exit For lastDigitPosition = i Else sDate = Empty digitCount = 0 End If End If Else lastDigitPosition = i sDate = emtpy digitCount = 0 End If Next i MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ "The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "yy/mm/dd") -- Jay "Pam" wrote: On Mar 27, 5:56 pm, Jay wrote: Hi Pam - Here is another version that selects the date from the filename as the first 6 consecutive numeric characters. It can therefore tolerate filenames that don't conform to your example as well as extra, non-date numeric characters. It also provides a conversion from the date substring to an excel date value. Sub Pam() fname = "MY Sheet_070331_US.xls" For i = 1 To InStr(fname, ".xls") - 1 digit = Mid(fname, i, 1) If IsNumeric(digit) Then If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = i Else If i - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 If digitCount = 6 Then Exit For End If lastDigitPosition = i Else sDate = Empty digitCount = 0 End If End If Else lastDigitPosition = i sDate = emtpy digitCount = 0 End If Next i MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ "The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "yy/mm/dd") End Sub -- Jay "Pam" wrote: Hi I was wondering if anyone new exactly how to use the chop function to chop up a string. Basically what I am trying to do is chop up thre namer of a file which contains the filename with date. I want to get the date for that file name; Here is an example MY Sheet_070327_US.xls I want to chop it up so I just end up with the date. Your help is appreicated. Thanking you in advance Pam- Hide quoted text - - Show quoted text - Hi Jay: Thank You for your response. I Tried your suggestion I got it to work using the example name forthe book and it works fine but I should have given the real name because I am having a problem. The name of the book is 3G_Test_Analysis_CPs_070320_USA.xls. The problem I am having is when I put is the pring statements for digit it shows the digits but it does not start counting until it gets to 7, there fore I get a date 70323 instead of 070323. I know it is because of the name starting with a number. Below is the code I had to tweak to get to work. The workbook name changes everyday by date difference only. For i = 1 To InStr(workbook_name, ".xls") - 1 digit = Mid(workbook_name, i, 1) If IsNumeric(digit) Then MsgBox ("What is digit" & digit) If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = i Else If i - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 MsgBox ("What is digit count" & digitCount) MsgBox ("What is date" & sDate) If digitCount = 6 Then 'if I change the number to 5 it will hit this but it should be 6 MsgBox ("What is last position" & i) Exit For End If lastDigitPosition = i Else sDate = Empty digitCount = 0 End If End If End If Next i MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ "The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "mm/dd/yyyy") Your help is greatly appreciated! Pam 03/28/07 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choping Up a String
On Mar 29, 12:56 pm, Jay wrote:
Hi Pam - Test this code on your actual workbook names and see what happens. For i = 1 To InStr(workbook_name, ".xls") - 1 digit = Mid(workbook_name, i, 1) If IsNumeric(digit) Then If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = i Else If i - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 If digitCount = 6 Then Exit For lastDigitPosition = i Else sDate = Empty digitCount = 0 End If End If Else lastDigitPosition = i sDate = emtpy digitCount = 0 End If Next i MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ "The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "yy/mm/dd") -- Jay "Pam" wrote: On Mar 27, 5:56 pm, Jay wrote: Hi Pam - Here is another version that selects the date from the filename as the first 6 consecutive numeric characters. It can therefore tolerate filenames that don't conform to your example as well as extra, non-date numeric characters. It also provides a conversion from the date substring to an excel date value. Sub Pam() fname = "MY Sheet_070331_US.xls" For i = 1 To InStr(fname, ".xls") - 1 digit = Mid(fname, i, 1) If IsNumeric(digit) Then If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = i Else If i - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 If digitCount = 6 Then Exit For End If lastDigitPosition = i Else sDate = Empty digitCount = 0 End If End If Else lastDigitPosition = i sDate = emtpy digitCount = 0 End If Next i MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ "The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "yy/mm/dd") End Sub -- Jay "Pam" wrote: Hi I was wondering if anyone new exactly how to use the chop function to chop up a string. Basically what I am trying to do is chop up thre namer of a file which contains the filename with date. I want to get the date for that file name; Here is an example MY Sheet_070327_US.xls I want to chop it up so I just end up with the date. Your help is appreicated. Thanking you in advance Pam- Hide quoted text - - Show quoted text - Hi Jay: Thank You for your response. I Tried your suggestion I got it to work using the example name forthe book and it works fine but I should have given the real name because I am having a problem. The name of the book is 3G_Test_Analysis_CPs_070320_USA.xls. The problem I am having is when I put is the pring statements for digit it shows the digits but it does not start counting until it gets to 7, there fore I get a date 70323 instead of 070323. I know it is because of the name starting with a number. Below is the code I had to tweak to get to work. The workbook name changes everyday by date difference only. For i = 1 To InStr(workbook_name, ".xls") - 1 digit = Mid(workbook_name, i, 1) If IsNumeric(digit) Then MsgBox ("What is digit" & digit) If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = i Else If i - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 MsgBox ("What is digit count" & digitCount) MsgBox ("What is date" & sDate) If digitCount = 6 Then 'if I change the number to 5 it will hit this but it should be 6 MsgBox ("What is last position" & i) Exit For End If lastDigitPosition = i Else sDate = Empty digitCount = 0 End If End If End If Next i MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ "The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "mm/dd/yyyy") Your help is greatly appreciated! Pam 03/28/07- Hide quoted text - - Show quoted text - Hi Jay: I wanted to get back to you sooner to let you know your help is appreciate. I ended up using the first snippet of code you gave me once I tweak it. I think the problem was the 3G_ then name then date so what id did was replace part of the workbook_name and it worked fine for what I was trying to do. getdate = Replace(workbook_name, "3G_", "") For I = 1 To InStr(getdate, ".xls") - 1 digit = Mid(getdate, I, 1) If IsNumeric(digit) Then 'MsgBox ("What is digit" & digit) If IsEmpty(sDate) Then sDate = digit digitCount = digitCount + 1 lastDigitPosition = I Else If I - lastDigitPosition = 1 Then sDate = sDate & digit digitCount = digitCount + 1 'MsgBox ("What is digit count" & digitCount) 'MsgBox ("What is date" & sDate) If digitCount = 6 Then Exit For End If lastDigitPosition = I Else sDate = Empty digitCount = 0 End If End If End If Next I 'MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _ '"The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _ ' Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "mm/dd/yyyy") Thank you very much! Pam |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choping Up a String
On 27 Mar 2007 09:25:38 -0700, "Pam" wrote:
Hi I was wondering if anyone new exactly how to use the chop function to chop up a string. Basically what I am trying to do is chop up thre namer of a file which contains the filename with date. I want to get the date for that file name; Here is an example MY Sheet_070327_US.xls I want to chop it up so I just end up with the date. Your help is appreicated. Thanking you in advance Pam Here's another way to extract the date string. It depends on the string being six digits in length and followed by an underscore. The string is stored at colMatches(0) as a string, and can be used however. ============================== Option Explicit Sub GetDate() Const str As String = "MY Sheet_070327_US.xls" Const sPattern As String = "\d{6}(?=_)" Dim objRegExp As Object Dim colMatches As Object Set objRegExp = CreateObject("VBScript.RegExp") With objRegExp .Pattern = sPattern .IgnoreCase = True .Global = True If .Test(str) = True Then Set colMatches = .Execute(str) Debug.Print colMatches(0) End If End With End Sub ============================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
Replace Hyperlink Addresses Help 'Dim OldStr As String, NewStr As String | Excel Programming | |||
Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a) | Excel Programming | |||
Importing Long String - String Manipulation (EDI EANCOM 96a) | Excel Programming | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions |