On Fri, 21 Aug 2009 03:45:01 -0700, RAYCV
wrote:

Hi, I am trying to extract and remerge data with an additional character
(comma). For example, I have the following list in a column with many
variables

Jones Bob Mr
Campbell Jock Mr & Mrs
Smith Bob Banking Trust

and want it to be displayed as

Jones, Bob Mr
Campbell, Jock Mr & Mrs
Smith, Bob / Banking Trust


I have used =LEFT(G9,FIND(" ",G9)-1) to find the text and then
=H9&","&RIGHT(G9,FIND(" ",G9)*1) to concatenate the text, but it doesn't
return all the data for the 3rd record?? What am I doing wrong

Thanks in advance

The "number of characters" argument for your right function is incorrect.

For this technique, you should use either:

=B1&","&RIGHT(A1,LEN(A1)-FIND(" ",A1)+1)

or (shorter):

=B1&","&MID(A1,FIND(" ",A1),99)

(where 99 is some number larger than the length of your longest string)

However, in your third example, you have inserted a "/" after the Bob. Are you
trying to do this with a formula? Or is it a typo?
--ron