Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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 <atyahoo<dotcom ---- "Stan" wrote in message ... 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
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
Pleasure`, Stan !
Thanks for posting back -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Stan" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
in excel can one repeat the text from a the last used column cell. | Excel Discussion (Misc queries) | |||
How Can I make a cell flash in Excel | Excel Discussion (Misc queries) | |||
Conditional formatting row if cell value in certain column is even | Excel Worksheet Functions | |||
How to sort/update large excel db | Excel Discussion (Misc queries) | |||
Column A is Town, Column B is names. How can Excel add & tell how. | Charts and Charting in Excel |