About Miguel Zapico Expertise I can answer question about how to use scripts to consolidate data, connect different systems and automate tasks. I have no experience on using VBScript on web programming.
Experience I have been using VBScript and Windows Scripting Host as my swiss tool for the last 6 years.
Organizations New York PC users group (NYPC)
Independant Computer Consultants Association (ICCA)
Education/Credentials Microsoft MCSE in Windows NT
Expert: Miguel Zapico Date: 11/9/2006 Subject: String Manipulation - Extract
Question Hey Miguel,
Wondering if you could help me out here. I have headers going from B1:T1 with different stock ticker symbols. For instance:
Update_^GSPC.csv
Update_AAI.csv
Update_GE.csv
Would all be examples of valid headers. I would like to get rid of the "Update_" and the ".csv" and be left with just the ticker symbol itself. Tickers can have a max of 5 characters (^GSPC) and a min of 1 character (F). The ticker symbols used as headers will be changing so the code must be adaptable enough where I can run it for any set of Ticker symbols. Some code I have been working on is posted below
Sub Ticker()
Dim strStock As String
arrStock = Array("Update_AAI.csv", "Update_AAPL.csv", "Update_C.csv")
For I = LBound(arrStock) To UBound(arrStock)
strStock = Mid(arrStock(I), InStr(arrStock(I), "_") + 1)
strStock = Left(strStock, InStrRev(strStock, ".") - 1)
Next I
End Sub
Thank you for your help
Ed
Answer Your code works, it extracts to the variable strStock the correct ticker.
In any case, if you want the code to apply to the headers, you could use a different approach, something like:
Sub ExtractTickets()
With ActiveSheet.Range("B1:T1")
.Replace What:="Update_", Replacement:="", LookAt:=xlPart
.Replace What:=".csv", Replacement:="", LookAt:=xlPart
End With
End Sub
This will get rid of the Update_ and the .csv part on any value on the range of headers.
