Sorting and filtering with more than one line of data in a cell

[Pelham]

I have a spreadsheet with about 10 fields (columns) and about 50 data
lines (rows) of associated real estate data that I want to sort and
filter.

Sorting and filtering works fine, but in this case I have more than
one line of data (e.g. a lease date) in some of the cells that I have
entered using ALT + ENTER after the first line. As a result, the
formatting that I have for the cells containing only one lease date
does not seem to work for the cells with more than one lease date in
the cell. Therefore, when I sort by this field the cells with more
than one date in them don't get sorted properly because they are not
read as dates like the cells containing only one lease date...

Can someone tell me how I can fix this problem, please?

Thanks in advance.

 

[paul.robinson]

Hi
You should re-design your database so that you only have one date in
one cell. Bit of a bore to do but handling your records (rows) will be
much more straightforward. Should not take you too long with only 50
rows.
regards
Paul

 

[joel]

the best solution would be to create a seperate row in your workbook for each
lease date. That is what most people do in excel. Other solutions would be
to put each lease data in a seperate column. if you really want to keep the
data in the same format you can sort by the first lease datta by adding an
additional column in your workbook with the 1st lease date. You can use a
formula like this in the new column to get the Lease date

=DATEVALUE(IF(ISTEXT(B30),LEFT(B30,FIND(CHAR(10),B30)-1),B30))

Replace B30 with the cell that has the dates and copy down new column