Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separate text string
I have a text string as follows LAST NAME, FIRST NAME
Smith, John. I need to put the last name in one column and the first name in another column. I can do it for one cell using the left or right formula. But I have 600 cells and can't figure out the formula to copy down for the rest of the cells. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separate text string
Select ur column, then
Data Text to Columns Next Check out commas (,) and Space OK "HRA1" skrev: I have a text string as follows LAST NAME, FIRST NAME Smith, John. I need to put the last name in one column and the first name in another column. I can do it for one cell using the left or right formula. But I have 600 cells and can't figure out the formula to copy down for the rest of the cells. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separate text string
Try this:
Make sure the column to the immediate right is empty. Select your range of names Goto the menu DataText to Columns Select DelimitedNext Select Comma and SpaceFinish -- Biff Microsoft Excel MVP "HRA1" wrote in message ... I have a text string as follows LAST NAME, FIRST NAME Smith, John. I need to put the last name in one column and the first name in another column. I can do it for one cell using the left or right formula. But I have 600 cells and can't figure out the formula to copy down for the rest of the cells. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separate text string
The TextToColumns as previously described is the best procedure to do this
sort of thing, but be sure and check your results..........if the 600 rows have been hand-entered, for sure there is a mistake somewhere along the line which will need to be corrected...............no comma, two commas, etc etc Vaya con Dios, Chuck, CABGx3 "HRA1" wrote: I have a text string as follows LAST NAME, FIRST NAME Smith, John. I need to put the last name in one column and the first name in another column. I can do it for one cell using the left or right formula. But I have 600 cells and can't figure out the formula to copy down for the rest of the cells. Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separate text string
Once you have your LEFT() and RIGHT() formulas going, theoretically
all you have to do is either pull the drag handle or use copy/paste to propagate the formulas for the entirety of your data set. So, if your data starts in A2, and you want first names in column B and last names in column C, your formulas would be like so (notice no $ symbols in cell addresses): B2 =TRIM(RIGHT(A2, LEN(A2)-FIND(",",A2))) C2 =LEFT(A2, FIND(",",A2)-1) Highlight cells B2:C2, and press copy (Ctrl+C). Next, highlight cells B3:C3, and press paste (Ctrl+V). Your formulas will be as follows: B3 =TRIM(RIGHT(A3, LEN(A3)-FIND(",",A3))) C3 =LEFT(A3,FIND(",",A3)-1) If instead of B3:C3 you selected B3:C600 (then press paste) all the formulas will adjust accordingly. A quick way would be to copy B2:C2, select A2, press Ctrl+End then down arrow, highlight the corresponding cells in columns B and C, press Ctrl+End, hold Shift and press the up arrow. Then press paste. As others mentioned, Text to Columns may yield the results quicker. The formula approach is better if you have an external data set, with data in column A being subject to change. On Jan 3, 12:56 pm, HRA1 wrote: I have a text string as follows LAST NAME, FIRST NAME Smith, John. I need to put the last name in one column and the first name in another column. I can do it for one cell using the left or right formula. But I have 600 cells and can't figure out the formula to copy down for the rest of the cells. Thank you. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separate text string
Sorry - not Ctrl+End, just End by itself. I was thinking of something
else. On Jan 3, 4:05 pm, ilia wrote: Once you have your LEFT() and RIGHT() formulas going, theoretically all you have to do is either pull the drag handle or use copy/paste to propagate the formulas for the entirety of your data set. So, if your data starts in A2, and you want first names in column B and last names in column C, your formulas would be like so (notice no $ symbols in cell addresses): B2 =TRIM(RIGHT(A2, LEN(A2)-FIND(",",A2))) C2 =LEFT(A2, FIND(",",A2)-1) Highlight cells B2:C2, and press copy (Ctrl+C). Next, highlight cells B3:C3, and press paste (Ctrl+V). Your formulas will be as follows: B3 =TRIM(RIGHT(A3, LEN(A3)-FIND(",",A3))) C3 =LEFT(A3,FIND(",",A3)-1) If instead of B3:C3 you selected B3:C600 (then press paste) all the formulas will adjust accordingly. A quick way would be to copy B2:C2, select A2, press Ctrl+End then down arrow, highlight the corresponding cells in columns B and C, press Ctrl+End, hold Shift and press the up arrow. Then press paste. As others mentioned, Text to Columns may yield the results quicker. The formula approach is better if you have an external data set, with data in column A being subject to change. On Jan 3, 12:56 pm, HRA1 wrote: I have a text string as follows LAST NAME, FIRST NAME Smith, John. I need to put the last name in one column and the first name in another column. I can do it for one cell using the left or right formula. But I have 600 cells and can't figure out the formula to copy down for the rest of the cells. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parsing a text string into separate cells | Excel Worksheet Functions | |||
Separate a String of #'s | Excel Worksheet Functions | |||
Separate a FirstLast text string into two columns | Excel Discussion (Misc queries) | |||
Converting Text String to Separate Numbers | Excel Discussion (Misc queries) | |||
Separate characters in a string | Excel Discussion (Misc queries) |