Excel Column-Can I separate info within a cell?

I have a col of Pope names.
3. St. Anacletus (Cletus) (76-88) (total line is a single cell)
4. St. Clement I (88-97)
5. St. Evaristus (97-105)
I want to eliminate [3. St.] and sort by name Anacleus only.
Is there a way to do this?
Thanks!

Hi!

I don't know if every one of your entries will start St.
But let's assume they do.
Highlight the column of data (making sure there is an empty column or two to its right).
Use Data > Text to columns and choose delimited by . (a period).

This should leve you with St in one col and the rest of the entry in the next col.

Alf

One way to try ..

Select the column

Click Data > Text to Columns > check "Delimited" > Next

In step2, enter a period "." in the "Other:" box, click Next

In step3, in the Data preview pane:
Select the 1st col, then check "Do not import column (Skip)" under Col data
format. Note that the 1st col header will change to "Skip"
Repeat to skip the 2nd col

Click Finish

The above will remove the desired parts
Now you could just Data > Sort to taste on the column
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Stan" <[email protected]> wrote in message
news:[email protected]...
> I have a col of Pope names.
> 3. St. Anacletus (Cletus) (76-88) (total line is a single cell)
> 4. St. Clement I (88-97)
> 5. St. Evaristus (97-105)
> I want to eliminate [3. St.] and sort by name Anacleus only.
> Is there a way to do this?
> Thanks!
>

> > 3. St. Anacletus (Cletus) (76-88) (total line is a single cell)
> > 4. St. Clement I (88-97)
> > 5. St. Evaristus (97-105)
> > I want to eliminate [3. St.] ..

Assuming data above is in A1 down, another way to try is to put in B1:

=MID(TRIM(SUBSTITUTE(A1,"St.","")),SEARCH(".",TRIM(SUBSTITUTE(A1,"St.","")))
+2,99)

Copy B1 down, then do a copy > paste special > values to overwrite col A,
and delete col B. Then do the Data > Sort on col A
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Sorry for delay in replying. The reply I received was specific! And it
worked! I just did not follow the instructions. BUT, my D-I-L did.

So thank you!

Stan

"Stan" wrote:

> I have a col of Pope names.
> 3. St. Anacletus (Cletus) (76-88) (total line is a single cell)
> 4. St. Clement I (88-97)
> 5. St. Evaristus (97-105)
> I want to eliminate [3. St.] and sort by name Anacleus only.
> Is there a way to do this?
> Thanks!
>

Pleasure`, Stan !
Thanks for posting back
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Stan" <[email protected]> wrote in message
news:[email protected]...
> Sorry for delay in replying. The reply I received was specific! And it
> worked! I just did not follow the instructions. BUT, my D-I-L did.
>
> So thank you!
>
> Stan