How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integer o

Didnt deal with the issue if behind more than 10 lengths blue highlight either. I have figured a way to fix the 1 or 11 problem. Thanks for trying Subodh_Tiwari_sktneer 

IngeborgHawighorst

Yes, I completely agree with you.

Subodh_Tiwari_sktneer 

That is the same problem I had with the formula approach above. The web page source with the HTML is the only machine readable resource to solve this issue. Looking at the HTML, it is crystal clear what the numbers are. Therefore, web scraping and parsing the HTML will be required for correct results.

Alan Skelly 

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.

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. SorrySubodh_Tiwari_sktneer 

Alan Skelly The helpers on this forum are all volunteers. Please don't offer paying them.

Alan Skelly 

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.

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

http://www.equibase.com/stats/View.cfm?tf=meet&tb=jockey&rbt=TB

Subodh_Tiwari_sktneer