Excel Column-Can I separate info within a cell?

S

Stan

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!
 
M

Max

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
 
M

Max

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.] ..
Click to expand...
Click to expand...

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
 
S

Stan

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
 
Top