SOLVED

Highlighted
Contributor

# 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              8   2

22 Replies
Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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?

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

Hello,

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

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

@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

Highlighted

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

Ty that is exactly what I want except as you stated 11/2 that is actually 1 1/2 but could be 11 2.

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

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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.

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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.

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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.

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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

Highlighted
Best Response confirmed by Alan Skelly (Contributor)
Solution

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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.

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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.

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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

Highlighted

# Re: How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integ

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.