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.

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.

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.

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.

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.

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.