In the attached workbook I am looking for a formula or an edit to the current function that will split out text and numbers from a cell. The function currently works as shown in row three of the workbook and splits out data as required. However the data (which is downloaded from the web), has changed in certain instances (see row 53) and the function no longer works correctly for those instances.
Any help will be appreciated.
Thank you in advance as always,
BigDawg15
its hard to control what websites actually put up on their sites
instead of ESPN try FBS website for data
http://www.fbschedules.com/ncaa/coll...l-schedule.php
here is what i came up with
no fancy UDF but it works
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Try this. The problem is that the pattern does not actually require a comma to be present. It is using one pattern twice, to look on each side of a comma, but it never checks for the comma.
This version uses a single pattern to match the entire string. It produces a single match, and has four submatches, so it loops four times to capture each submatch. I tested this on your data. I also changed it so that it always returns an array of four strings, which makes it much easier to test.
Function TeamScore(ByVal txt As String) Dim a As Variant, i As Long, n As Long With CreateObject("VBScript.RegExp") .Global = True .Pattern = "^(.+) (\d+),(.+) (\d+)$" a = Array("", "", "", "") If .test(txt) Then For i = 0 To 3 a(i) = Trim$(.Execute(txt)(0).submatches(i)) Next i End If TeamScore = a End With End Function
Guys,Originally Posted by 6StringJazzer
From your responses it appears I left something out. The new attached workbook should make it clearer. The team names contain "vs." until the game is played. After which the "vs." is deleted and a comma is inserted with the score for each team.
Teams that are in the top 25 rankings will have their number in front of their name (ie No.1).
I have tried the FBS website in the past but the formatting changes quite frequently and it is not updated in a timely manner with scores like ESPN. The ESPN website has worked great for the last 3 years, but this year they added the "No." and is throwing everything off.
Sorry for not making it clearer from the onset. The new workbook should make it clearer.
In my actual workbook, the score data is updated automatically and feeds the other worksheets.
Thanks again for your assistance.
BigDawg15
I made assumptions that are consistent with what you just described and my code works for both your original workbook and the one you just attached. Have you tried it?
6StringJazzer,
Yes, I have tried the function you provided in the last attached workbook. The only thing that comes up is the first team name (ie No. 10 Utah or Nevada etc.) I have tried entering the formula as an array and without and it does not change the output.
It appears your function is working correctly for the first team name but not the first team name and score and second team and score. I must be doing something wrong but the output is not as expected.
Thanks for your patience and guidence.
BigDawg15
I am just not seeing a problem. See the attached with my code, and additional fake test data. If this still isn't what you are expecting then you will need to provide a file that you have set up manually to show what results you want.
teamscore.png
6StringJazzer,
Thanks again for all your help. I downloaded your example and it worked as expected. I then copied the Function and reentered the formulas and no dice. Please see the attached actual workbook and worksheet "sheet100update", row 51.
Thanks again, its something probably stupid that I am doing on my end, but can't figure it out.
Regards,
BigDawg15
Mike
PS If you have a different idea to make it work or something I should change, please feel free to let me know.
Here's a screenshot of what I'm talking about on my end.
TeamsExample.jpg
Thank you,
Mike
When I take the example from your file (row 51) and paste it to my file, with the same code, it works. I cannot see anything wrong in the way you are using the formula. I will continue to look as time permits.
By the way, doing this with a function that returns an array is what I might call tricky--it certainly can work but it's less obvious what's going on. Here is a slightly different way to do it that provides a workaround. This one adds another argument to say which item to return, avoiding returning an array. It no longer requires an array formula.
6StringJazzer,
The work around works just fine. Thank you so much for your patience and help.
I would be interested if you ever find the problem with the other. If not, no big deal,
I have a working solution. Hopefully they won't change the formatting again next year!
Merry Christmas and Happy New Year!
Cheers,
Mike
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks