Forum Discussion
How can I seperate from 1 column to 2 columns. using function. cant use text to column, no integer o
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.
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
'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.
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
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.
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