Text to column question

**JO505** · 11-30-2011, 06:29 PM

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** · 11-30-2011, 06:44 PM

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** · 11-30-2011, 07:11 PM

Martin,

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

Jim O

**martindwilson** · 11-30-2011, 07:26 PM

=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** · 11-30-2011, 08:04 PM

Martin,

That does the trick.

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

Jim O

**Haseeb Avarakkan** · 11-30-2011, 08:26 PM

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))