excel pros
Thread Starter
RIP Woong.
Joined: May 2000
Posts: 17,284
Likes: 0
From: san jose, ca
excel pros
is it possible to split the contents of a cell into two cells?
ex: "Robert Smith" is in one cell, but i want it to split to "Robert" and "Smith".
basically there's a name column, but the database has first name & last name columns.
thanks
ex: "Robert Smith" is in one cell, but i want it to split to "Robert" and "Smith".
basically there's a name column, but the database has first name & last name columns.
thanks
__________________
.
.
Administrator ☂
Joined: Jul 2002
Posts: 56,734
Likes: 3
From: NY
Highlight the cells containing the data you want to split
NOTE: Excel will split only one column at a time.
From the Data menu, select Text to Columns...
The Convert Text to Columns Wizard appears.
Select Delimited
Click NEXT
In the Delimiters section, select the character(s) you want to use to separate the cell data
OPTIONAL: Select or deselect Treat consecutive delimeters as one as appropriate (see Considerations)
Deselect any delimiters you do not want to use
The Data Preview box shows you what the split data will look like.
Click NEXT
OPTIONAL: If you do not want your split cells to write over the original cells, you may change the location where the split data will appear on the spreadsheet:
At the right end of the Destination box, click
The mouse pointer will become a cross.
Select the cell(s) where you want the split cells to appear
OR
In the Destination box, type the data destination in the format [ $(column letter)$(row number)]
To return to the Convert Text to Columns Wizard, click
Click FINISH
If there is any existing data in the cells where the split data will be placed, a dialog box appears asking if you want to replace the content of the destination cells.
To replace the data, click OK
To cancel the cell split and leave the cells as they were, click CANCEL
This makes life easier for me, I work with Excel sheets 90% of the time. I am still not a pro, but always have an Excel bible handy...
NOTE: Excel will split only one column at a time.
From the Data menu, select Text to Columns...
The Convert Text to Columns Wizard appears.
Select Delimited
Click NEXT
In the Delimiters section, select the character(s) you want to use to separate the cell data
OPTIONAL: Select or deselect Treat consecutive delimeters as one as appropriate (see Considerations)
Deselect any delimiters you do not want to use
The Data Preview box shows you what the split data will look like.
Click NEXT
OPTIONAL: If you do not want your split cells to write over the original cells, you may change the location where the split data will appear on the spreadsheet:
At the right end of the Destination box, click
The mouse pointer will become a cross.
Select the cell(s) where you want the split cells to appear
OR
In the Destination box, type the data destination in the format [ $(column letter)$(row number)]
To return to the Convert Text to Columns Wizard, click
Click FINISH
If there is any existing data in the cells where the split data will be placed, a dialog box appears asking if you want to replace the content of the destination cells.
To replace the data, click OK
To cancel the cell split and leave the cells as they were, click CANCEL
This makes life easier for me, I work with Excel sheets 90% of the time. I am still not a pro, but always have an Excel bible handy...
__________________
9Y0 Cayenne GTS
E46 ///M3
YD4 MDX - SOLD
EG Ferio - SOLD
FA Ferio - Returned
www.NelsonLee.net
9Y0 Cayenne GTS
E46 ///M3
YD4 MDX - SOLD
EG Ferio - SOLD
FA Ferio - Returned
www.NelsonLee.net
Last edited by MaxBoosT; Mar 25, 2004 at 10:02 AM.
i need to hoe this forum better
Thread Starter
RIP Woong.
Joined: May 2000
Posts: 17,284
Likes: 0
From: san jose, ca
Originally Posted by MaxBoosT
Highlight the cells containing the data you want to split
NOTE: Excel will split only one column at a time.
From the Data menu, select Text to Columns...
The Convert Text to Columns Wizard appears.
Select Delimited
Click NEXT
In the Delimiters section, select the character(s) you want to use to separate the cell data
OPTIONAL: Select or deselect Treat consecutive delimeters as one as appropriate (see Considerations)
Deselect any delimiters you do not want to use
The Data Preview box shows you what the split data will look like.
Click NEXT
OPTIONAL: If you do not want your split cells to write over the original cells, you may change the location where the split data will appear on the spreadsheet:
At the right end of the Destination box, click
The mouse pointer will become a cross.
Select the cell(s) where you want the split cells to appear
OR
In the Destination box, type the data destination in the format [ $(column letter)$(row number)]
To return to the Convert Text to Columns Wizard, click
Click FINISH
If there is any existing data in the cells where the split data will be placed, a dialog box appears asking if you want to replace the content of the destination cells.
To replace the data, click OK
To cancel the cell split and leave the cells as they were, click CANCEL
This makes life easier for me, I work with Excel sheets 90% of the time. I am still not a pro, but always have an Excel bible handy...
NOTE: Excel will split only one column at a time.
From the Data menu, select Text to Columns...
The Convert Text to Columns Wizard appears.
Select Delimited
Click NEXT
In the Delimiters section, select the character(s) you want to use to separate the cell data
OPTIONAL: Select or deselect Treat consecutive delimeters as one as appropriate (see Considerations)
Deselect any delimiters you do not want to use
The Data Preview box shows you what the split data will look like.
Click NEXT
OPTIONAL: If you do not want your split cells to write over the original cells, you may change the location where the split data will appear on the spreadsheet:
At the right end of the Destination box, click
The mouse pointer will become a cross.
Select the cell(s) where you want the split cells to appear
OR
In the Destination box, type the data destination in the format [ $(column letter)$(row number)]
To return to the Convert Text to Columns Wizard, click
Click FINISH
If there is any existing data in the cells where the split data will be placed, a dialog box appears asking if you want to replace the content of the destination cells.
To replace the data, click OK
To cancel the cell split and leave the cells as they were, click CANCEL
This makes life easier for me, I work with Excel sheets 90% of the time. I am still not a pro, but always have an Excel bible handy...
Originally Posted by flipped cracka
i don't know about other versions, but in excel 2003 you click on the cell you want to split, then go to Data | Text to Columns and then go through the wizard.
:bowdown: :thumbup:
thanks guys...just saved me a few hours of bs :hugglez:
__________________
.
.
Senior Member
Joined: Dec 2000
Posts: 44,835
Likes: 0
From: the southwest
Originally Posted by rich
worked fine for me..hmmm
what's wrong?
what's wrong?
she used a separate files for numerical order, alpha order, and date received order
also she used commas, hyphens, spaces, and tabs as delimiters for names with hypens and spaces
example cells:
john-smith, robert 1/1/1985
john smith, robert 1/1/1985
john, smith, robert 1/1/1985
:squint: