Split Data Out of a column into two columns. !!

traversd

Board Regular
Joined
Mar 17, 2002
Messages
82
Hi I have a huge list of Car makes that I have to dissect. My information has come in with the Manufacturer and the Model in the same column. i.e. Ford Fiesta 1.1 or Suzuki Vitara I need to split this data out into two columns, One for manufactuer and the other for Model. The only constant is that there is a space between the make and model on every entry.

Anyone solve that ?

Cheers
Darren
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi
Someone may have a formula or two for you?
If not, I will post some code that will accomplish what you need.
Tom
 
Upvote 0
On 2002-04-03 05:07, traversd wrote:
Hi I have a huge list of Car makes that I have to dissect. My information has come in with the Manufacturer and the Model in the same column. i.e. Ford Fiesta 1.1 or Suzuki Vitara I need to split this data out into two columns, One for manufactuer and the other for Model. The only constant is that there is a space between the make and model on every entry.

Anyone solve that ?

Cheers
Darren

Say your data is in Column A: starting in A1

in B1 put:

=LEFT(A1,FIND(" ",A1)-1)

and C1 put:

=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))

copy down as needed.
I'll expand on this if required.

Any help?
 
Upvote 0
Wouldn't Data/Text to Columns achieve what you want, unless I've misunderstood the problem?

Nobby
 
Upvote 0
You can use Text -> TO Column Function provided by excel.

goto: Data -> Text to Column

okay..

We can also use split function provided by VBE.
I am just started working on it.

If you want macro from me then mail me your
worksheet with few sample data I will write a macro for you and send it.

ni****h desai
http://www.pexcel.com
nisht@pexcel.com
This message was edited by nisht on 2002-04-03 05:19
 
Upvote 0
On 2002-04-03 05:20, traversd wrote:
All,

I tried Ian Mac's Fomula First and it worked a treat, Thank IAN !

I'd try the other options also, Text>Cols is an excellent feature if it's what you need. Just wish I'd also thought of that :biggrin:
 
Upvote 0
Guys,

Text To Columns worked rather well too.

Your all making me look good in front of the boss ! Thanks !
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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