Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 18th 05, 06:37 PM
jv
 
Posts: n/a
Default Can I divide a column at the first "space" occurance

I have a Column that is currently set up as follows:

jones john & sharon

I want to divide the column and make two colums like:

Column one = Jones
Column Two= & Sharon


  #2   Report Post  
Old February 18th 05, 08:05 PM
Myrna Larson
 
Posts: n/a
Default

And what has happened to "John"? Eliminated?

On Fri, 18 Feb 2005 10:37:12 -0800, "jv" wrote:

I have a Column that is currently set up as follows:

jones john & sharon

I want to divide the column and make two colums like:

Column one = Jones
Column Two= & Sharon


  #3   Report Post  
Old February 18th 05, 08:09 PM
James
 
Posts: n/a
Default

You can use Text to Columns to do this. Go to Data - Text to Columns and
follow the instructions

James


"jv" wrote in message
...
I have a Column that is currently set up as follows:

jones john & sharon

I want to divide the column and make two colums like:

Column one = Jones
Column Two= & Sharon



  #4   Report Post  
Old February 19th 05, 08:11 PM
jv
 
Posts: n/a
Default

John would stay in the first column

"Myrna Larson" wrote:

And what has happened to "John"? Eliminated?

On Fri, 18 Feb 2005 10:37:12 -0800, "jv" wrote:

I have a Column that is currently set up as follows:

jones john & sharon

I want to divide the column and make two colums like:

Column one = Jones
Column Two= & Sharon



  #5   Report Post  
Old February 19th 05, 08:17 PM
jv
 
Posts: n/a
Default

The space isn't in the same position in each row.

JV

"James" wrote:

You can use Text to Columns to do this. Go to Data - Text to Columns and
follow the instructions

James


"jv" wrote in message
...
I have a Column that is currently set up as follows:

jones john & sharon

I want to divide the column and make two colums like:

Column one = Jones
Column Two= & Sharon






  #6   Report Post  
Old February 19th 05, 09:11 PM
 
Posts: n/a
Default

Assuming your text is in A1
In B1 enter =LEFT(A1,FIND("&",A1,1)-1) to get jones john
In C1 enter =MID(A1,FIND("&",A1,1),LEN(A1)-FIND("&",A1,1)+1) to get &
Sharon
Ilan

  #7   Report Post  
Old February 19th 05, 10:17 PM
Myrna Larson
 
Posts: n/a
Default

If you use Data/Text to Columns, with the delimiter as "&". That will give you

"Jones John " in column 1 and " Sharon" in column 2

But that leaves you leading and trailing spaces. If you first use Search and
Replace to replace " & " with "&", then Text to Columns, that will take care
of that issue


On Sat, 19 Feb 2005 12:11:01 -0800, "jv" wrote:

John would stay in the first column

"Myrna Larson" wrote:

And what has happened to "John"? Eliminated?

On Fri, 18 Feb 2005 10:37:12 -0800, "jv"

wrote:

I have a Column that is currently set up as follows:

jones john & sharon

I want to divide the column and make two colums like:

Column one = Jones
Column Two= & Sharon




  #8   Report Post  
Old February 21st 05, 09:59 PM
jv
 
Posts: n/a
Default

Thank you so much. That worked for me
JV

" wrote:

Assuming your text is in A1
In B1 enter =LEFT(A1,FIND("&",A1,1)-1) to get jones john
In C1 enter =MID(A1,FIND("&",A1,1),LEN(A1)-FIND("&",A1,1)+1) to get &
Sharon
Ilan




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sort by row instead of by column? PercivalMound Excel Worksheet Functions 7 August 28th 06 10:41 PM
Format cell in column B based on value in the next cell (column c) Nicole Excel Discussion (Misc queries) 7 May 18th 05 10:19 PM
same cell from multiple sheets into one column Kel Excel Discussion (Misc queries) 0 February 18th 05 12:53 AM
Copying the contents of a column into a chart Richard Excel Worksheet Functions 1 November 16th 04 02:39 PM
How to calculate the data in excel 2002 including only the last 9. TylerMaricich Excel Worksheet Functions 6 November 8th 04 07:27 AM


All times are GMT +1. The time now is 11:54 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017