How do I separate data into separate columns

[Cowichandave]

I have about 20000 entries on 1 line in this format

<TABLE style="WIDTH: 203pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=270 border=0><COLGROUP><COL style="WIDTH: 203pt; mso-width-source: userset; mso-width-alt: 9874" width=270><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 203pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=270 height=16>56.6 km Design Automobile Industries Inc -290-451-0600

How can I separate that into 3 columns

Column 1 would be 56.6 km

Column 2 would be Design Automobile Industries Inc

Column 3 would be 290-451-0600

The dash after the name would have to be deleted



</TD></TR></TBODY></TABLE>

 

[jindon]

Are they all in same format ?

1) xx km
2) anything with - at the end
3) anything after 2)
?

 

[Lincoln Six Echo]

Provided that all entries are formatted the same way, and that the first entry is in A1;

KM:
=LEFT(A1,FIND("km",A1)+1)

Company Name:
=RIGHT(LEFT(A1,LEN(A1)-(LEN(A1)-(FIND("-",A1)-2))),LEN((LEFT(A1,LEN(A1)-(LEN(A1)-(FIND("-",A1)-2)))))-(FIND("km",A1)+2))

Phone Number:
=RIGHT(A1,LEN(A1)-(FIND("-",A1)))

 

[Cowichandave]

Could be 0.1 km or as high as 100.0 km in that format.

The data is all on one line as below

56.6 km Design Automobile Industries Inc - 290-451-0600

So just need to separate mileage, name and phone number from 1 column into 3 different columns

 

[jindon]

try

B1
=Left(A1,Find("km",A1)+1)

C1
=Mid(A1,Find("km ",A1)+3,Sum(Find({"-","km "},A1)*{1,-1})-4)

D1
=Mid(A1,Find("-",A1)+1,255)

 

[Lincoln Six Echo]

If there is always a "km" in the string (regardless of the actual number of kms), and that there is a - before the phone number, formulas above will work.

In case there was an extra space in the original string ... (say two spaces before the dash, two spaces after the dash of 'km', you might want to put the above formulas in a TRIM() to remove extra spaces at the beggining or end of the strings.

 

[c_m]

If A1 has the text try this:
B1 =LEFT(A1,FIND(" km ",A1)+3)
C1 =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,"")," - "&D1,""))
D1 =TRIM(RIGHT(A1,LEN(A1)-FIND(" - ",A1)-1))

Hope this helps

 

[Cowichandave]

Works perfect. Thank you. You guys are awesome.