Forum Discussion

Alan Skelly's avatar
Alan Skelly
Copper Contributor
Jun 01, 2020
Solved

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

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   ...
excel
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Jun 03, 2020

    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.

     

     

IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Jun 03, 2020

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.

 

Alan Skelly's avatar
Alan Skelly
Copper Contributor
Jun 03, 2020

http://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!   IngeborgHawighorst 

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Jun 03, 2020

    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.

    • Alan Skelly's avatar
      Alan Skelly
      Copper Contributor
      Jun 03, 2020

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

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Jun 03, 2020

        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.

         

Resources