All Text to Left of a Comma (2003)

Topic

New Reply

Is it possible extract all text to the left of a comma to pull the last name out of a string of text. For example, “PATTON, JOHN X” is it possible to use a command to pull PATTON out into an adjacent column?

Thanks.

Reply | Quote

Replies

Let’s say the values are in column A, starting in cell A2.
In the cell next to it, B2, enter the following formula:

=LEFT(A2,FIND(“,”,A2)-1)

Fill down as far as needed.

Reply | Quote

Works great after I put in right reference!!!!

Thanks.

Reply | Quote

Using this same process, how would I extract the first name from this?

Reply | Quote

(Edited by sdckapr on 18-Sep-08 14:05. changed formula to account for double last names)

=MID(A1,FIND(“, “,A1)+2,FIND(” “,A1&” “,FIND(“, “,A1)+2)-FIND(“, “,A1)-2)

Steve

Reply | Quote

Amazing!

Reply | Quote

Extract the 1st 2nd and 3rd name

One formula can do the work

1] A2 : PATTON, JOHN X

2] B2 enter the formula, and copied across to D2

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,”,”,””),” “,REPT(” “,50)),COLUMN(A2)*50-49,50))

Regards
Bosco

Reply | Quote