I have a very large database that I need to be able to sort by state.
Unfortunately, whoever put the database together including city,state,zip all
in the same column. Is there any way that I can still sort by state without
retyping (retyping is not an option bc there are over 7000 entries)?

Hi,

The following are probably two of many possible ways to accomplish what you
want.

Method A:
You could easily separate the city-state-zip info into three individual
columns without having to retype the data.
1. Insert two blank columns on the right-hand side of the column
containing the combined data.
2. Select the entire column, click on 'Data' (in the Toolbar) = 'Text
to Columns' = 'Next' (to go to page 2 of the 'Convert text to columns
wizard') = select the the delimiting character(s) (e.g, comma and space)
that separate the three components (city, state, and ZIP) and click 'Next' =
'Finish'.

Method B (This assumes that the combined column contains the state data as
two letter abbreviations and the ZIP codes as 5 numbers)
1. Insert a blank column on the right-hand side of the column
containing the combined info (let's say A)
2. Enter the formula in the first data-containing cell of the newly
inserted column (let's say B2)
=LEFT(RIGHT(A2,8),2)
and fill down the column B with the formula (the shortcut for this is,
select B2, and move the cursor pointer to the bottom-right corner of the
highlighted cell, and double click)

Regards,
B.R.Ramachandran



"Sharon" wrote:

I have a very large database that I need to be able to sort by state.
Unfortunately, whoever put the database together including city,state,zip all
in the same column. Is there any way that I can still sort by state without
retyping (retyping is not an option bc there are over 7000 entries)?