An Excel table contains a column with
a time duration in the form ‘11:00-
12:00’. You want to split this data in
two cells: one with the start time and
the other with the end time.
Select the cells that contain the data you want to split. Ensure that all selected cells are in a single column. Excel is not in a position to split the content of cells from different columns in just one step.
In older versions of Excel, open the
command Data | Text to Columns. In
versions 2007 and 2010, select the
‘Data’ tab in the multifunctional tool bar.
Navigate to ‘Text to Columns’ under ‘Data
tools’. Excel starts the wizard for text
conversion. In the first box that shows up,
select the ‘Delimited’ option, if you want
to use a particular character to separate
each field when splitting the cell content.
The alternative here is ‘Fixed width’, where
the splitting then takes place based
on fixed lengths. Since the ‘-’ character
separates the start and the end time in
our example, use the ‘Delimited’ option
for splitting ‘10:00-12:00’ and click ‘Next’.
Set the delimiter on the next screen. Select semicolon, comma or space by checking the relevant checkbox. You can set any characters in ‘Other’. In the example, the delimiter is ‘-’. Now click ‘Next’ again. You can assign special data formats to the new cells. To do so, select the respective column and select ‘Column data format’. It is also possible to set the point at which Excel should insert the new cells under ‘Destination’. For this, enter the position of the first cell, which should have the split up contents. Excel thereafter automatically inserts the remaining parts in the subsequent cells.
If you split more than one cell in one step, Excel automatically uses the subsequent cells below the specified cells for results. If you don't enter anything in ‘Destination’, Excel overwrites the original cells. End the wizard with ‘Finish’.
Select the cells that contain the data you want to split. Ensure that all selected cells are in a single column. Excel is not in a position to split the content of cells from different columns in just one step.
Set the delimiter on the next screen. Select semicolon, comma or space by checking the relevant checkbox. You can set any characters in ‘Other’. In the example, the delimiter is ‘-’. Now click ‘Next’ again. You can assign special data formats to the new cells. To do so, select the respective column and select ‘Column data format’. It is also possible to set the point at which Excel should insert the new cells under ‘Destination’. For this, enter the position of the first cell, which should have the split up contents. Excel thereafter automatically inserts the remaining parts in the subsequent cells.
If you split more than one cell in one step, Excel automatically uses the subsequent cells below the specified cells for results. If you don't enter anything in ‘Destination’, Excel overwrites the original cells. End the wizard with ‘Finish’.
0 comments:
Post a Comment
please write your comment
Note: Only a member of this blog may post a comment.