Jun 01 2020 02:55 PM
62 1/2
21/2
11/2 1 1/2
4hd 4 hd
103 1/2 10 3 1/2
31/2 3 1/2
7hd 7 hd
92 9 2
11 11
54 1/2 5 4 1/2
82 8 2
Jun 01 2020 04:35 PM
Hello @Alan Skelly ,
describe the logic that you would like to apply.
Why this : 92 9 2
But this: 11 11
Why this: 103 1/2 10 3 1/2
Can you describe in words how to split the original column?
Jun 01 2020 05:36 PM
Jun 01 2020 06:27 PM
Why this : 92 9 2
But this: 11 11
Why this: 103 1/2 10 3 1/2
Can you describe in words how to split the original column?
92 is 9th position 2 yrds behind: 11 is 11 position last: 103 1/2 is 10th position 3 1/2 yrds behind..
positions could be 1 to 20 yrds behind could be 0 to 60
as is they download into one column, but I need position and yrds to be in separate columns. text to column wont work
Jun 01 2020 06:45 PM
Jun 01 2020 09:27 PM - edited Jun 01 2020 09:30 PM
'positions could be 1 to 20 yrds behind could be 0 to 60'
So, what about 11/2?? This is difficult to put into formulas if you need a human to decide that this is 1 1/2 over 11 /2.
Where do you get the data from? Would it be possible to clean up the source, before loading it into Excel?
My logic in words so far is: If the first two characters are below or equal to 20, then split off the first two characters, otherwise split off only the first character. But that doesn't work for 11/2. So how would you put the rule into words?
(How can first be 1/2 behind??)
Anyway, here is a formula that splits the data:
In C1 =IF(IFERROR(LEFT(A1,2)+0,21)<20,LEFT(A1,2),LEFT(A1,1))
In D1 =SUBSTITUTE(A1,C1,,1)
But 11/2 is problematic for the above reasons.
Jun 01 2020 10:51 PM
Ty that is exactly what I want except as you stated 11/2 that is actually 1 1/2 but could be 11 2.
1 would be 1/2 ahead instead of behind.
attached sample I would like to change fractions on import to decimal 11/2 to 1.50 etc., but excel just treats it as math 11 divided by 2 5.50 etc. if there is away to import as decimal other than fraction without it seeing it as division would be great
Jun 02 2020 07:31 PM
OK, I know what you want, but how would you word that in a rule that can be applied in Excel? How would Excel know when to split off only 1 versus splitting off 11?
The data is text. Converting 1/2 to .5 is not possible with text. The conversion you are after is not trivial.
Again: where is the data coming from? Maybe it can be formatted more appropriately at the source. Once it is in Excel, there are certain limitations, which might be easier to be solved at the source.
Jun 02 2020 07:52 PM
www.brisnet.com/cgi-bin/static.cgi?page=chart_archives_sample … There is url. You probably know how, but when I try to do web query I get error msg. I have to copy and paste. Thank you for your time! @Ingeborg Hawighorst
Jun 02 2020 08:19 PM
Hmm, that's a tough one. Looking at the web site, the numbers with the superscript formatting make a lot more sense, but when copied into Excel that formatting is lost.
It might be easier to copy the HTML source and parse that data, because in the HTML you can see which characters are in the superscript. This can be done with web scraping and would require VBA programming.
I'm afraid with copy and paste the options in Excel are limited to what I posted above.
Jun 02 2020 08:53 PM
If you know vba programming and want to do it I will pay you for your time. @Ingeborg Hawighorst
Jun 02 2020 10:18 PM
You may try this UDF to split the cell content in the desired format. See if this works for you.
Function SplitData(ByVal str As String, valType As String)
Dim matches As Object
Dim val As Variant
Dim strPos As String
Dim strBhnd As String
With CreateObject("VBScript.RegExp")
.Global = False
.Pattern = "(\d+)(\s)?((\d\/\d)|([a-z]{2}))"
If .test(str) Then
Set matches = .Execute(str)
val = matches(0).submatches(0)
If LCase(valType) = "position" Then
val = matches(0).submatches(0)
If val > 20 Then
strBhnd = Right(val, 1)
strPos = Replace(val, strBhnd, "")
Else
strPos = val
End If
SplitData = strPos + 0
ElseIf LCase(valType) = "behind" Then
If val + 0 > 20 Then
strBhnd = Right(val, 1)
strBhnd = strBhnd & matches(0).submatches(1) & matches(0).submatches(2)
Else
strBhnd = matches(0).submatches(1) & matches(0).submatches(2)
End If
SplitData = strBhnd
Else
SplitData = ""
End If
Else
val = str
If IsNumeric(val) Then
If val + 0 > 20 Then
strBhnd = Right(str, 1)
strPos = Left(val, Len(val) - 1)
If LCase(valType) = "position" Then
SplitData = strPos + 0
ElseIf LCase(valType) = "behind" Then
SplitData = strBhnd + 0
Else
SplitData = ""
End If
Else
If LCase(valType) = "position" Then
SplitData = val + 0
ElseIf LCase(valType) = "behind" Then
SplitData = val + 0
Else
SplitData = ""
End If
End If
Else
SplitData = val
End If
End If
End With
End Function
You may use this UDF on the worksheet like a regular Excel Function.
To get the position value...
=SplitData(H2,"position")
To get the behind value...
=SplitData(H2,"behind")
Refer to the attached for more details.
Jun 03 2020 05:25 AM
Thank You, It seems to mostly work haven't done large sample, because of one problem. On splittimg the "behind" part when it gets down to H8 (11) It returns 11 when it should return blank because there isn't any "behind"@Subodh_Tiwari_sktneer
Jun 03 2020 05:55 AM
SolutionThat's because you mocked up 11 in both the cells as the desired output in your sample file, refer to the cells X10 and Y10 in the sample file, and that confused me.
I have tweaked the code in the attached.
Jun 03 2020 09:51 AM
That works perfect thank you!!! Would you be willing to answer another question for me? I'll pay you for your time. I am trying to set up a web query that embeds in excel and updates automatically or with button push, but I keep getting error message. here is url I'm trying get jockey stats to update daily from website without copy and pasting into excel program
www.equibase.com/stats/View.cfm?tf=meet&tb=jockey&rbt=TB
Jun 03 2020 12:00 PM
You're welcome! Glad it worked as desired.
I don't prefer doing the web projects as they consume lot of time and the solution provided may not work correctly if the html of the page gets changed in future. So this is the max I could do for you.
Of course you may open a New Question for your web query related issue, maybe someone else would be able to help you with this.
If that takes care of your original question, please take a minute to accept the post with the solution provided as a Best Response to mark your question as Solved.
Jun 03 2020 04:41 PM
@Alan Skelly The helpers on this forum are all volunteers. Please don't offer paying them.
Jun 04 2020 11:06 AM
Hi sorry to bother you again but I had a chance to look deeper into function you gave me there are some issues. I uploaded sample with notes of issues. I ran much more and these are an example of all issues that came up. If you might want to look at it again Thank you. Also I guess I owe an apology another told me everybody volunteers here and offering $ is insulting. Sorry@Subodh_Tiwari_sktneer
Jun 04 2020 12:30 PM
I cannot find a way to deal with the scenario when as per your below criteria....
11 --> 11 Position and 0 Behind
17 --> 1 Position and 7 Behind
So this the max I could tweak to deal with the max scenarios. Replace the existing Function with the following one...
Function SplitData(rng As Range, valType As String)
Dim matches As Object
Dim str As String
Dim val As Variant
Dim strPos As String
Dim strBhnd As String
str = rng.Text
With CreateObject("VBScript.RegExp")
.Global = False
.Pattern = "(\d+)(\s)?((\d\/\d)|([a-z]{2}))"
If .test(str) Then
Set matches = .Execute(str)
val = matches(0).submatches(0)
If LCase(valType) = "position" Then
val = matches(0).submatches(0)
If val + 0 > 20 Then
If Len(val) = 2 Then
strBhnd = Right(val, 1)
strPos = Left(val, 1)
Else
strPos = Left(val, 2)
strBhnd = Right(val, Len(val) - 2)
End If
Else
strPos = val
End If
SplitData = strPos + 0
ElseIf LCase(valType) = "behind" Then
If val + 0 > 20 Then
If Len(val) > 2 Then
strBhnd = Right(val, Len(val) - 2)
Else
strBhnd = Right(val, 1)
End If
strBhnd = strBhnd & matches(0).submatches(1) & matches(0).submatches(2)
Else
strBhnd = matches(0).submatches(1) & matches(0).submatches(2)
End If
SplitData = strBhnd
Else
SplitData = ""
End If
Else
val = VBA.Trim(str)
If IsNumeric(val) Then
If val + 0 > 20 Then
strBhnd = Right(str, 1)
strPos = Left(val, Len(val) - 1)
If LCase(valType) = "position" Then
SplitData = strPos + 0
ElseIf LCase(valType) = "behind" Then
SplitData = strBhnd + 0
Else
SplitData = ""
End If
Else
If LCase(valType) = "position" Then
SplitData = val + 0
ElseIf LCase(valType) = "behind" Then
SplitData = ""
End If
End If
Else
SplitData = val
End If
End If
End With
End Function
In case it still doesn't work for some scenarios, maybe you need to adopt a different approach which I am not sure about.
Jun 03 2020 05:55 AM
SolutionThat's because you mocked up 11 in both the cells as the desired output in your sample file, refer to the cells X10 and Y10 in the sample file, and that confused me.
I have tweaked the code in the attached.