How do I separate data into separate columns

Cowichandave

New Member
Joined
Jan 18, 2009
Messages
44
Office Version
  1. 2016
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>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Are they all in same format ?

1) xx km
2) anything with - at the end
3) anything after 2)
?
 
Upvote 0
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)))
 
Upvote 0
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


 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top