SOLVED

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

Copper Contributor

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

22 Replies

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?

Hello,

If possible, do prepare a sample Excel file with your data and upload

@Ingeborg Hawighorst

 

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 

@Alan Skelly 

 

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

 

2020-06-02_16-29-32.png

 

 

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

 

@Ingeborg Hawighorst 

@Alan Skelly 

 

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.

 

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 

@Alan Skelly 

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.

sample sheet 2 an idea not sure will work?@Ingeborg Hawighorst 

If you know vba programming and want to do it I will pay you for your time. @Ingeborg Hawighorst 

@Alan Skelly 

 

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.

 

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 

best response confirmed by Alan Skelly (Copper Contributor)
Solution

@Alan Skelly 

That'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.

 

 

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

 

@Subodh_Tiwari_sktneer 

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

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

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 

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

 

 

 

1 best response

Accepted Solutions
best response confirmed by Alan Skelly (Copper Contributor)
Solution

@Alan Skelly 

That'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.

 

 

View solution in original post