Sorting with alt-enter in the cells

[Some Dude]

I have a column with cells that have data on 2 lines. On line is the product
name, line 2 is the product manager. To get them to appear in 1 cell but on
2 lines, I do an ALT-Enter [Actually, I take 2 other cells and do a
=concatenate(a1,char(10),b1)] at the end of the first line. Looks great but
doesn't sort correctly. If the values of 2 cells were

501
John Doe

and

10029
Jane Doe

it would sort those 2 rows with 10029 appearing first. What's a good way to
resolve this problem that doesn't require any knowledge of macros?
Thanks

 

[Tom Hutchins]

In another column, enter
=VALUE(LEFT(A4,SEARCH(CHAR(10),A4)-1))
and copy down for all your rows of data. This assumes your data begins in
A4. Sort by this new column, which has only the numerical portions of the
other cells.

Hope this helps,

Hutch

 

[Gord Dibben]

As long as you have text in a cell, Excel will sort as text.

To properly sort you should have your data in two cells, A1 and B1 as
originally.

You can still have the two line text in C1 but get rid of the formulas by
copying and pasting special as values.


Gord Dibben MS Excel MVP