I am working in a spreadsheet where I frequently use the filter and sort
functions. I want to split one cell into two in one column, I don't want to
use two columns and merge the rest of the cells as this upsets the
filter/sort functions.

Yes, you can split one cell into two cells in Excel without affecting the filter/sort functions. Here's how you can do it:

1. Select the cell that you want to split into two cells.
2. Click on the "Home" tab in the ribbon.
3. Click on the "Wrap Text" button in the "Alignment" group. This will allow the text to wrap within the cell.
4. Place the cursor where you want to split the text.
5. Press "Alt + Enter" on your keyboard. This will insert a line break and split the text into two lines within the same cell.

Now you have two lines of text within the same cell. You can use the filter/sort functions as usual without any issues. If you want to split the cell into two separate cells, you can use the "Text to Columns" feature. Here's how:

1. Select the cell(s) that you want to split into two cells.
2. Click on the "Data" tab in the ribbon.
3. Click on the "Text to Columns" button in the "Data Tools" group. This will open the "Convert Text to Columns Wizard".
4. In the first step of the wizard, select "Delimited" and click "Next".
5. In the second step, select the delimiter that you want to use to split the text (e.g. space, comma, semicolon, etc.) and click "Next".
6. In the third step, you can choose the data format for each column. You can leave it as "General" or choose a specific format if needed. Click "Finish" when you're done.

Now the cell(s) have been split into two separate cells, and you can use the filter/sort functions as usual.

Try this....

Highlight the required cells you want to split and DATA TEXT TO COLUMNS
NEXT then enter the parameter that you want to split by (colon, space, etc)
FINISH

"Chalky" wrote:

I am working in a spreadsheet where I frequently use the filter and sort
functions. I want to split one cell into two in one column, I don't want to
use two columns and merge the rest of the cells as this upsets the
filter/sort functions.

Thanks for your reply James. That doesn't really do the trick, maybe because
it is only one cell or maybe because I am trying to put a formula into the
cell I want to split.

Chalky

"James Hamilton" wrote:

Try this....

Highlight the required cells you want to split and DATA TEXT TO COLUMNS
NEXT then enter the parameter that you want to split by (colon, space, etc)
FINISH

"Chalky" wrote:

I am working in a spreadsheet where I frequently use the filter and sort
functions. I want to split one cell into two in one column, I don't want to
use two columns and merge the rest of the cells as this upsets the
filter/sort functions.

You cannot 'split' a cell into 2, unless it is a previously merged cell.

If you provide more detail about your ultimate objective, you might get
advice on an alternative approach


"Chalky" wrote:

I am working in a spreadsheet where I frequently use the filter and sort
functions. I want to split one cell into two in one column, I don't want to
use two columns and merge the rest of the cells as this upsets the
filter/sort functions.

Chalky,

Try this, it may work depending on the data that you want to split...

A B
1 chalky =left (a1,2) will yield "ch" in cell B1

This returns the first two characters of A1. You can also use the
=right(....) function and I think there's another one.... "mid" perhaps.

Good luck


James


"Chalky" wrote:

Thanks for your reply James. That doesn't really do the trick, maybe because
it is only one cell or maybe because I am trying to put a formula into the
cell I want to split.

Chalky

"James Hamilton" wrote:

Try this....

Highlight the required cells you want to split and DATA TEXT TO COLUMNS
NEXT then enter the parameter that you want to split by (colon, space, etc)
FINISH

"Chalky" wrote:

I am working in a spreadsheet where I frequently use the filter and sort
functions. I want to split one cell into two in one column, I don't want to
use two columns and merge the rest of the cells as this upsets the
filter/sort functions.

Hi Chalky

I'm not sure exactly what you are after. Perhaps you are wanting to sort
using just part of a cell contents. If so a previous response by Jim Cone
may help

"Jim Cone" wrote in message
...
Mike,
My Excel add-in "Special Sort" - rel 1.30, should do what you want.
It adds 11 sort features not found in Excel, including sorting by..
Prefix, Suffix or Middle (where you specify the length/position).
Will also sort by color, dates, number group, length, reverse,
random and others.
Comes with a 2 page install/use Word file.
It is available - free - upon direct request.
Remove XXX from my email address.
Regards,
Jim Cone
San Francisco, USA
XX

Jim was kind enough to send me a copy and it works very well. I am sure that
if you mail him, he would send you a copy also.

If this is not your problem, post back with more details.

Regards

Roger Govier


Chalky wrote:
Thanks for your reply James. That doesn't really do the trick, maybe because
it is only one cell or maybe because I am trying to put a formula into the
cell I want to split.

Chalky

"James Hamilton" wrote:


Try this....

Highlight the required cells you want to split and DATA TEXT TO COLUMNS
NEXT then enter the parameter that you want to split by (colon, space, etc)
FINISH

"Chalky" wrote:


I am working in a spreadsheet where I frequently use the filter and sort
functions. I want to split one cell into two in one column, I don't want to
use two columns and merge the rest of the cells as this upsets the
filter/sort functions.