for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

1 lurker | 102 watchers

Ted Nov 2018 3:25pm, 23 Nov 2018 14,594 posts	Quote Pin Ted Why not? Someone needs to.
JCB Nov 2018 3:24am, 24 Nov 2018 178 posts	Quote Pin JCB Diogenes, does this work for you? It's VBA: Function GetTokens(str) GetTokens = "" For i = 1 To Len(str) If Mid$(str, i, 1) = "." Then ' possible token, go back until hit space or the start found = False pos = i - 1 Do Until found Or pos < 1 ' do until non alphanumeric char = UCase(Mid$(str, pos, 1)) If Not (char >= "A" And char <= "Z") And Not (char >= "0" And char <= "9") Then found = True Else pos = pos - 1 End If Loop If found Then possToken = Mid$(str, pos + 1, i - pos - 1) Else possToken = Mid$(str, 1, i - 1) End If If IsValidToken(possToken) Then GetTokens = GetTokens + possToken + "," End If End If Next If GetTokens <> "" Then GetTokens = Left(GetTokens, Len(GetTokens) - 1) End If End Function Private Function IsValidToken(str) hasAlpha = False hasNum = False finished = False pos = 1 posMax = Len(str) Do Until (hasAlpha And hasNum) Or pos > posMax char = UCase(Mid$(str, pos, 1)) If char >= "A" And char <= "Z" Then hasAlpha = True Else If char >= "0" And char <= "9" Then hasNum = True End If End If pos = pos + 1 Loop IsValidToken = hasAlpha And hasNum End Function In any cell you can just call it like this: =GetTokens(A1) The value returned is a comma delimited list of 'tokens'. A token is a combination of alphanumeric text before a '.'. The token must contain at least one alpha character and one number to be classed as a token. This definition seems to work with the examples you've given, but it won't work with something like '14. This happened.'
lark-the-herald-angels-sing-im Dec 2018 12:44pm, 14 Dec 2018 6,271 posts	Quote Pin lark-the-herald-angels-sing-im Google sheets - I'm using importhtml() and query() to bring in some data from RunBritain. But as the times are read in the data is confused between times which do and which do not have leading hours. So for example a 10k time of 39m40 would come in as 39:40:00.000 but a marathon time of 3hr30 would come in as 03:30:00.000 I'd like to parse the import into a single formula and allow the range to be dynamic so I don't have a redundant column of data manipulating the imported time. Is there a way to manipulate the "column" (in SQL terms) so that when =query(importhtml(),"SQL") imports it the data is pre-corrected?
GlennR Dec 2018 12:49pm, 14 Dec 2018 39,827 posts	Quote Pin GlennR I'm puzzled as to how the information after the final : can mean something different when extracted from the same data set.
lark-the-herald-angels-sing-im Dec 2018 1:07pm, 14 Dec 2018 6,272 posts	Quote Pin lark-the-herald-angels-sing-im Me too. But I think that as importhtml parses the data as at reads it (presumably line by line) as it is reading left to right it sees "Number-colon-number-sometimescolon-sometimesnumber" so assumes the left-most digit has a consistent meaninng. When it doesn't.
lark-the-herald-angels-sing-im Dec 2018 1:08pm, 14 Dec 2018 6,273 posts	Quote Pin lark-the-herald-angels-sing-im It's the runner's history table on runbritain - for "John Smith" see runbritainrankings.com
GlennR Dec 2018 1:11pm, 14 Dec 2018 39,830 posts	Quote Pin GlennR Yes, but all of the number I can see have seconds as the final two digits, so in principle there shouldn't be a problem.
lark-the-herald-angels-sing-im Dec 2018 1:14pm, 14 Dec 2018 6,274 posts	Quote Pin lark-the-herald-angels-sing-im Agree, but when it appears in Google Sheets this is what it returns:- Event Time 10M 88:15:00 parkrun 27:01:00 parkrun 25:10:00 parkrun 24:57:00 HM 02:01:33 And he's not run three 24+hour parkruns
lark-the-herald-angels-sing-im Dec 2018 1:16pm, 14 Dec 2018 6,275 posts	Quote Pin lark-the-herald-angels-sing-im It may be a limitation of importhtml() and I'd need to use importxml() instead, but that is messy and tricky.
GlennR Dec 2018 1:46pm, 14 Dec 2018 39,832 posts	Quote Pin GlennR Annoying, isn't it? Obviously to automate the process we need some sort of fixed marker.