Forum Discussion
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
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.
22 Replies
- Hello,
If possible, do prepare a sample Excel file with your data and upload 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?
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.
