Text to column question

[JO505]

In the attached file I want to take the raw data (college basketball standings) and display the team record as a win-loss format rather than a date using text to colums without retyping the result. I have tried formating as text but the format disapears using text to colums.

The file should explain it much clearer.

Jim O

[martindwilson]

when you use the wizerd text to columns change the "column data format" from general to text before you complete finish
you might be better using 3 formulas as text to columns will split
1 Kentucky 6-0
2 Ohio State 6-0
differently


in f2 =TRIM(LEFT(C2,2))
in g2 =TRIM(MID(SUBSTITUTE(TRIM(C2)," ",REPT(" ",50)),48,25))
in h2 =TRIM(RIGHT(SUBSTITUTE(TRIM(C2)," ",REPT(" ",50)),10))

[JO505]

Martin,

The formulas work well but if the school has 2 names I only get the first name. See file.


Jim O

[martindwilson]

=if(len(trim(c2))-len(substitute(trim(c2)," ",""))=2,trim(mid(substitute(trim(c2)," ",rept(" ",50)),48,25)),trim(mid(substitute(trim(c2)," ",rept(" ",50)),48,100)))

[JO505]

Martin,


That does the trick.

If you dont mind I have a question. How does it work?

Jim O

[Haseeb Avarakkan]

Also,

F2,

=TRIM(LEFT(C2,FIND(" ",C2)-1))

G2,

=TRIM(SUBSTITUTE(REPLACE(C2,FIND(H2,C2),250,""),F2,"",1))

H2,

=TRIM(RIGHT(SUBSTITUTE(TRIM(C2)," ",REPT(" ",50)),20))