Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) | |||
same cell from multiple sheets into one column | Excel Discussion (Misc queries) | |||
Copying the contents of a column into a chart | Excel Worksheet Functions | |||
How to calculate the data in excel 2002 including only the last 9. | Excel Worksheet Functions |