How do I separate data into separate columns

Cowichandave

New Member
Joined
Jan 18, 2009
Messages
10
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Are they all in same format ?

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

Lincoln Six Echo

Board Regular
Joined
Aug 8, 2008
Messages
92
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

New Member
Joined
Jan 18, 2009
Messages
10
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Board Regular
Joined
Aug 8, 2008
Messages
92
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

Well-known Member
Joined
May 29, 2008
Messages
836
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
 

Forum statistics

Threads
1,082,630
Messages
5,366,656
Members
400,909
Latest member
ola97316

Some videos you may like

This Week's Hot Topics

Top