Sorting with alt-enter in the cells

S

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
 
T

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top