Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find and Replace
I have a list of names that are written as Last, First.
I want to divide this up so that column A contains only the last names and column B contains only the first ones. I have tried doing find *,* It does "find" each name, but when I tell it to replace with ,* or * all it does is give me the comma and asterisk or asterisk. I know this can be done because I have done it before, but I keep forgetting how. Please help! Thanks. :-) |
#2
|
|||
|
|||
HI Nancy
assuming the names are all in the same column, try using the Text to Columns tool to split them. You may need to paste the results into the respective columns afterwards, but it's a nifty way of doing it! HTH Phil |
#3
|
|||
|
|||
Or, if the names are more complex, look here
http://www.cpearson.com/excel/FirstLast.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Phil" wrote in message ups.com... HI Nancy assuming the names are all in the same column, try using the Text to Columns tool to split them. You may need to paste the results into the respective columns afterwards, but it's a nifty way of doing it! HTH Phil |
#4
|
|||
|
|||
If you have the name in A1 as Last, First
Enter in Cell B1 =MID(A1,FIND(",",A1)+2,LEN(A1)) Enter in Cell C1 =MID(A1,1,FIND(",",A1)-1) You can have all names in column A and then copy paste cells b1 and C1 till the last row containing name. and if there is no space in the name after the "," then write this formula in cell B1 ( C1 formula will remain unchanged) Enter in Cell B1 =MID(A1,FIND(",",A1)+1,LEN(A1)) Thanks Karthik Bhat Bangalore |
#5
|
|||
|
|||
Type the name is cell A1 (last, first) and then enter these formulas in
B1 =MID(A1,FIND(",",A1)+2,LEN(A1)) C1 =MID(A1,1,FIND(",",A1)-1) If the name in cell A1 is last, first (without space after the symbol "," ) then the formula in cell B1 will have to be B1 =MID(A1,FIND(",",A1)+1,LEN(A1)) Now you can copy paste these two in any no. of rows... Thanks Karthik Bhat Bangalore |
#6
|
|||
|
|||
Type the name is cell A1 (last, first) and then enter these formulas in
B1 =MID(A1,FIND(",",A1)+2,LEN(A1)) C1 =MID(A1,1,FIND(",",A1)-1) If the name in cell A1 is last, first (without space after the symbol "," ) then the formula in cell B1 will have to be B1 =MID(A1,FIND(",",A1)+1,LEN(A1)) Now you can copy paste these two in any no. of rows... Thanks Karthik Bhat Bangalore |
#7
|
|||
|
|||
Thank you, Niek! I appreciate your help.
:-) "Niek Otten" wrote: Or, if the names are more complex, look here http://www.cpearson.com/excel/FirstLast.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Phil" wrote in message ups.com... HI Nancy assuming the names are all in the same column, try using the Text to Columns tool to split them. You may need to paste the results into the respective columns afterwards, but it's a nifty way of doing it! HTH Phil |
#8
|
|||
|
|||
Phil,
You're a prince! Thank you so much for your help! Stay safe. :-) "Phil" wrote: HI Nancy assuming the names are all in the same column, try using the Text to Columns tool to split them. You may need to paste the results into the respective columns afterwards, but it's a nifty way of doing it! HTH Phil |
#9
|
|||
|
|||
Hi, Karthik!
Thank you so much for your help! I really appreciate it. :-) "Karthik" wrote: If you have the name in A1 as Last, First Enter in Cell B1 =MID(A1,FIND(",",A1)+2,LEN(A1)) Enter in Cell C1 =MID(A1,1,FIND(",",A1)-1) You can have all names in column A and then copy paste cells b1 and C1 till the last row containing name. and if there is no space in the name after the "," then write this formula in cell B1 ( C1 formula will remain unchanged) Enter in Cell B1 =MID(A1,FIND(",",A1)+1,LEN(A1)) Thanks Karthik Bhat Bangalore |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
find and replace | Excel Discussion (Misc queries) | |||
Find and REPLACE within a selection, or column- not entire sheet/. | Excel Worksheet Functions | |||
find replace cursor default to find box | Excel Discussion (Misc queries) | |||
VB Find and Replace | Excel Worksheet Functions |