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

If Jones Bob Mr is in A1:

=REPLACE(A1,FIND(" ",A1),1,", ")

"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

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

Put this formula in h9 and then drag it down
=LEFT(G9,FIND(" ",G9)-1)&", "&RIGHT(G9,LEN(G9)-FIND(" ",G9)*1)

vijay

"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

On Aug 21, 11:45�am, 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


As you're just inserting a comma, why not find the space and use that
position to extract both the pre-comma & post-comma text.

=MID(G9,1,FIND(" ",G9)-1)&","&MID(G9,FIND(" ",G9),50)

Similar technique for the stroke insertion.

Alan Lloyd

Try
=SUBSTITUTE(A1," "," , ",1)

If this post helps click Yes
---------------
Jacob Skaria


"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